Sequential Filtering on Data Entry Form

Question

I have a data entry form that is used to add "Review" records to a database. In my Relationships mapping, Reviews are linked with Submissions with a one-many relationship (i.e. one submission can have many reviews). Submissions are then linked to Applications with a one-many relationship (i.e. one application can have many submissions). FYI, I also have a SubmissionOrder Table with a preset list of numbers (1-8) linked to my Submissions table, because every application will have at least a first submission, and potential subesequent submissions. When adding a review record, I need to be able to first select the application number, and then the submission number that this new review record will be associated with. So far I can select the application number using a combo box, but I'm having trouble with selecting the submission number. If I use a combo box pulling records from my SubmissionsOrder table (i.e. 1-8), I get an error message saying I need an associated Submission record. If I change the combo box to pull records from the Submissions table, then I get a list of all submission records (ie. multiple 1s, 2s, etc. tied to different application numbers). What I'd like for this form to do is after I select the application number, the submissions combo box then is filtered to only show the submissions associated with that application. How do I do this?

Answer 1

To achieve the desired functionality of filtering the submissions based on the selected application number in your data entry form, you can use a combination of VBA code and the properties of the combo box controls in Microsoft Access.

Here’s a step-by-step approach:

1. Set Up Your Combo Boxes: Ensure you have two combo boxes on your form: one for selecting the application number and another for selecting the submission number.
   • Let's call the application combo box cboApplication and the submission combo box cboSubmission.
2. Populate the Application Combo Box: Make sure the cboApplication is populated with the application numbers from your Applications table.
3. Filter the Submission Combo Box: You need to add an event to the cboApplication combo box that triggers when the application number is selected. This can be done using the AfterUpdate event. Here’s an example of the VBA code you could use:

      Private Sub cboApplication_AfterUpdate()
          Dim strFilter As String
          strFilter = "ApplicationID = " & Me.cboApplication.Value
          Me.cboSubmission.RowSource = "SELECT SubmissionID, SubmissionOrder FROM Submissions WHERE " & strFilter
          Me.cboSubmission.Requery
      End Sub
   

   • In this code, replace ApplicationID with the actual field name that links the submissions to applications in your Submissions table.
   • The RowSource property of the cboSubmission combo box is set to a SQL query that filters submissions based on the selected application number.
4. Requery the Submission Combo Box: The Requery method refreshes the submission combo box to reflect the filtered results based on the selected application.
5. Test the Form: After implementing the above code, test your form by selecting an application number and checking if the submission combo box updates to show only the relevant submissions.

By following these steps, you should be able to filter the submissions based on the selected application number effectively.