Query/Dlookup problem in access form

[load3d]

SELECT [EquipmentReport].[Equipment#], [EquipmentReport].CurrentHours
FROM [EquipmentReport]
WHERE ((([EquipmentReport].[Equipment#])=[Forms]![Mainform]![subform].[form]![controlname]));

The query works when I test outside of the form and enter in a value manually.

on the save button of the form after all data is entered I have the Dlookup statement

me.controlname = Dlookup("CurrentHours", "QueryName")

However, the field stays blank. This tells me the problem is in the WHERE statment but I can't figure out what the problem is. The syntax matches what I use in update queries.

 

[Remou]

Try:

me.controlname = Dlookup("CurrentHours", "EquipmentReport","[Equipment#]=[Forms]![Mainform]![subform].[form]![controlname]")

 

[dhookom]

If the code is running in the MainForm, you should be able to use:

me.controlname = Dlookup("CurrentHours", "EquipmentReport", "[Equipment#]=" & Me.[subform].[form]![controlname])

If Equipment# is text rather than numeric, try:

me.controlname = Dlookup("CurrentHours", "EquipmentReport", "[Equipment#]='" & Me.[subform].[form]![controlname] & "'")

Duane
Hook'D on Access
MS Access MVP

 

[load3d]

Thanks for the prompt responses! I finally got it to work.