Results 1 to 4 of 4
  1. #1
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39

    unbound form to update multiple tables

    I've searched and can't find what I need in a similar thread. Here is my update statements in SQL:

    update
    FieldTicketHeader set sonumber=1001435 where fieldticketnumber in('MAR102208','MAR102209','MAR102210')

    update
    FieldTicketInvoice set sonumber=1001435 where fieldticketnumber in('MAR102208','MAR102209','MAR102210')

    update
    PayrollDetailTable set sonumber=1001435 where fieldticketnumber in('MAR102208','MAR102209','MAR102210')


    I am trying to duplicate this on one form in Access. I want the end user to be able to open this form and enter the old or original SO Number, the new or SO Number the field tickets are moved to and then the tickets (more than one ticket might be needed to update). I am not sure how this can be done since there are 3 tables that need to be updated. Can anyone help me with this as I am not sure if I need some elaborate query to bound the form? Maybe I am thinking in the wrong direction. Help. Thanks.


  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So the number of "fieldticketnumber" numbers can be 1 or more? Sometimes 1, sometimes 3, sometimes 5, sometimes 10??

    I think you will have to do this using VBA.

    How are you entering the "fieldticketnumber" ? Text boxes? List box?

  3. #3
    osupratt is offline Advanced Beginner
    Windows 98/ME Access 2007
    Join Date
    May 2011
    Posts
    39
    correct. currently (well currently what I have doesn't work) I have text boxes but a list box might be the way to go? I'd like to see multiple such as 2 ticket number fields work first and then I can decide I think how to handle the 'multiple' scenario. so currently I have the front-end access application used for data entry and when end users needs some things moved or changed I just run update scripts on the back end SSMS. I want to give the end users the ability to modify certain things. this being one of them. lost.




    Quote Originally Posted by ssanfu View Post
    So the number of "fieldticketnumber" numbers can be 1 or more? Sometimes 1, sometimes 3, sometimes 5, sometimes 10??

    I think you will have to do this using VBA.

    How are you entering the "fieldticketnumber" ? Text boxes? List box?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Rather than try and explain how to write the VBA, try the demo and tear it apart to see how the coding is done.

    Remember, this is just a demo.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 3
    Last Post: 06-15-2014, 01:44 PM
  2. Replies: 2
    Last Post: 02-12-2013, 07:27 PM
  3. Replies: 10
    Last Post: 07-12-2011, 11:09 PM
  4. Replies: 0
    Last Post: 05-09-2010, 08:43 AM
  5. Update Multiple tables from one form
    By KenK in forum Forms
    Replies: 0
    Last Post: 10-30-2009, 08:44 PM

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