fill in checkbox after runnin query

[megatronixs]

Hi all,

I have a query that produces a report for charges to the client. On the form I have a checkbox that when the client is charged it is ticked. What would be better is that when I run the query to export to excel, it should check all those checkboxes and the ones that are blank, fill them in (tick the box). Then I need to add to the query that that field should contain only blanks so it will show me only the ones that where not charged and then after producing the report check those boxes so it keep track of all the clients charged (to avoid double charge)

I hope some one can help out as this is driving me crazy as I don't know where to start.

Greetings.

 

[megatronixs]

Hi all,

I found that I can use a change query. The only thing is that if ther is a condition it should not change it. I have a checkbox that is checked when we don't charge the client. It only should instert "True" if there is a field that has an amount (not empty and not 0).

Any clues how to do this part?

Greetings.

 

[Newie]

Hi Megatronixs,

Not sure exactly what you are trying to do ?

If I haven't got it wrong?....... it seems you could:
Run an Update query to update the Checkbox to True/Yes where it is False/No and where the monetary field <>0.
But here you will have trouble distinguishing between those you've just updated today and those you've already done earlier (your double charging Reports) so it might be worth considering either changing the Checkbox to a Date field (Date Charged) and updating that to Date() or adding a Date field alongside your Checkbox so when you produce your reports, you can specify only those where the date Charged = Date().

From here, it should not be difficult to produce a Query for those not charged (Where Checkbox = False/No and Date Charged Null and Value Field =0)

Hope this helps ???

Steve

 

[megatronixs]

Hi Newie,

Thanks for the tip ;-)
it worked when I added the date field and did as above ;-)
How Can I now combine the button that is used to run the first query to export to excel and then run the change query after the first one so it will insert the changes (but then without the message that I'm about to make changes).

This is the code on the button that I use on the export query:

Private Sub Command242_Click() 'Charges
On Error GoTo Err_Command242_Click
Response = MsgBox("Are You Sure You Want To Produce The Charges?", vbYesNo)
If Response = vbYes Then
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ChargeQuery", "C:\Charges\Charges- " & Format(Date, "dd-mm-yyyy") & ".xls", True
End If
 
    Dim stDocName As String
Exit_Command242_Click:
    Exit Sub
Err_Command242_Click:
    MsgBox Err.Description
    Resume Exit_Command242_Click
End Sub

The change query I need to run is called: "ChargesUpdateQuery"


Greetings.