Results 1 to 7 of 7
  1. #1
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22

    Lightbulb Query to Locate Information from One table and after form input, post data to different table

    I am having issues trying to create a query that will allow me to lookup information from one table, populate the data into a Form, then allow additional input into the Form and with the click of a button, post both the looked up and entered data into a different table than where the lookup was performed.



    I am looking up name and address information from a table called Roster by an employee ID [EID] and populating part of a Form when it opens. Then I need to enter shipping information into the bottom half of the Form and then save all of the data (including the address info) into a table called [WAH_Shipping_Detail]. I can get the data to populate for the name and address. I've tried to join the two tables in a query, but when I do that I get a list of all of the records that reside in the WAH table. I am unable to input any information or edit what is pulled up.

    I have spent 4 days on this and just can't get the right combination to make it work. Oh yeah, I'm not a whiz with SQL either.

    If anyone can assist me, I would GREATLY APPRECIATE it!

    Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    append query.
    use the key for the record in the form, to pull data in the query to be added to the target table and any textboxes on the form too.

    similar to:
    INSERT INTO tPickedList ( PickKey, Name, State )
    SELECT tNames.ClientID, [LastN] & ", " & [FirstN] AS Name, [Forms]![frmPick3]![cboState]
    FROM tNames
    WHERE (((tNames.ClientID)=[Forms]![frmPick3]![txtID]));

  3. #3
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    I have 20 fields on my form that are not tied to a query, because they don't exist in the source table where I lookup the name and address info. Should I create the fields in the Parameter query as a new variable field and then insert all of the fields into the data table? Right now, I am unable to input any data into my form. It is as if the Parameter query is blocking the data and combo box input/selections. This is the first time I have tried to lookup with a Parameter query and then input data into the same form to save in a detail table.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.

    Identify the objects of issue.
    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
    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
    I recommend that you give readers a clear, simple 30,000 ft overview of your application to help us understand what you are dealing with. Also, help us help you by telling us what the form(s) is for.

    What does this mean in detail?
    I have 20 fields on my form that are not tied to a query, because they don't exist in the source table where I lookup the name and address info.
    You haven't provided enough info to get focused responses.
    Good luck with your project.

    [/COLOR][/I]

  6. #6
    DaKetch is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    United States
    Posts
    22
    Thanks for the replies! I have figured out how to capture all of the data from the form by writing code that assigns all of the form data to variables that I just need to append to the Detail table. Now I just need to figure out how to write the SQL in the Code to append the data to my Detail table.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A lot of concatenation of literal text and variables.
    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.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-17-2015, 11:04 AM
  2. Replies: 3
    Last Post: 08-03-2014, 10:12 PM
  3. Replies: 3
    Last Post: 03-07-2014, 10:39 AM
  4. Replies: 6
    Last Post: 11-15-2012, 05:38 PM
  5. Replies: 2
    Last Post: 02-02-2011, 06:39 AM

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