Many form records to many table records

[Anne]

This is a repost from August 29


I have a continuous form that lists all of the order
details for a particular order. I've added a button that
lets the user invoice this order. What I want to do is
have the code behind the button add a record to the
invoice header table (1 record) as well as add a record
for each of the order details displayed on the form to the
invoice details table (many records - 1 for each order
detail).

The code I have written so far adds the invoice header
correctly but I can't get any more than one record in the
invoice details table.

VB Help says I can use a For Each or nest With statements
but I 'Think' I am having trouble at the syntax level.

For Each Me!Fieldname or Me.Fieldname in Form is
unacceptable.

Nested With statements may be the better way to go but
again, if I reference either a field within the form or
the form itself I get only 1 record added to the invoice
details table.

Any and all suggestions welcome

Anne

 

[Jen]

Hi Anne,

Looks like the first thing you need to do is count the
records on your form in order to add the appropriate
number of recs to the invoice_detail table. So, on
the "invoice" button on the order details form you would
do something like this:

Invoice_Button On_Click()
Dim rs As DAO.Recordset
Dim intCount As Integer
' ensure you have a reference to
' MS DAO 3.6 library (for A2K2)
Set rs = Me.Recordset
rs.MoveLast
intCount = rs.RecordCount

Call addInvoiceRecs(intCount, Me.Order_id.value)
'I'm assuming you'll want to populate the order_id in the
'invoice table - probably a foreign key?? If not, don't
'pass the second argument.

End Sub

Sub addInvoiceRecs(numRecs As Integer, OrdID As String)
Dim intCounter As Integer
Dim db as Database
Dim rsInvoice as DAO.Recordset
On Error GoTo err_Invoice_Recs

Set db = CurrentDB
Set rsInvoice = db.OpenRecordset("Invoice_Table_Name")
With rsInvoice
For intCounter = 1 to numRecs
.addNew
.Fields("order_id").Value = ordID
.Update
Next intCounter
End With
rsInvoice.Close
Set rsInvoice = Nothing
Exit Sub

exit_Invoice_Recs:
Exit Sub
err_Invoice_Recs:
MsgBox err.Description & vbCrLf & err.Number
rsInvoice.close
Resume exit_Invoice_Recs
End Sub

Regards,
Jen