title: Returning multiple records using Recordset MS Access
body: <p>I have an MS Access form that has a combo box (which acts as a primary key) called <code>cboProjectID</code>. I am trying to create the form in a way so that once a selection is made in the <code>cboProjectID</code> combo box, it triggers another combo box called <code>cboErrCod1</code> to display error codes based on those available to the project_id selected in the <code>cboProjectID</code> combo box.</p>
  
  <p>Only certain Error Codes are available to certain Project ID's and those are defined in another table. However, i have created a query called <code>HDR_ERRCODES</code> that relates <code>Project_ID</code>, <code>Project_Code</code> and the possible <code>Error_Reason_Code</code> 's available to a specific <code>project_Id</code>.</p>
  
  <p>For example, a project code (which is selected from the cboProjectID combo box) could look something like these: "FI-01-05", "FI-01-01", "SY-02-02" etc). Once selected, the <code>Project_ID</code> is stored in the table <code>Project_DTA_REV_T</code> (this is the table that the form stores its information) and the <code>Project_ID</code> is simply a next up number (1,2,3,4 etc. etc.).    </p>
  
  <p>I understand that you need to use a Recordset to return multiple values when doing VBA code. This is the code I have started off with but it doesnt seem to be working in the "on change" command for the cboProjectID combo box field:</p>
  
  <pre><code>Private Sub cboProjectID_Change()
  
  Dim VarComboKey As Integer
  
  Dim dbs As DAO.Database
  Dim Err1 As DAO.Recordset
  
  VarComboKey = Me.cboProjectID.Value
  
  Set dbs = CurrentDb
  
  Set Err1 = dbs.OpenRecordset("SELECT DISTINCT [Error_Reason_Code] FROM [HDR_ErrCodes] WHERE [project_ID] = " &amp; VarComboKey)
  
  Do While Not Err1.EOF
     Me!cboErrCod1 = Me!cboErrCod1 &amp; Err1!Error_reason_code &amp; " "
     Err1.MoveNext
  Loop
  
  Err1.Close
  Set Err1 = Nothing
  
  End Sub
  </code></pre>
  
  <p>I am no VBA expert and have been trying to follow the coding methodology i have read on this website for Recordset's. Access is displaying every type of <code>Error_Reason_Code</code>, not the specific <code>Error_Reason_Code</code> 's pertaining to the project ID selected in the <code>cboProjectID</code> combo box.</p>
  
  <p>Can someone point me in the right direction in getting this code to display on the <code>Error_Reason_Code</code> 's in the cboErrCod1 combo box, only pertaining to the <code>Project_ID</code> selected in the <code>cboProjectID</code> combo box??</p>