Help building a Data Entry Form with multiple filters

This sounds similar to an order entry system with a main form based on the Order/Audit and a continuous subform to enter the Order Details/Observation. The response would be similar to quantity in the order entry. This is modeled well in the new Northwind templates.

Thank you. I'll check this out.

I would create an unbound form with 2 unbound comboboxes. The first combo would select an Audit Number. This would then filter the second combo to a list of observations for that audit. Then a button to open a form filtered to that Observation.

Without knowing the actual structure of your tables. the following is just a guess but should give you an idea to go on

frmSelObservation

cboSelAudit

RowSource: SELECT AuditNumber, AuditDescription FORM tblAuditNumber ORDER BY AuditDescription;

Column Count: 2

BoundColumn: 1

After Update event

dim strSQL as String

strSQL = "SELECT ObservationID, ObservationTitle FROM tblObservations WHERE AuditNumber = " & Me.cboSelAudit & ";"

Me.cboSelObservation = strSQL

Me.cboSelObservation.Requery

cmdOpenObservation

DoCmd.OpenForm "frmObservation",,,"ObservationID = " & Me.cboSelObservation

This sounds like a candidate for the use of correlated combo boxes, the first to list the audits, the second to list the classifications related to the selected audit number.  You'll firstly need a table to model the relationship type between classifications and audit numbers, ClassificationsAuditNumbers say.

The RowSource property of the classification combo box would be along these lines:

SELECT DISTINCT Classification.ClassificationID, Classification 

FROM ClassificationsAuditNumbers INNER JOIN Classification 

ON ClassificationsAuditNumbers.ClassificationID = Classification.ClassificationID

WHERE ClassificationsAuditNumbers.AuditNumber = Form!cboAuditNumber 

ORDER BY Classification;

The combo boxes would be unbound, and  could be placed in a form, in single form view, based on a query on the observations table, restricted by parameters which reference the unbound combo boxes.  In the AfterUpdate event procedure of the classification combo box the form would be requeried to return only the observation(s) on the audit/classification selected.  If you want to prevent users entering a new observation set the form's AllowAdditions property to False (No).  If you want to prevent them editing an existing observation set the Locked property of the observation text box control to True (Yes) and its Enabled property to False (No).

Within the form would be a subform based on the Response table, linked to parent form on ObservationID or similar as the LinkMasterFields and LinkChildFields properties of the subform control.  The subform would thus, by default, allow one or more responses to be made to the observation currently showing in the parent form.  If you want to limit the subform to the entry of only one response per observation, create a unique index in the Responses table on the ObservationID column.  This will make the relationship type one-to-one, so an error would be raised if a user attempts to enter a second response to an observation.

The form's RecordSource would be a query along these lines:

SELECT *

FROM Observations

WHERE AuditNumber = Forms!frmObservations!cboAuditNumber

AND ClassificationID = Forms!frmObservations!cboClassification

ORDER BY ObervationDate DESC;

With this example the observations per audit/classification would be returned in the form in descending date order, but the observations could be returned in whatever order you wish of course.

The AfterUpdate event procedure of the audit combo box would be:

    Me.cboClasification.Requery

    Me.cboClassification = Null

And that for the BeforeUpdate event procedure of the classification combo box would be:

   Const MESSAGE_TEXT = "An audit number must be selected first."

    If IsNull(Me.cboAuditNumber) Then

        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

        Cancel = True

    End If

The AfterUpdate event procedure of the classification combo box would be:

    Me.Requery

Thank you. I wanted to filter by audit and then by observations associated with that audit. Just wasn't sure how to do it. The unbound combo boxes with the afterupdate on the audit combo box event sounds like the way to go. I appreciate the suggestion on how to limit it to one response per observation as well. It should be one-to-one