Problem w/ DLookup in INSERT Statement

[Adam McReynolds]

I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:

Dim lookModType1 As Variant

lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")

SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"

DoCmd.RunSQL SQLtext4

Note: My DLookup works fine by itself for other uses.

Any help would be much appreciated.

 

[pbaldy]

If EquipmentDescription is text, you'd need the single quotes around the value, like the previous value has. If that isn't it, this may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Adam McReynolds]

If EquipmentDescription is text, you'd need the single quotes around the value, like the previous value has. If that isn't it, this may help:

http://www.baldyweb.com/ImmediateWindow.htm

Thanks for the reply!
EquipmentDescription is text and I tried single quotes around the value and it outputs to the table ( & lookModType1 &) in that field. I tried with the debug and in the line it gave me for that value the barcode I entered. I tried other ways and the only other output in the field I got was null.

Is there a way to enter the DLookup straight into the INSERT statement? When I tried it kept having problems.

 

[pbaldy]

What is your code with the single quotes around that value? It would be similar to what you did around Me.txt_bc1.

 

[Adam McReynolds]

What is your code with the single quotes around that value? It would be similar to what you did around Me.txt_bc1.

Yes it works now. I did what you said but I was working with a test table and I did not populate that field so it inserted Null. I feel like forum village idiot. Thanks so much for your help. You are a life saver. Cheers!

 

[pbaldy]

Happy to help!