Results 1 to 3 of 3
  1. #1
    hunths is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    1

    Using a form based on a query, using On Click on form to update records in a table

    In Access 2010 I need to copy data from physical address fields in a table to mailing address fields in the same table if a yes/no checkbox is selected on a form. It seems like this would be very easy; however, I keep trying different things and nothing works.



    When I click on the checkbox on the first record in the form, nothing gets updated. When I select the checkbox on the second record shown on the form, the first record in the table gets updated instead of the second record in the table. Also, I can’t get the updated records to show on the form once the fields get updated.

    Right now I have a macro attached to the On Click of the checkbox and the macro is supposed to run a SQL query.

    My SQL query is:

    UPDATE tblVenues
    SET ContactMailingAddress = ContactPhysicalAddress, ContactMailingSuite = ContactPhysicalSuite, ContactMailingCity = ContactPhysicalCity, ContactMailingState = ContactPhysicalState, ContactMailingZip = ContactPhysicalZip, ContactMailingCountry = ContactPhysicalCountry
    WHERE ContactMailingAddressSame = true;

    My macro is:

    OpenQuery
    Qry Name: qrySQLUpdateMailingAddresses
    View Datasheet
    Data Mode Edit
    Stop Macro

    Any help would be greatly appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you try running your query directly from the query designer?

  3. #3
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    If I understand your question right, what's happening is the record is not updating when you update it, but it is waiting until you update the next record before visually updating itself.
    If you were to update the second record first, nothing would happen, but then if you were to update your first record, the second record would update. (Okay, that's kind of confusing).

    If I understand your question right, what you need to do is essentially refresh your table and update the RecordSource.
    Through VBA this can be done Me.RecordSource = Me.RecordSource (assuming it's a form, and not a subform).

    I don't use macros, so I'm not entirely sure how to do it with a macro, but you might want to try Requery.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-27-2015, 07:19 PM
  2. Replies: 5
    Last Post: 11-29-2014, 10:19 PM
  3. Replies: 12
    Last Post: 11-11-2014, 02:10 PM
  4. Replies: 2
    Last Post: 03-30-2014, 04:57 PM
  5. Replies: 6
    Last Post: 08-30-2012, 06:23 PM

Tags for this Thread

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