title: Using a query in Form On-Open event -- MS Access
body: <p>So I have a couple forms. In the parent form, I collect information about a User and include a UserID field. Additionally in the parent form, there are several buttons that a data-enterer can click dictating several different categories. </p>
  
  <p>When the data-enterer clicks on a category button, the engine opens a child UserCategory form. I've included logic in the On-Click event of the button (as well as the On-Open event of the child form) such that the UserID value is passed from the parent form record, and the CategoryID value is determined by the button clicked and is also passed to the child form. The combination of UserID and CategoryID is unique for the underlying UserCategory table in question and represents a key of the table, however, the primary key is another field -- UserCategoryID (which has a 1:1 relationship between the combination of UserID and CategoryID). </p>
  
  <p>A requirement, then, is that if the form receives a combination of UserID and CategoryID that is already in the table, then the form should reference that record. However, the form will go ahead and create a new record when its given values that are already represented. I've encountered this problem before when creating related forms, and the problem arose because the table wasn't respecting the 1:1 relationship between the FK and the PK of the related table. In all cases, I was able to fix the problem by enforcing a 1:1 relationship using the Relationships Database Tool. However, in this scenario, a user can belong to multiple categories and a category can have multiple users -- in other words, the UserID field is 1:M and the CategoryID field is 1:M, but their combo is 1:1. I don't know if there's a way to enforce this relationship.</p>
  
  <p>One potential solution I've thought of is to build a query in the On-Open event of a form. It will use the UserID and CategoryID to select the UserCategoryID from the table, which will then be fed into the field on the form. I've included my initial thoughts on the design of the query. Please excuse the syntax -- VBA beginner. </p>
  
  <p>Any help is appreciated</p>
  
  <p>EDIT -- Logic on the passer side:</p>
  
  <pre><code>Private Sub FLower_Click()
      Dim catType As Integer
      catType = 2
  
      DoCmd.OpenForm "frm_RespondentUseCat", OpenArgs:=2
  
  
  End Sub
  </code></pre>
  
  <p>Logic on the Passee side</p>
  
  <pre><code>Private Sub Form_Open(Cancel As Integer)
      Dim catType As Integer
      catType = Forms.frm_RespondentUseCat!OpenArgs
  
      Dim userID As Integer
      userID = Forms!Respondent.RespondentID.Value
  
      Dim strSQL As String
      strSQL = "SELECT * From RespondentUseCategories WHERE RespondentID = " &amp; userID &amp; " AND UseCategoryID = " &amp; catType &amp; ";"
  
      Dim results As DAO.Recordset
      Set results = CurrentDb.OpenRecordset(strSQL)
  
      If results.RecordCount &gt; 0 Then
          Set Forms("frm_RespondentUseCat").Recordset = results
  
      Else
          Me.UseCategoryID.Value = catType
          Me.RespondentID.Value = userID
  
      End If
  
  End Sub
  </code></pre>
  
  <p>Essentially the passer (i.e. the parent form with the button) will simply pass the category type reflecting what button was clicked. On the passee side, the form will execute some code on the event that it is opened. First it will collect the category type, then it will attempt to read the userID (RespondentID) value from the parent form (Respondent). Then it will use those two values to get the relevant record from the UserCategory table (RespondentUseCategories). If there is any data here, then we use that as the recordset for the form, and if not, we populate the userID and categoryID with the collected values and let the UserCateogryID autopopulated itself. This seems like the correct approach, however, when I tried the test-case where the record exists (e.g. an already encountered combo of User &amp; Category IDS), the child form remains unpopulated. I'm guessing theres a bug in the VBA. </p>