How do I create a form to input new records into a table that contains only ID values from other tables?

user_19139991 0
Viewed 42

I am new to databases, and I am working on a final project for a class. I have a database with tables related to each other as shown in the diagram here:

enter image description here

I want to create an unbound form that allows a user to add a new purchase to the purchase table by choosing their name, category, and store from data already in these tables, and then add purchase amount and date.

Since the purchases table does not contain the names of people, categories, and stores themselves, only the ID values from these tables' fields, I am struggling with how to create a form that will add the correct IDs into a new record in Purchases based on the names from other tables.

I am wondering if this requires VBA? I have tried playing around with the property sheet on forms, but I am struggling with which properties to address/what to do with them.

If anyone can explain at least a starting process to create this form.

ms-access forms sql
1 Answers

Simply, use combo boxes that query Buyer, Category, and Store table data, hides the primary key ids, but shows the corresponding lookup value to the user. Users will select by the lookup value(s) but really are saving the id to Purchases table as new foreign key id.

As commented above, use a bounded form to map combo boxes to table id fields. Once you place a combo box, the default wizard will guide you on the steps but below are key property sheet attributes (which may be automatically set with wizard but can be manually adjusted later).

Data

  • Control Source: The column in table (i.e., PurchaseID, BuyerID, CatID, StoreID) to store the user-selected data of combo box (i.e., a form control).
  • Row source: A distinct SQL query of primary table id and all needed values for human searches. This can be a named table or saved query or an inline SELECT statement.
  • Row Source Type: If using SQL, Table/Query.
  • Bound Column: The position of primary table ID in the query resultset to be stored as foreign key Id. Usually this would be the first column.

Format

  • Column Count: The total number of columns from the recordsource including hidden, bound column.
  • Column Widths: To hide column from view, set its positional number within semicolon delimiters to zero. Preview form to decide how large to space out other columns. Do note: you can extend beyond the Width of combo box using List Width.
  • Column Heads: Optional and best if more than one column to guide users on the lookup value content (e.g., First Name, Last Name).

As example, for Category combo box on bounded Purchases table form, consider below property values:

  • Control Source: CatId
  • Row Source: SELECT CatId, CatName FROM Category
  • Row Source Type: Table/Query
  • Bound Column: 1
  • Column Count: 2
  • Column Width: 0";2.5"
  • Column Heads: No
user_14224510
Related