create a variable in vba with sql code

[blobb]

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?

 

[J_Goddard via AccessMonster.com]

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]

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!