OpenRecordset problem

[Ron Berns]

I am using Access 2003.
I am fairly new at writing code.

I have a Table (InvHdr) that contains Invoice information. I have another Table
(InvDet) that contains the detail lines of each Invoice. They are associated by
Inv_Num field in each Table.


I open a table with

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("select * from [InvHdr]")

I am able to work with each record in the InvHdr Table. Now while I have the
InvHdr table open I would like to open the InvDet Table and work with the detail
records of a certain invoice. Close the InvDet table and finish working with the
InvHdr table.

My question is, after I open the InvHdr table and set the INUM variable to
Inv_Num how do I open the InvDet table and process just the records where
Inv_Num = INUM? I think I have to use an SQL Select statement, but everything
I've tried does not work.

Any help would be greatly appreciated.
Thank You in advance.

Ron Berns

 

[Dirk Goldgar]

I am using Access 2003.
I am fairly new at writing code.

I have a Table (InvHdr) that contains Invoice information. I have another
Table (InvDet) that contains the detail lines of each Invoice. They are
associated by Inv_Num field in each Table.


I open a table with

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("select * from [InvHdr]")

I am able to work with each record in the InvHdr Table. Now while I have
the InvHdr table open I would like to open the InvDet Table and work with
the detail records of a certain invoice. Close the InvDet table and finish
working with the InvHdr table.

My question is, after I open the InvHdr table and set the INUM variable
to Inv_Num how do I open the InvDet table and process just the records
where Inv_Num = INUM? I think I have to use an SQL Select statement, but
everything I've tried does not work.

Rougly and inefficiently:

'------ start of example code ------
Dim db As DAO.Database
Dim rsHdr As DAO.Recordset
Dim rsDet As DAO.Recordset
Dim INUM As Variant

Set db = CurrentDb

Set rsHdr = db.OpenRecordset("select * from [InvHdr]")
Do Until rsHdr.EOF

INUM = rsHdr!Inv_Num

Set rsDet = db.OpenRecordset( _
"SELECT * FROM InvDet WHERE Inv_Num = " & INUM)

Do Until rsDet.EOF

' ... Process each record in rsDet ...

rsDet.MoveNext
Loop
rsDet.Close

rsHdr.MoveNext

Loop
rsHdr.Close
'------ end of example code ------

But it's not going to be very fast, opening a detail recordset for each Hdr
record. You might open a single detail recordset (outside the loop) for all
records, and use Seek or FindFirst to locate the first appropriate record.
Or you might open a single recordset on a query that joins the two tables on
Inv_Num and includes all the fields you want to process.