Results 1 to 4 of 4
  1. #1
    Pianopizza5 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    2

    Setting up forms so don't have to remember and enter primary keys multiple times

    Hello,



    This is my first post, so I apologize if I am not doing something in the right way. Additionally, I looked for information on this subject in the forum but did not see anything. I am creating forms for my database and am not sure what is the best way to set them up so that I don't have to enter the primary key information multiple times.

    I have three tables: Location, Meeting Details, and Point of Contact (POC). One Location can be for many meetings (one to many) and one POC can be for many meetings (one to many). The Location ID is set to Autonumber as the primary key of Location and the POC ID is set to Autonumber as the primary key for Point of Contact. Meeting Details contains the Location ID and POC ID as foreign keys.

    The optimum situation would be to enter the information in this order: Meeting Details (date and time), Location, POC. However, this wouldn't work due to referential integrity rules, as Location and POC must have a record before the foreign key can be entered in the Meeting Details. So, I entered the information in the Location and POC tables first. My problem is that if I am using a form (or table) to enter the information in Meeting Details, I have to remember the Location ID and POC ID. Is there a solution to this? This is a pretty simple set up, so I am thinking there should be an answer to this. I have tried using queries and subforms (or is this the answer?).

    Thank you!

    Location
    - LocationID (primary key)
    - LocationName
    - Address

    Meeting Details

    - Day
    - Time
    - LocationID (foreign key)
    - POCID (foreign key)

    Point of Contact
    - POCID (primary key)
    - Name

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Can you upload a zipped copy of your database ?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    First of all, Day and Name are reserved words and should not use reserved words as names for anything. Use MtgDate and ContactName instead. Advise not to use spaces nor punctuation/special characters in naming convention.

    Options for form design:

    1. one form with comboboxes to select location and contact

    2. main form bound to Location and subform bound to MeetingDetails with combobox to select contact

    3. main form bound to Contacts and subform bound to MeetingDetails with combobox to select location

    VBA code can dynamically set DefaultValue of combobox in first record of session so value will automatically carry forward to subsequent new records until user changes or form closed. http://access.mvps.org/access/forms/frm0012.htm

    Can also use combobox NotInList event to add new location or contact on-the-fly during data entry.

    These techniques are subjects of numerous discussions and many examples available.
    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.

  4. #4
    Pianopizza5 is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2019
    Posts
    2
    Thank you very much for your reply. It really helped me!

    Please find my solution below (in case others have a similar problem):

    The answer was to create a combo box with multiple values. For example, say I wanted to enter an event using a form but I didn't want to have to remember the primary key of the Location (number), using a combo box would solve this.

    - In Design View, select the Combo Box in the Controls section of the Design tab and drag it onto the form.
    - Select the Table:Location, then click Next.
    - Move the LocationID and LocationName fields into the "Selected Field" window by pressing the right arrow. Select Next.
    - Sort them by the LocationName. Click Next. Proof how the column will be displayed and press Next.
    - Make sure that "Remember the value for later use." is selected. Click Next, type the combo box name, and then click Finish.

    - Check that the combo box is selected and then open the Property Sheet if it is not already opened (press F4).
    - Click the Data tab.
    - In Control Source, click the down arrow and select ID. This is the field where the value that you enter will be stored. (In this case, we want the ID to be stored.)
    - Click the Format tab.
    - The Column Count should read 2 and the Column Widths should read 0";1". What this means is that the combo box contains two pieces of information (the LocationID and LocationName), but we only see the LocationName. Then, when we select an item the LocationID is stored in the ID field.
    - Switch to Form View to test it out. Notice that you can enter in the location by typing it out, which is a lot easier than memorizing the ID Numbers.

    Note that if a Location is has not yet been entered in the Location Table that it will not appear. Thus, in my example above, the Location and Point of Contact must be entered in their respective table before they can be entered in the Meeting Detail form.

    June7, thank you again for all of your helping and for pointing me in the right direction.

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

Similar Threads

  1. Replies: 10
    Last Post: 08-12-2015, 10:21 AM
  2. Primary Keys for Tables & Forms
    By Tantum4 in forum Access
    Replies: 9
    Last Post: 02-04-2015, 06:07 AM
  3. Replies: 19
    Last Post: 03-11-2014, 10:02 PM
  4. Multiple Primary Keys
    By JonathanOz in forum Access
    Replies: 2
    Last Post: 03-22-2013, 08:32 AM
  5. Multiple primary keys
    By elektrisk in forum Access
    Replies: 5
    Last Post: 02-11-2010, 04:39 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