Results 1 to 5 of 5
  1. #1
    tx_developer is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    2

    Building Input Form Based on Two Tables

    Hi,

    It has been a very, very long time since I have used MS Access. I am building a small database and form for a friend and now can't seem to remember how to do things.

    I have two tables - CLIENT and SESSION



    CLIENT has client_id plus name, address, phone, etc.
    SESSION has session_id, client_id, session_date, price

    I have a one-to-many join between client and session.

    This application will have several forms and one of them is for entering any time a client came in for a session and how much they paid. On this screen or form I want to display the client's name (rather than the ID of course) and let the user input the date and price paid. For some reason I cannot remember how to do this.

    Would I create a query with the fields I want and make it an update query? If someone could point me in the right direction I would very much appreciate it.

    Thank you,
    tx_developer

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Typically a one-to-many relationship is handled with a form bound to the one table containing a subform bound to the many table. Master/child links keep them in sync with each other.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    As Paul has suggested, a Main Form/Subform is the way to go, here:

    1. Make a Form using the CLIENT Table
    2. Set its Default View to Single View
    3. Make a Form using the SESSION Table
    4. Set its Default View to Datasheet View
    5. Open the Client Form in Form Design View
    6. Place a Subform Control on the Form
    7. When the Subform Wizard comes up, select the Form based on the SESSION Table for its Source Object
    8. The Wizard should ask if you want to link the two Forms using the client_id Field; say Yes

    Bob's your uncle!

    As you move thru the Records in the Clients Form, the Records in the Sessions Form will display appropriately.

    If you go to enter a new Sessions' Record, Access will automatically insert the client_id from the Current Client Record into that Record.

    If you find you have a need for things like Command Buttons on the Subform, you'll need to set the Default View for that Form to Continuous, which allows buttons, instead of Datasheet View, which doesn't. You can then tweak the design of the Continuous Form so that it looks like a Datasheet, if that's the look you want.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    tx_developer is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    2
    Awesome...thank you both. I am on the right track but have one issue. The SESSION table also has a package_id from you guessed it, the PACKAGE table which has things like Groupon, Living Social, and other packages. I created a drop down (or combo box I guess) to display the package name. When I drop down and select a package for one session record, all the other session records are set to the same package name. When I created the combo box with the wizard, if asked at one point if I want Access to remember the value. Not sure if that's why it is doing this or not but now I can't find how to change it.

    Thanks,
    ~TX

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The Combobox for Packages needs to be Bound to a Field in the underlying Sessions Table, in order for it to be Record-specific.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. View Data based on Form Input?
    By 0REDSOX7 in forum Forms
    Replies: 7
    Last Post: 11-29-2011, 10:25 AM
  2. Run a query based on an input from a form
    By apoorv in forum Queries
    Replies: 4
    Last Post: 07-11-2011, 01:39 PM
  3. Update two tables from one input form
    By Jeff-H in forum Forms
    Replies: 7
    Last Post: 09-26-2010, 10:44 AM
  4. Replies: 2
    Last Post: 06-17-2010, 04:15 PM
  5. Replies: 1
    Last Post: 06-14-2010, 02:31 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