CODEWITHSUNDEEP
sqlms-accesstextboxvba
Jessica Mather
Jessica Mather

Reputation: 133

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.

Upvotes: 0

Views: 407

Answers (2)

Gustav
Gustav

Reputation: 55806

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 & "")

Upvotes: 1

Lybren
Lybren

Reputation: 330

Most certainly the reason is behind you combobox. If you are using a combobox with more than 1 column, hiding the column that has an ID because you use a query to populate said combobox, Then you are passing the underlying ID to the select query.

To actually pass what is written on the combobox to the select clause use it's text property. 

sSQL = "SELECT Building FROM tblDepartment WHERE " _
& "Department_ID = '" & Me.cboDepartment.Text & "'"

Upvotes: 1

Related Questions