Updating Multiple Records at once

[AvesRule33]

I hope someone can help me with this. I know I have done this before, but for the life of me for some reason I can not come up with a solution to make this work.

I have a Form [frmPayInvoice] which is based off of a select query result [qryPayInvoice] which is pulling data from my [Repairs] table.

What I need to be able to do is for the user to be able to check the Yes/No Checkbox for all of the invoices that they are going to pay (Which automatically changes each record in the [Repairs] table to "Yes" - this works).

What I am having trouble with is that I also have several bound fields [DatePaid] and [CheckNo] on this form that I want the user to only have to enter once and it will update all the records that have a Checkmark in the Checkbox [PayInvoice].

I have this running off of a simple Macro trying to use a simple Update query [qryPayInvoiveUpdate]. The query is written like this:

Field: DatePaid CheckNo InvoiceNo
Table: Repairs Repairs Repairs
UpdateTo: [forms]![frmPayInvoice]![DatePaid]
[forms]![frmPayInvoice]![CheckNo]
Criteria: (This is only under the [InvoiceNo] field)
[forms]![frmPayInvoice]![InvoiceNo]

When I run the update query it will only update the last record with the DatePaid and CheckNo values.

I also tried using the [InvoicePaid] = Yes, but then all of my records get updated with the DatePaid and CheckNo values - not just the records on the form that I have selected.

If anyone could offer up any suggestions on how to get these records to update correctly I would Greatly Appreciate it.

 

[PJ44]

Hey Aves -

If I were to do this, I'd use a SQL statement in VBA that would run when an "Update Now" button is clicked. I did a quick test and here is what it looks like.

Private Sub cmdGo_Click()
    Dim strSQL As String

    strSQL = "UPDATE Repairs SET DatePaid = '" & Me.NewDate.Value & "', CheckNo = '" & Me.NewCheck.Value & "', PayNow = 0 WHERE PayNow = True"
    DoCmd.RunSQL strSQL
    Me.Requery
    
End Sub

Private Sub PayNow_Click()
    Me.Requery
End Sub

NewDate and NewCheck are textboxes in the Form Header.

cmdGo is the button in the form footer that the user clicks.

PayNow is the checkbox that the user would click signifying they are paying on that record. This field is a record in the table. I did this so the SQL update statement can find the proper records in the table. The requery for PayNow_Click is just so the table is updated prior to the SQL running. There may be a better way to do that, but I'm no expert.

After the SQL string runs, I requery the form again to display the new info.

Hope this helps.

 

[AvesRule33]

Thanks for your response PJ44. This works to update all of my fields (I did have to change the PAYNOW = -1 so that the the checkbox stays marked as Paid).

Now the issue I have is that this code still changes all records in my REPAIRS table with a Checkmark in the PAYNOW field - thus it is overwritting all records with the new values.

Also, I want the records to stay on the form until the user clicks the UPDATENOW button so that they can verify all the Invoices that they want to pay are checked. With the Me.Requery it takes them out of the form as soon as you click the checkbox.

 

[PJ44]

In the test db I did, the requery resorted them, but did not remove them from the table. Instead of changing PAYNOW = -1, my brain (faulty thought it may be) would set it back to 0 and create an on Load event on the form that says "If Not IsNull(DatePaid) Then PayNow.Enabled=False". Or better yet, tweak the query so that it only displays records where DatePaid is Null. That way, the records stay visble in the form as long as DatePaid has not been filled in. Once the user clicks the button, DatePaid is filled in and the requery removes it from the form leaving only unpaid records.

Hopefully this is making sense. I'm no expert by any means lol. Let me know if you have more questions. Together, we'll figure it out!

 

[AvesRule33]

OK - now that part works beautifully!!! Thanks A Million.
My only issue now is that my Repairs table is still not keeping the checkmark in the checkbox for those records that are paid.

No need to apoligize for not being an expert - obviously I am not either (Not even close). Just love building these databases and learning new tricks! Wishing I had more VBA and SQL experience - I only know enough to get me into trouble

 

[PJ44]

Hey man, thanks for the star.

And if you want to keep the paynow box checked for the records that have been paid, just remove the PayNow = 0 from the UPDATE string. I'm not real sure why I originally included that unless it was to ensure that all records were viewable in the query. Removing that should take care of it for you.

Regards,
PJ

 

[james33]

Dear AvesRule33,

Just as a matter of interest; how do you select the records and print out the receipt for those that have been paid, does this happen before or after these events we are talking about?

Regards
Jim