Updating text box value from combobox option

0
votes

I can't work out where I am going wrong with my code. When the user selects a value in the combo box, i want it to go to the Consultants table and grab the default rate for that consultant and stick it in the Hourly Rate text box. This is the msg that I get when I update the combobox.

Private Sub cmbConsultant_Change()
Dim db As Database
Dim rs As DAO.Recordset ''Requires reference to Microsoft DAO x.x Library
Dim strSQL As String

strSQL = "defaultFee * FROM tblConsultants WHERE ID = """ & Me!cmbConsultant & """"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
   Me.txtHourlyRate = rs!CountOfEndDeviceType
Else
   Me.txtHourlyRate = ""
End If

Set rs = Nothing
Set db = Nothing

End Sub

2
ms-accesscomboboxvbams-access-2010

2 Answers

2
votes

You could use DLookup for this - much simpler:

Private Sub cmbConsultant_Change()

    Me!txtHourlyRate.Value = DLookup("defaultFee", "tblConsultants", "ID = '" & Me!cmbConsultant.Value & "'")

End Sub

However, most likely your ID is numeric, thus:

Private Sub cmbConsultant_Change()

    Me!txtHourlyRate.Value = DLookup("defaultFee", "tblConsultants", "ID = " & Me!cmbConsultant.Value & "")

End Sub
1
votes

I Think You need some SELECT Here 

strSQL = "defaultFee * FROM tblConsultants WHERE ID = """ & Me!cmbConsultant & """"

It Should Be:

strSQL = "SELECT defaultFee AS [CountOfEndDeviceType] FROM tblConsultants WHERE ID = """ & Me!cmbConsultant & """"

Also Note, That [CountOfEndDeviceType] Must be a FieldName, SO I've put it in a Select statement.