One record at time

[Stapes]

Hi

Can I do a select like so: -

SELECT TM_InvoiceItemGroup.Field3, TM_CompContact.AcNo
FROM TM_InvoiceItemGroup LEFT JOIN TM_CompContact ON
TM_InvoiceItemGroup.Field3 = TM_CompContact.AcNo
WHERE (((TM_CompContact.AcNo) Is Null));

and then step through the record set one record at a time? For each of
these I must first check if a Company record exists, and if not create
it. Then create the Contact record (which is what the above query was
looking for.

Stapes

 

[Stapes]

Hi

Can I do a select like so: -

SELECT TM_InvoiceItemGroup.Field3, TM_CompContact.AcNo
FROM TM_InvoiceItemGroup LEFT JOIN TM_CompContact ON
TM_InvoiceItemGroup.Field3 = TM_CompContact.AcNo
WHERE (((TM_CompContact.AcNo) Is Null));

and then step through therecordsetonerecordat atime? For each of
these I must first check if a Companyrecordexists, and if not create
it. Then create the Contactrecord(which is what the above query was
looking for.

Stapes

Hi Again.

Did nobody know the answer to this?

Stapes

 

[Lior Montia]

try this:

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("")
qdf.sql = "SELECT TM_InvoiceItemGroup.Field3, TM_CompContact.AcNo
FROM TM_InvoiceItemGroup LEFT JOIN TM_CompContact ON
TM_InvoiceItemGroup.Field3 = TM_CompContact.AcNo
WHERE (((TM_CompContact.AcNo) Is Null))"
Set rst = qdf.OpenRecordset(2, 512)
rst.movefirst
while not(rst.eof)
xxxxxxxxx
xxxxxxxxxxx
rst.movenext
wend

write your code to update instead of xxxxxxxxx

 

[Douglas J. Steele]

You can step through any recordset using code like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT TM_InvoiceItemGroup.Field3, " & _
"TM_CompContact.AcNo " & _
"FROM TM_InvoiceItemGroup " & _
"LEFT JOIN TM_CompContact " & _
"ON TM_InvoiceItemGroup.Field3 = " & _
"TM_CompContact.AcNo " & _
"WHERE (((TM_CompContact.AcNo) Is Null))"

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do Until rsCurr.EOF
' do work here
rsCurr.MoveNext
Loop

Having said that, though, it's seldom efficient to loop through a recordset
in VBA if you can simply use a SQL query, and from the sounds of it, you
should be able to create an INSERT INTO query that simply generates the
necessary records. Unfortunately, you haven't given enough details for me to
actually write the SQL for you.