Dlookup in Forms

[ria4life]

Can anyone please assist:
I have this dlookup code setup..but for some reason i only get the first record....I tried several things but cant seem to get over that hump...
Anyone please assist.

--All fields are setup as short text in the table. However Task_Code is a numeric value




Option Compare Database
Dim currentdb As String
Option Explicit



Private Sub Form_problem_Change()
Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "Problemcode= '" & [TaskCode] & "'")
End Sub

 

[arnelgp]

can you explain further, do you need to fill the combo27 from your table?

 

[ria4life]

arnelgp,

I have renamed the tables and forms to remove the underscore as recommended by another user:

----------------------------
Option Compare Database
Dim currentdb As String
Option Explicit


Private Sub Problemcode_Change()
Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "Problemcode= '" & [TaskCode] & "'")
End Sub
-----------------------------

The user would select a code from the drop down list in Problemcode.

I would like access to pull the description "Desc" of that code from the HPEMProblemCodes table using the dlookup function. and return the result in Combo27

Once the user selects an option from the Problemcode field in the form...Combo27 should update with a description of the code....The problem is it only returns the first "Desc" no matter what code is selected.

The HPEMProblemCodes table has two columns:
TaskCode - which matches Problemcode
Desc - Which is the field i would like to return


All formatting is setup as short Text...However Problemcode is a number ranging from 000 to 1000

Hope this makes sense

 

[arnelgp]

Private Sub Problemcode_Change()
Combo27 = Nz(DLookup("Desc", "[HPEMProblemCodes]", "[TaskCode] = " & Me.[Problemcode]), "")
End Sub

its better to put the code in AfterUpdate event of the Problemcode textbox.

 

[ria4life]

arnelgp,

I tried your formula but got an error 3464 - Data type mismatch in criteria expression.

However in comparing your formula to mine...i reversed the criteria like you did and that seemed to have worked....

Combo27 = DLookup("Desc", "[HPEMProblemCodes]", "TaskCode= '" & [Problemcode] & "'")


Thanks for providing some insight

 

[arnelgp]

so taskcode was a string after all. good luck!