dlookup help with bound field on a form

[olivercastell]

hi,
I got pointed in the right direction with dlook up to solve this problem, but i cant for the life of me get it to work, so any help would be greatly appreciated.

I have two main tables:

batch record
this contains information on each batch, including patient details, date of manufacture, label code (this identifies the drug), dose etc etc.

Cytotoxic products table
this contains the label code (unique identifier of product), drug, route of administration, warnings, usual dose, usual volume.

I have a form for inputtung data into the batch record table
batch record form
relevent fields for this question are: label code and dose

the databse is used to generate labels for the drugs (products) and also for statistics etc

certain products are always the same dose, and hence the usual dose value in the cytotoxic products table. What i am trying to do is when you enter the label code on the form the dose field on the form is updated to contain the usual dose for that product (taken from the cytotoxic products table)

I need this valueable to be overridable though, for those few cases where we are required to make those products at a different dose.

I have been trying to use dlookup on exit of the label code field on the form. however im not having much luck. This is the first time i have used visual basic and after trawling the internet for examples i still cant get it to work.

If anyone could give me a hand, it would be greatly appreciated.

I hope all the above makes sense.

thanks very much

oliver

 

[olivercastell]

thank you

this is what iv got;

Private Sub Label_Code_AfterUpdate()
[dose] = DLookUp("[usual dose]", "[Cytotoxic products table]", "[label code]=' " & [label code]"'")
End Sub

Am I on the right lines?
my label code isnt numeric.

do I need to define anything before hand with dim or anything?

thanks

 

[RichO]

Was this code taken verbatim from your database or did you retype it for your post?

I see a syntax error (missing & after [label code]) and a space between the first ' and [label code].

"[label code]=' " & [label code]"'")

fixed...

"[label code]= '" & [label code] & "'")

 

[olivercastell]

thanks, that has fixed the one problem, but now i have another,
when i open the form the dose field says #Error (would you expect this? I know it cant contain a value at this point as i havent specified a label code) then one i start to fill in data the #error message goes, but once i enter the label code - and therefore start running the code, it comes up with:
run time error '2001':
You canceled the previous operation.

when i debug, it highlights the whole expression yellow and when i place my mouse over it it says

[dose]=null

it shouldnt because i have specified a number in the usual dose field for the label code im testing with.

Any ideas what im doing wrong?

the code was cut n pasted by the way.

thanks for your help