Automatically control checkbox status

[GregP]

Hi,

I have an address book for my customers, and once they've completed a transaction I want to generate a receipt. The way I've done this so far is to have a field in the orders table called 'GenerateReceipt', and a button called 'GenerateReceiptButton' for each record in my tabular orders form which ultimately opens a report with just the details of that particular customer. It works like this:

When the button is pressed, it ticks the checkbox, then runs a macro which moves to the next record and then back to the current one (this is to save the status of that checkbox into the appropriate table/query), and then opens the report. The report gets its data from a query containing only the customer details who have 'GenerateReceipt' ticked, i.e. the customer whose details I just clicked the 'GenerateReceiptButton' for.


This all works fine. The only other thing I want to do is when the report has been generated and focus comes back to the form (or everything is closed for that matter), I want to untick the checkbox, so all checkboxes are false as they originally were. I originally did this by me.GenerateReceipt = False in the form current property, but when I try to generate a receipt for the last customer it reports a runtime error 'Cannot add record; join key of tblOrders not in recordset'. So if I then add the necessary join fields into the recordset and try again, it then doesn't allow me to add new records, meaning if you want to generate a receipt for the last customer in the field, there isn't a 'next' field for the macro to go to. So either way I'm stuck. If I remove the me.GenerateReceipt = False from the form current property, this error goes away.

I tried the same code in several other form properties, e.g. form_close, form_deactivate, form_lostfocus, form_gotfocus, but none of these did anything. What is the property I'm looking for? How do I get these checkboxes to clear once I either ideally come back to the form or less ideally close the form?

The only other avenue if anyone has ideas is another way to save the status of the checkboxes without going from one record to another. I tried several avenues including saving the form, query or table, but none seemed to work.

Thx in anticipation!

 

[FoFa]

If I am reading that right, you are trying to move to the next record to "Save" the current record, but it doesn't work on the last record in the record set. Gee, I wonder why. Have you thought of just updating the check box manually rather than moving off the record and back to it?

 

[GregP]

Well I had a macro which saved the current form, but that didn't work. Nor did manually pressing the 'Save' button. Macros to save the associated query or table failed because the table/query in question wasn't open. In each case, the checkbox status didn't *appear* to be saved into the table/query simply by ticking it, therefore the query remained empty and thus so did the report. Obviously for the query (and thus the report) to contain any data, the changes made in the form must be saved into the query before launching the report, and the only process I found achieved this (apart from closing it presumably) was moving to a new record, which is what presented these other problems.

I'm happy to do it another way, but this was the only procedure which seemed to work. What alternative were you suggesting?

Ultimately, the checkbox will be hidden anyway. I don't want to modify it manually, it's simply to be a control field whose status is controlled by the GenerateReceiptButton code.

 

[GregP]

It's OK, got it to work. Rather than running a macro to save the current record, I used the button wizard to hunt down the appropriate VB code for it, and incorporated it into the 'Generate Receipt' button code. For the record, this is the code. It ticks the checkbox, then saves the current record data into the table, then clears the checkbox so it's cleared by the time you come back to the form, and then opens the report.


Private Sub GenerateReceiptButton_Click()
On Error GoTo Err_GenerateReceiptButton_Click

Me.GenerateReceipt = True

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Me.GenerateReceipt = False

Dim stDocName As String

stDocName = "rptReceipt"
DoCmd.OpenReport stDocName, acPreview

Exit_GenerateReceiptButton_Click:
Exit Sub

Err_GenerateReceiptButton_Click:
MsgBox Err.Description
Resume Exit_GenerateReceiptButton_Click

End Sub


Hopefully this helps someone out there in the future!

 

[Rich]

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
has been "obsolete" for years now, use DoCmd.RunCommand acCmdSaveRecord instead

 

[FoFa]

Just one more thought, rather than basing the rpt off the check box in the table, why not just base it off the form that is open since it sounds like it would be the same thing, but you would not have to jump through so many loops to get it done.