Hi everyone,
I have been struggling with this control all day - I have a form with a combo box [Employee ID] that looks up the value [Employee ID] from a table called tbl_Personnel_Details
This table is linked to another table called tbl_Personnel_Status_Update which contains the field [CBD_Code]. I use the query qry_Personnel_Current_Status to determine the most recent value for [CBD_Code] in relation to the [Employee ID].
I have been trying to use the following VBA code to update the combo box [Charge_Code] with the relevant [CBD_Code] using the AfterUpdate event on [Employee ID]:
Whenever I try to run it I get a message saying either "Runtime Error '2001' - you cancelled the previous event" or "Runtime Error '3464' - data type mismatch".
I have tried changing the query that it is getting the value for [Employee ID] from and it works fine (although it gives me a different value so this is not suitable). I have also used the correct query with different criteria (I used a field called [Equipment #]) and it works fine. the Employee ID field is a text field.
Does anyone have any suggestions why its not working? Or a better way to update the field [Charge_Code] with the CBD_Code that is associated with the selected Employee ID?
Thanks,
Duncan
I have been struggling with this control all day - I have a form with a combo box [Employee ID] that looks up the value [Employee ID] from a table called tbl_Personnel_Details
This table is linked to another table called tbl_Personnel_Status_Update which contains the field [CBD_Code]. I use the query qry_Personnel_Current_Status to determine the most recent value for [CBD_Code] in relation to the [Employee ID].
I have been trying to use the following VBA code to update the combo box [Charge_Code] with the relevant [CBD_Code] using the AfterUpdate event on [Employee ID]:
Code:
Private Sub Employee_ID_AfterUpdate()
Dim EID, CBD As String
EID = Me.Employee_ID.Value
'update charge code with employee's current CBD Code
CBD = DLookup("[CBD_Code]", "qry_Personnel_Current_Status", "[Employee ID] = " & EID)
Me.Charge_Code.Value = CBD
End SubI have tried changing the query that it is getting the value for [Employee ID] from and it works fine (although it gives me a different value so this is not suitable). I have also used the correct query with different criteria (I used a field called [Equipment #]) and it works fine. the Employee ID field is a text field.
Does anyone have any suggestions why its not working? Or a better way to update the field [Charge_Code] with the CBD_Code that is associated with the selected Employee ID?
Thanks,
Duncan
