Hi Richard,

After a long day of extensive playing around, I have finally found a work around of what I needed to have done. I thank you for your time in providing me with the guidance on how to go about this.

I was about to give up completely and needed some assistance, hence I have posted my query through to your Tech Help service. Could you please ignore my request.

I apologies for the inconvenience caused.

Regards

Hamish

---

Reply from Richard Rost:

No problem. I just found your question in TechHelp. I'll post the solution here for everyone else's benefit in case they were following the thread:

In my case using the two tables examples below. How do I get the ID field into Table 1 using VBA code.

Table 1
ID VendorNumber VendorName Inventory InventoryValue
1412269 Colgate Palmolive 400 $500,000.00
1428757 Reckitts
1429444 Natures Organics
1427036 Procter and Gamble 350 $400,000.00

Table 2. ID's from this table needs to be inserted into Table 1 ID field
ID VendorNumber VendorName
1 1427036 Procter and Gamble
2 1428757 Reckitt Benckiser
3 1429444 Nature Organics
4 1412269 Colgate Palmolive

---

OK, this makes a lot more sense. :)

Create an ID field in Table1, let's call it ID2 (since it's table 2's ID).

Make a recordset and loop through all of the records in Table 1, looking up the ID from Table 2 as you go:

[this code is from memory, I haven't tested it]

dim db as database
dim rs as recordset
set db = currentdb()
set rs = db.openrecordset("Table1",dbopendynaset)
while not rs.eof
rs!ID2 = Nz(Dlookup("ID","Table2","VendorNumber=" & rs!VendorNumber)
rs.movenext
wend
rs.close
set rs = nothing
set db = nothing

Basically this says, loop through all of Table 1's records. For each record, look up the ID field from Table 2 where the Vendor Number is equal to the Vendor Number of the current record in Table 1.

Now, you might run into issues if any of the values are NULL, but if that's the case you can wrap it inside of an NZ function and don't set the value.

Hope this helps.