Results 1 to 5 of 5
  1. #1
    pkjividen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2018
    Posts
    2

    Use Form to Update Existing Table Rows

    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

  2. #2
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,761
    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:

    Code:
    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:

    Code:
    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
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  3. #3
    pkjividen is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2018
    Posts
    2
    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.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,041
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,761
    Quote Originally Posted by pkjividen View Post
    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
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update existing table in Access from Excel using VBA
    By rscott1989 in forum Import/Export Data
    Replies: 3
    Last Post: 11-25-2015, 01:20 PM
  2. Replies: 7
    Last Post: 03-22-2015, 02:29 AM
  3. Changing data in existing rows in a table
    By Zekii61 in forum Access
    Replies: 8
    Last Post: 10-06-2013, 02:57 PM
  4. Replies: 7
    Last Post: 09-20-2013, 09:14 AM
  5. Replies: 3
    Last Post: 05-23-2012, 07:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums