Use Form to Update Existing Table Rows

[pkjividen]

I need help with updating existing rows in a table with a form. I have a table that lists invoice information (vender, date, amount, paid?, etc). I need a form that a user can enter the invoice number and the information will pop up and then be able to go to the field that says paid and click the dropdown saying it is paid. Should be easy but I have tried combo boxes that bring up the information but will not let me change the paid field. I do not want to add another row in my table, I only want the existing row to show paid in the table. Can you advise how to create the form properly so that the user does not have to open the table to accomplish this goal?

Thanks.

pkjividen

[isladogs]

First of all, you shouldn't use special characters like ? in field or control names

Add code to the After_Update event for your invoice field
If you just want to move to the Paid control then something like:

Private Sub InvoiceNumber_AfterUpdate()

If Me.InvoiceNumber<>"" Then
   Me.[Paid?].SetFocus
End If

End Sub

If you want to populate the field then perhaps this would work:

Private Sub InvoiceNumber_AfterUpdate()

If Me.InvoiceNumber<>"" Then
   Me.[Paid?] = "Paid"
Else
  Me.[Paid?] = ""
End If

Me.Requery 'update display

End Sub

Modify all names to suit your control names

[pkjividen]

I tried this code but instead of updating the existing line for that invoice number as paid, it created a new line with the same invoice number and put paid in the table column.

[June7]

User is editing an already existing record? Does user need to see the record? If not, just run an UPDATE action. Assuming invoice number is a text type field:

CurrentDb.Execute "UPDATE Invoices SET Paid=True WHERE InvoiceID='" & Me.cbxInvoice & "'"

But how do you know the invoice is paid? How can you verify user is correctly identifying invoice as paid? Do you have a table of Receipts? Do you enter the invoice number as a foreign key in this table? Does the total receipts for an invoice equal or exceed the invoice amount? Whether or not an invoice is 'paid' can be determined by calculation and then don't have to rely on user input.

Another approach is not to associate payments directly with invoice but to a customer account record. Invoices as debit and receipts as credit and the net balance determines account status.

Otherwise, need code that first locates and sets focus on record. Review http://allenbrowne.com/ser-62.html

Control used to input search criteria must be UNBOUND, unless you use the intrinsic search/filter tools, not VBA.

[isladogs]

I tried this code but instead of updating the existing line for that invoice number as paid, it created a new line with the same invoice number and put paid in the table column.

I gave you two different codes - which did you use? Neither should create a new record
June's response is another way of doing what I implied in the second bit of code