Results 1 to 7 of 7

Thread: Copy form field to another table

  1. #1
    Dega is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Calgary, AB
    Posts
    73

    Copy form field to another table

    I have a simple tblCustomer form with firstname, lastname, address, etc.

    And a subform called tblBookings.

    The tblCustomer "lastname" field needs to be copied over to tblGroup which has a primary key and a groupname field.

    I would rather it be copied over than have the user enter it in a text box which could result in a typo and make the form less confusing for double entries. (The lastname is used as an identifier for persons travelling in a group)

    The idea would then query the tblGroup with a combo box type field and choose the desired groupname for entry in the tblBookings.

    I think I can do this on the frmCustomers: QueryBuilder properties but not sure how to do it.

    Thanks

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370
    off the top of my head...

    UPDATE tblGroup
    SET lastname = Forms!myForm!myControl
    WHERE primarykey = (SELECT primaryKey FROM tblCustomer WHERE lastname = Forms!myForm!myControl)

    i THINK this will work.

  3. #3
    Dega is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Calgary, AB
    Posts
    73
    UPDATE tblGroups
    SET lastname = Forms!frmCustomers!myControl
    WHERE GroupID = (SELECT GroupID FROM tblCustomers WHERE lastname = Forms!frmCustomers!myControl)

    Thanks ...

    What does the myControl (action button?) refer to and will this query run when the form is submitted or should I use a "button" control to update it.

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370
    the myControl would be most likely a combobox containing customer names. theres threads in here about creating a realtime customer search txtbox/combobox team which i think will help you a lot here. You would then have a button that would run that query.

    One concern I do have is that there's a good chance you will come up with multiple customers with the same last name. I'm not sure how it's unique like you said it is. But if you have a way to do it, go for it.

    Also, this is assuming there is already an entry for this customer in your group table. It is merely updating the lastname and not changing any other information. Are you looking to create a new entry in your group table?

  5. #5
    Dega is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Calgary, AB
    Posts
    73
    Yes, it would be a new entry in the tblGroup.

    As far as duplicate entries the lastname may have to have the firstname inital appended to it because no duplicates are allowed in the table. This is not going to be a big problem as it doesn't happen often on these tours.

    My idea was fill out the tblCustomers with personal info then submit it. This is where I need the "lastname" copied over to the tblGroups.

    THEN on the tblBookings subform which I have done some work on get the rest of the details that are needed. A query on the tblBookings as you suggested would retrieve the "lastname" and use it as a groupname. (combo box).

    So this would be a new entry in tblGroup (GroupID, GroupName).

    Thanks for your input.

  6. #6
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370
    replace the query with:
    INSERT INTO tblGroup (lastname)
    VALUES (Forms!frmCustomers!myControl)

    This will create a new record in tblGroup and insert whatever is in the control into the lastname field. if you want more fields, just add more field names in the INSERT INTO and put more values into the VALUES, in the proper order.

  7. #7
    Dega is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Location
    Calgary, AB
    Posts
    73
    Thank you for your help.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-02-2010, 06:42 AM
  2. Replies: 1
    Last Post: 03-13-2010, 06:38 PM
  3. Replies: 0
    Last Post: 10-15-2009, 01:08 PM
  4. Replies: 5
    Last Post: 06-17-2009, 01:18 PM
  5. Making a backup copy of table or database
    By wasim_sono in forum Access
    Replies: 0
    Last Post: 03-09-2006, 03:44 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
  •  
Tech Forums: Microsoft Office Forums