Dlookup (1 Viewer)

[MANJIT]

I NEED HELP USING DLOOKUP ON A ACCESS SUBFORM.

IN THE SUBFORM I HAVE THE FOLLOWING:
SOR CODE (FIELD SAVED IN TABLE JOB COSTS)
UNIT (TEXT BOX)
RATE (TEXT BOX)
WORK DESC. (TEXT BOX)

WHEN THE SOR CODE IS ENTERED I NEED THE UNIT AND RATE TO POPULATE, BY LOOKING UP VALUES IN TABLE SOR CODES

THE TABLES THAT CONTAINS THE DATA IS CALLED SOR CODES.

THE MAIN FORM IS CALLED JOB DETAILS
THE SUBFORM IS CALLED JOB COSTS


CAN SOMEONE HELP PLEASE.


I HAVE TRIED


=DLookUp("[UNIT]","[SOR CODES]","[SOR CODE]='" & [SOR CODE])

AND I GET A ERROR?

 

[Galaxiom]

You have unmatched single quotes.
If [SOR CODE] is a text field:

=DLookUp("[UNIT]","[SOR CODES]","[SOR CODE]='" & [SOR CODE] & "'")

BTW. Full capitilisation online indicates shouting.

You would also do well to drop spaces from names and use capitilisation more judiciously in your code.

 

[MANJIT]

Hi,


Sorry I did not realise about caps.

I have tried

=DLookUp("[UNIT]","[SOR CODES]","[SOR CODE]='" & [SOR CODE] & "'")


but still get #error


is there anything else I am doing wrong?



many thanks for your help

 

[vbaInet]

Your table is called Job Costs, so what should be the middle argument is the name of the table:

=Nz(DLookUp("[UNIT]","[Job Costs]","[SOR CODE]='" & Me![SOR CODE] &  "'"), "")

Have a look at these links for more info:
http://www.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php

Nz():
http://www.techonthenet.com/access/functions/advanced/nz.php

 

[MANJIT]

Many thanks for the response but it still does not work.


I have broken it down and


=Nz(DLookUp("[UNIT]","[sor codes]"))


Returns the first value from the correct table and field within the table. I just need the retrieve the correct value based on the Sor code that is selected in the sub form?



=Nz(DLookUp("[UNIT]","[Job Costs]","[SOR CODE]='" & [Me]![SOR CODE] & "'"),"")

Gives the following error
#Name?


=Nz(DLookUp("[UNIT]","[sor codes]","[SOR CODE]='" & [Me]![SOR CODE] & "'"),"")

also gives
#Name?


I have tried

=Nz(DLookUp("[UNIT]","[sor codes]","[SOR CODE]='" & [job details form]![job costs subform]![SOR CODE] & "'"),"")

Where job details form is the main form job costs subform is within job details form and the field that is used to select the info is called SOR code

Sorry to be a pain, I have been on this all day and am not getting anywhere?

 

[vbaInet]

You're referencing incorrectly somewhere. You can write it like this too:

=Nz(DLookUp("[UNIT]","[Job Costs]","[SOR CODE]= [Forms]![[COLOR=Red][B]NameOfForm[/B][/COLOR]]![SOR CODE]"),"")

Amend the bit in red.

 

[MANJIT]

=Nz(DLookUp("[UNIT]","[sor codes]","[SOR CODE]= [Forms]![Job Details Form]![job costs subform]![SOR CODE]"),"")

I have referenced the main form, then the subform.


WORKED!!!!



Many Many thanks

 

[vbaInet]

Good job!