Hi I am attempting to create a variable (NewbornID) with an SQL query in VBA
and cannot seem to get it to work (I am new to VBA). I have tried it various
ways based on what I found on the internet, but nothing seems to work. This
is what I was trying to do:

Dim NewbornID As Integer

NewbornID = ("SELECT Contacts.ContactID FROM Contacts WHERE
Pregnancy.UniqueID = Contacts.UniqueID")

the query works in SQL but not when i transferred it to VBA. Could someone
help?

Oh okay sorry I didn't explain things very well. What I am attempting to do
is to move the ContactID into a [Link] table for a certain type of
relationship that is defined by the UniqueID. More specifically, I have an
index case that is related to one or more subsequent cases. The [Contacts]
table holds the name, addresses etc... and the [Pregnancy] table holds the
information related to the offspring (ie., Contacts.ContactID = 001 could be
related to Contacts.ContactID = 238 and Contacts.ContactID = 239. The
UniqueID in these cases would be 001-00 (for the index) and 001-01 001-02
(for the related cases respectively). What I want to do is select
Pregnancy.UniqueID = 001-01 and insert into the [Link] table ContactID of the
index (Link.ContactID1 = 001) and of the related case (Link.ContactID2 =
238).

Any suggestions?

Thank you in advance!

"J_Goddard via AccessMonster.com" wrote:

> Hi -
>
> First, a note - the Dim statement creates the variable; what you are trying
> to do is assign a value to it.
>
> You are trying to use a SQL statement as if it was a function which returns a
> value, which is not the way SQL works.
>
> In your case, you might try the DLookup function, which does return a single
> value.
>
> However, I don't understand your query - are you using two related tables in
> the query (i.e. pregnancy and contacts)? If so, then a) the syntax is wrong
> ("pregnancy" needs to be in the FROM clause) and b) it almost certainly will
> return more than one value.
>
> Can you provide more details on what you are trying to do, please?
>
> John
>
>
>
> blobb wrote:
> >Hi I am attempting to create a variable (NewbornID) with an SQL query in VBA
> >and cannot seem to get it to work (I am new to VBA). I have tried it various
> >ways based on what I found on the internet, but nothing seems to work. This
> >is what I was trying to do:
> >
> >Dim NewbornID As Integer
> >
> >NewbornID = ("SELECT Contacts.ContactID FROM Contacts WHERE
> >Pregnancy.UniqueID = Contacts.UniqueID")
> >
> >the query works in SQL but not when i transferred it to VBA. Could someone
> >help?
>
> --
> John Goddard
> Ottawa, ON Canada
> jrgoddard at cyberus dot ca
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200807/1
>
>