Share via

Help building a Data Entry Form with multiple filters

Anonymous
2025-01-13T14:56:20+00:00

I'm building a database for audit results. I've got the following tables:

Site (list of sites)

Audit Host (list of hosts)

Client (List of clients)

Audit Number (creates an audit associated with a client, site, and host selected from the lists above)

Classification (list of classifications)

Observation (associated with an audit number and a classification selected from list above)

Response (associated with a specific observation)

So each audit will have an audit number. Each observation will be associated with an audit number and will have a classification entered as a dropdown selected from the classifications table. There can be more than one observation per audit, but each observation will have its own ID. I have built forms that allow people to enter new audits and observations and to add new options to the list tables.

What I need help on is building a form to enter a response to the specific observation. Ideally I would like them to be able to select an audit number from the dropdown and then select the specific observation that the response is addressing. I would like for them to be able to see the text of the observation pulled in from the observation table as well. I tried to show my relationships diagram, but I get an error when trying to upload the image or paste a screenshot.

Any help would be appreciated.

Microsoft 365 and Office | Access | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-01-13T18:18:37+00:00

    I was unclear from your original post what the Classification table is related to.  If it's not related to AuditNumber, but only referenced by Observation, then you won’t need the table to model the relationship type between AuditNumber and Classification of course, and the Classification combo box will not be correlated with the Audit combo box.  The form's RecordSource would remain the same, and be requeried in the same way.

    0 comments
  2. Anonymous
    2025-01-13T18:49:34+00:00

    The classification just relates to the classification of an individual observation and wouldn't be used for indexing. It could be used in a query or report, like a search for all critical observations, as I build the reports. For this step I am building the entry form for entering a response to a particular observation. Since the responses to observations for a particular audit would be entered at the same time, I thought that organizing by audit number would be the best way to do it.

    But I took your comment and substituted Observation for Classification and was looking into that.

    Thanks again for the input.

    0 comments
  3. Anonymous
    2025-01-13T21:38:33+00:00

    Presumably the Observation table includes an AuditNumber foreign key column, so you just need to restrict the parent form on that column with a single unbound combo box with a RowSource of:

    SELECT AuditNumber

FROM AuditNumber

ORDER BY AuditNumber;

    BTW the above shows why you should not use the same name for a table and column.  The usual convention is to use a plural or collective noun for a table, reflecting the fact that a table is asset, and a singular noun for a column, reflecting the fact that a column represents an attribute, so the above would be as follows, eliminating the ambiguity:

    SELECT AuditNumber

FROM AuditNumbers

ORDER BY AuditNumber;

    The parent form's RecordSource property would be like this:

    SELECT *

FROM Observations

WHERE AuditNumber = Forms!frmObservations!cboAuditNumber;

    In the combo box's AfterUpdate event procedure requery the form with:

    Me.Requery

    The parent form would be linked to the Responses subform as described earlier.

    0 comments
  4. Anonymous
    2025-01-13T21:55:15+00:00

    That makes sense. Thank you.

    0 comments