(Solved) – MS Access, setting listbox based on row by row


I have a requirement in MS Access where a table is displayed as several rows in the form. I have created one form detail record(several fields) that will repeat for each row in the Table1. Lets say I have five columns in the Table1. Based on Column3 value, I would like to have a list of value for Column4 and Column5 during form_load. I have also created a separate Table2 to establish relationship between Column3, Column4 and Column5. I have set up Event procedure to populate the values using sub function. The challenge I have is, not being able to set up different listbox ‘value list’ for different rows. Any tips on populationg form fields IMRecomExIns and AmendReasonExIns by processing each row in Table1 would be a great help.

Private Sub IMRecomExIns_Click()

Dim CoverType As String
Dim ListRecomm As String
Dim ListAmend As String
Dim db As DAO.Database
Dim tablevar As Recordset

Set db = CurrentDb
Set tablevar = db.OpenRecordset("Table2")

CoverType = "*" & Me.CoverTypeExIns.Value & "*"

ListRecomm = ""
ListAmend = ""

If tablevar.EOF = False And tablevar.BOF = False Then
    Do Until tablevar.EOF
        If tablevar!CoverType Like CoverType Then
            ListRecomm = tablevar!Recommendation
            ListAmend = tablevar!AmendReason
        End If
End If

Me.IMRecomExIns.RowSourceType = "Value list"
Me.IMRecomExIns.RowSource = ListRecomm

Me.AmendReasonExIns.RowSourceType = "Value list"
Me.AmendReasonExIns.RowSource = ListAmend

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *