VBA Missing Object

[KrissyB]

One of my first attempts at VBA:

Option Compare Database

Function Vendors()
Dim CurComm As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim VendorID
Dim i

Set curDB = CurrentDb
Set CurrConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "datasource=" & curDB.Name
End With

Set RS = New ADODB.Recordset
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS.Open "Select * From INVUPC;", CurConn, , , adCmdText
i = 0
Do Until RS.EOF
For Each VENDOR In RS.Fields
VendorID(i) = VENDOR.Value
i = i + 1
Next
RS.MoveNext
Loop
RS.Close
CurConn.Close

End Function

What I want to do next is to create a loop to get result sets in a select format kinda like this:

SELECT Vendor, UPC from INVUPC WHERE Vendor=(VendorID from above loop) and output these results to seperate tables.

Any help would be great.

 

[Trendsetter]

Hi Krissy,
With the output you have here you could use the same loop to update another table just open another recordset from a table as well as RS say RSNext and add the values from RS to RSNext in the loop:
RSNext!VendorID = RS!VendorID

Note:
At the end of your recordsets remember to Set = Nothing to clean your memory!

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[KrissyB]

Thank you very much! This has been helpful, I am currently trying to get it to work for me!