Dlookup

[gheyman]

I have a form where I have a Combo Box. Based on whats selected in that combo I want to do a lookup in a table

My table Name is: tblItem
From the table I want to find the: ItemDescription
Based on the Value in my Combo Box named Combo53

Combo53 is storing the a value from the table - the field name in the table is ItemID

So it should be looking up the ItemID and finding the ItemDescription in the Table tblItem

Private Sub Combo53_AfterUpdate()
Dim ItemDesc As Long

ItemDesc = DLookup("ItemDescription", "tblItem", "ItemID = " & Forms![frm_LTA]!Combo53)

Me.TextItemDescription = ItemDesc

End Sub

I am getting an debug error Runtime 3464 Data Type Mismatch in Criteria Expression



The unbound Field on my form where I want the Dlookup results to go into is: TextItemDescription

 

[gheyman]

correction

If I put the formula in the unbound field instead of running it in VBA, this formula works. But I would rather do it in VBA
=DLookUp("ItemDescription","tblItem","ItemKey =" & [Forms]![frm_LTA]![Combo53])

 

[Micron]

The error is raised on which line, DLookup or assignment to the control?
Is ItemID text or number data type?

EDIT - frm_LTA is open at the time?

 

[gheyman]

Yes form is open
See my last post, I figured the Dlookup if I was to do it directly in the field - but I really want this in VBA
=DLookUp("ItemDescription","tblItem","ItemKey =" & [Forms]![frm_LTA]![Combo53])

 

[gheyman]

Micron
Its the ID - AutoNumber and the Error is raised on the Dlookup Formula

 

[Micron]

Dim ItemDesc As Long

Then you're trying to assign the description [ItemDescription], which appears to be text, to a variable that you've declared as a Long:
ItemDesc = [the ID as a number]

In other words, the function looks at your (text) field in the (table) you specify based on the (criteria) you specify.
EDIT - forum didn't like my use of square brackets around the word "table".

 

[gheyman]

Are you saying that if I change to
Dim ItemDesc as String this should then work?

 

[Micron]

That would be one solution, and according to how I interpret what you're doing, that would be the route I'd pick. If you have to ask, then I must not have explained myself very well. How would you explain what it is that you want the function to do/return?

 

[gheyman]

I would like to run a DLookup using VBA Code. The Event would be when a Combo box is changed

I have the Dlookup formula currently in an unbound field. This is the formula in the Control Source (It works and gives the correct results). I did this to check my formula and to help the board understand what I need.

=DLookUp("ItemDescription","tblItem","ItemKey =" & [Forms]![frm_LTA]![Combo53])

I want to move that formula from the aforementioned fields control Source to VBA Code that runs when the Combo Box (Combo53) is changed.

I want the results of the DLookup formula to populate an Unbound Filed on the same form that is named Text53

The Combo Box is on the aforementioned form (in the header) and the Unbound Text box is on the same form in the details section

I want the function to return the "ItemDescription" and place it in Text53

 

[gheyman]

Got it!

Private Sub Combo53_AfterUpdate()
Dim ItemDesc As String

ItemDesc = DLookup("ItemDescription", "tblItem", "ItemKey =" & [Forms]![frm_LTA]![Combo53])

Me.Text53 = ItemDesc

End Sub

Thank you for the Help. Much appreciated.