Jessica Mather Jessica Mather - 8 days ago 7
SQL Question

How do I populate the value of a textbox from my database?

This is my code that I have so far, I tried looking up the solution to my problem but could not find exactly what I was looking for. I am doing this in Access.

Dim rs As DAO.Recordset
sSQL = "SELECT Building FROM tblDepartment WHERE Department_ID =
'" & Me.cboDepartment & "'"
Set rs = CurrentDb.OpenRecordset(sSQL)
Me.txtLocation = rs.Fields(1).Value


I also tried this:

Dim db As DAO.Database
Set db = CurrentDb()
sSQL = "SELECT Building FROM tblDepartment WHERE " _
& "Department_ID = '" & Me.cboDepartment.Value & "'"
Me.txtLocation.Value = db.Execute(sSQL)


Third attempt was this:

Sub spGetBuilding()
Dim rs As DAO.Recordset
sSQL = "SELECT Building FROM tblDepartment WHERE Department_ID = '" & Me.cboDepartment & "'"
Set rs = CurrentDb.OpenRecordset(sSQL)

If rs.RecordCount > 0 Then
Me.txtLocation = rs.Fields(0).Value
Else
Me.txtLocation = 0
End If

Set rs = Nothing
End Sub


But it returned the error message "Data type mismatch in criteria expression"

I figured out the reason for this error is because some of the values don't contain a value for building. I tried to set the parameter so that the WHERE also included a "AND Building IS NOT NULL" to eliminate get rid of the null values. But the same error is still coming up.

Answer

For such a simple task, DLookup is much simpler, and it returns a Null if no building is found.

Also, leave out the single quotes as Department_ID most likely is numeric:

Me!txtLocation.Value = DLookup("Building", "tblDepartment", "Department_ID = " & Me!cboDepartment.Value & "")