Update record via event procedure

[round]

Hello all,


I hope someone can help.

How do I update a record via an event procedure?
I will try to explain...as best I can...
In my access db I have a button that will produce (via an event procedure) an invoice/report for a given customer of all transactions to date. What I want to do is update the transaction records that are shown on the invoice when it is produced as "already invoiced" (a field in my transaction log table) so that if the customer makes more transactions after the last invoice was produced they do not appear on the next invoice so it will only show records which have occurred since last invoice.
Could I call a macro via the event procedure? If so, how?

Did I make sense??

Here is the current event procedure vb:

Private Sub invoice_Click()
On Error GoTo Err_invoice_Click
Dim stDocName As String
stDocName = "rpt_invoice"
DoCmd.OpenReport stDocName, acPreview
Exit_invoice_Click:
Exit Sub

Err_invoice_Click:
MsgBox Err.Description
Resume Exit_invoice_Click

End Sub

Many Thanks.

 

[khawar]

You can creat a yes/no field in table and on form which is marked after invoice is viewed

Private Sub invoice_Click()
On Error GoTo Err_invoice_Click
me.fieldname=true *you use invoice viewed or something else as the name of field here*
Dim stDocName As String
stDocName = "rpt_invoice"
DoCmd.OpenReport stDocName, acPreview
Exit_invoice_Click:
Exit Sub

Err_invoice_Click:
MsgBox Err.Description
Resume Exit_invoice_Click

 

[round]

Thanks Khawar but the check box would need to be on a sub form. I have a form which displays customer details then a sub form which scrolls through the transaction records. So the check box would have to go on the sub form so that each individual transaction could have one.

Many Thanks

 

[khawar]

you have to use only one check box in the design view and it will be displayed on each row

 

[aling2507]

query by form

1. Do you know query by form ?

 

[aling2507]

1. I think , kawar want you to add the new field (yes/no)
2. Query by form ( filter by form)

 

[round]

I ended up using a yes/no field which can be updated via a query and then I used the vb code:
DoCmd.RunMacro "update_invoice_raised"
as part of the event procedure to call a macro that fires the update query.
Worked out quite useful as I was able to have messages pop up explaining what is happening from the macro.

Thanks for all help