Returning multiple records using Recordset MS Access

I have an MS Access form that has a combo box (which acts as a primary key) called cboProjectID. I am trying to create the form in a way so that once a selection is made in the cboProjectID combo box, it triggers another combo box called cboErrCod1 to display error codes based on those available to the project_id selected in the cboProjectID combo box.

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 HDR_ERRCODES that relates Project_ID, Project_Code and the possible Error_Reason_Code 's available to a specific project_Id.

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 Project_ID is stored in the table Project_DTA_REV_T (this is the table that the form stores its information) and the Project_ID is simply a next up number (1,2,3,4 etc. etc.).

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:

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] = " & VarComboKey)

Do While Not Err1.EOF
   Me!cboErrCod1 = Me!cboErrCod1 & Err1!Error_reason_code & " "
   Err1.MoveNext
Loop

Err1.Close
Set Err1 = Nothing

End Sub

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 Error_Reason_Code, not the specific Error_Reason_Code 's pertaining to the project ID selected in the cboProjectID combo box.

Can someone point me in the right direction in getting this code to display on the Error_Reason_Code 's in the cboErrCod1 combo box, only pertaining to the Project_ID selected in the cboProjectID combo box??

Solution

To change the list of items displayed in a combo box you need to modify its Row Source. There are 3 options for the Row Source Type: Table/Query, Value List and Field List.

Value list will take a semi colon separated list of values and display them for selection. For example if you entered a Row Source of "Cat;Dog;Mouse" you would get the following when selecting your drop down.

cat

dog

mouse

Field List will display the field names of whatever query or table you select as the row source.

Table Query is what you want. This will populate the combo box with the values from the table or query you provide. What you are doing with your code now and the line Me!cboErrCod1 = Me!cboErrCod1 & Err1!Error_reason_code & " " is changing the currently displayed value in the Combo Box but not affecting the values that will be available when you expand it. To do this you have to change the Row Source of your combo box with the query that you have already created.

Me!cboErrCod1.RowSource = "SELECT DISTINCT [Error_Reason_Code] FROM [HDR_ErrCodes] WHERE [project_ID] = " & VarComboKey

So your method would change to be what is below since you do not need to modify a record set.

Private Sub cboProjectID_Change()
    Dim VarComboKey As Integer

    VarComboKey = Me.cboProjectID.Value

    Me!cboErrCod1.RowSource = "SELECT DISTINCT [Error_Reason_Code] FROM [HDR_ErrCodes] WHERE [project_ID] = " & VarComboKey
End Sub

EDIT: I have tried to duplicate your setup to see why the results would return nothing. Please correct this if I have made any errors in my assumptions.

I made a form with a combo called cboProjectID that has a source that gets all of the project IDs from the project table(Select * from Projects;) and displays the Project Code in the drop down. I made another one called cboErrCod1.

I made two tables one called Projects and the other called ErrorCodes with the following set ups based on your description.

Projects                          HDR_ErrCodes
ProjectID    ProjectCode(text)    Error_Reason_Code(text)    project_ID(integer)
1            FI-01-05             Failure                    1
2            FI-01-01             Testing                    1
3            SY-02-02             Manual                     1
4            SY-01-01             Failure                    2
5            SY-01-02

With the above set up and the on change event my combo box cboErrCod1 gets populated with the correct error codes. Is there something wrong with that setup?