Table Design & Auto-Populating a Subform

[Plaxerous]

Thanks in advance for anyone who can help!

I am helping a food safety audit company build a database that tracks their restaurant audits. I am having difficulty auto-populating a subform with default entries, as well as entering more than one record per primary key.

The descriptive information for the restaurants is hosted on a linked table in a separate database:

tbl_Restaurants
REST_SITE_CD
Restaurant
Address
City
State
Zip

The first table I made will be used to join a user-selected site code and comments with the descriptive information in the linked table via a query:

tbl_SiteCode
SiteCodeID (PK)
SiteCode
SiteComments

The query has a join arrow pointing from the SiteCode of tbl_SiteCode to the REST_SITE_CD of tbl_Restaurants

qry_Restaurants
[All fields from tbl_Restaurants]
[All fields from tbl_SiteCode]

My second table contains the audit metrics that are important for generating reports

tbl_Metrics
MetricsID (PK)
MetricsSiteCodeID (FK)
MetricsType
MetricsDate
MetricsCount
MetricsComments

The SiteCodeID from the first table has a One-To-Many relationship with the MetricsSiteCodeID from the second table.

MetricsType should always be “Violations Identified”, “Violations Reported”, and “Violations Aligned” for every SiteCodeID report.

Afterwards, I created two forms – one from qry_Restaurants and one from tbl_Metrics. I inserted frm_Metrics as a subform into frm_Restaurants.

I would like frm_Metrics to auto-populate the MetricsType field with the 3 “Violations” entries as specified above upon the insertion of a new record. My attempt so far at creating an AfterInsert() event on frm_Restaurants is as follows:

Dim strSQL As String
strSQL = "INSERT INTO tbl_Metrics (MetricsType) " & _
"VALUES (" & "'" & "Violations Identified" & "'" & ")"
CurrentDb.Execute strSQL
frm_Metrics.Requery

However, the entry does not show up in the subform, but indirectly appears in tbl_Metrics while lacking a MetricsSiteCodeID.

Additionally, I am not sure what to do if the user does two audits of the same restaurant site. Because each record in tbl_Restaurants is tied to a primary key SiteCodeID, I am unable to generate more than 1 record using the same SiteCode.

Am I arranging my tables incorrectly, or is there something else I’m missing?

 

[jdraw]

I realize that this is your first post here and I note that you are knee-deep into a physical database. That's an approach, and it could work, but I would recommend you start with a good description of a "restaurant audit", and what is required to be recorded. If you model the "things involved" with pencil and paper, then add some "test data and scenarios", you may gain more insight by "working and adjusting the model" to ensure things match your intent. You then have a blueprint from which to proceed with physical database.

Here is a link to info I have collected on Database Planning and Design that may be helpful to you.

Welcome to the forum and good luck with your project.

 

[Plaxerous]

jdraw,

Thank you very much for the resources – I will read through the articles as time allows!

During each restaurant audit, violations are tracked in the form of “Violations Identified”, “Violations Reported”, and “Violations Aligned”. Each of these entries have a number/count associated with them, and a unique date when they are finalized.

At a higher level, these violations are tagged to restaurant sites that have a unique ID. The ID and descriptions of these restaurant sites, such as name, address, etc. are located on an existing table.

For the ease of the user, I would like a form where the user can select a restaurant based on its descriptive information in the main form, and input the dates and comments associated with each violation entry in the subform. However, it is important that the violation entries in the subform remain consistent for report generation (e.g. not have “Violations Identified” and a misspelled “Violations Iedntified”).

These are the reasons why I’d like a subform to auto-populate with Violation entries, and to help brainstorm of a way to generate more than one record per restaurant site in case more audits are done of the same restaurant in the future.

Hope this helps? Thanks everyone for their time again!