DLookup referencing a subform

[Stella Pieters via AccessMonster.com]

L.S.

The intention of the following code is to lookup the artDescrip in the
contraceptive table, using the description (which is a combobox) from the
subform visits1. The main form is called "registration form".

I coded it as follows:


Dim varX As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)

varX = DLookup("[ArtDescrip]", "Contraceptives", "[Contraceptives] = '" _
& Forms![REGISTRATION FORM]![visits1]!Contraceptives)

rst("ArtDescrip") = varX

Could you please give me a hint on how to do this, as I'm getting the run
error 438, saying that "object does not support this object or method".

Thanks in advance

Stella Pieters

 

[Marshall Barton]

The intention of the following code is to lookup the artDescrip in the
contraceptive table, using the description (which is a combobox) from the
subform visits1. The main form is called "registration form".

I coded it as follows:

Dim varX As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Transaction Table", dbOpenTable)

varX = DLookup("[ArtDescrip]", "Contraceptives", "[Contraceptives] = '" _
& Forms![REGISTRATION FORM]![visits1]!Contraceptives)

rst("ArtDescrip") = varX

Could you please give me a hint on how to do this, as I'm getting the run
error 438, saying that "object does not support this object or method".

The DLookup needs the closing quotes on the condition:

varX = DLookup("[ArtDescrip]", "Contraceptives", _
"[Contraceptives] = '" & _
Forms![REGISTRATION FORM]![visits1]!Contraceptives & "'")

But it seems like the rules of normalization preclude doing
this. As long as the transaction table has the
Contraceptives field, there is no need to copy the
ArtDescrip from the Contraceptives table.

 

[Stella Pieters via AccessMonster.com]

Thank you very much for your help.