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

--
Message posted via http://www.accessmonster.com

Stella Pieters via AccessMonster.com wrote:
>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.

--
Marsh
MVP [MS Access]

Thank you very much for your help.

--
Message posted via http://www.accessmonster.com