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?

 

[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!