Set text box default value to dlookup results

[bconner]

All,
I have a subform that is the result of a query. I want to set the default to a text box on the subform based on the value in another field on the same subform.

Below is a dlookup using the table that the subform query is based on:

=DLookUp("[Self Pay AP]![Date Worked]","[SELF PAY AP]","[Self Pay AP]![INVOICE NUMBER] =" & [Forms]![FrmMain]![QRY_ALL_DETAIL SUBFORM].[Form]![INVOICE NUMBER])

the default value is coming back null on the subform, any ideas how to get this to work?

 

[GohDiamond]

If you're looking up from a field on a dirty form that data probably hasn't been recorded yet, so it's null.. on the other hand.. You might not be addressing the Control properly, I always have trouble with Syntax when addressing controls on forms...

To record the data use something like Me.Dirty = False then do the lookup
for syntax here's a link :
http://www.access-programmers.co.uk/forums/showthread.php?t=212911&highlight=control+on+subform

Cheers
Goh

 

[bconner]

Thanks Goh, I appreciate the help, I will give it a go.