How To Use The Value Of A Hidden Combo Box Column

So I had a question come my way regarding some form operations. It will probably help someone else, so I am going to let you know how I did it.

Basically it had to do with setting 2 other form controls based on the first.

Here is a picture:

The table data is a simple 3 column table:

Here is the simple code I used on the “AfterUpdate” event of the combo box:

Private Sub cboAgencyID_AfterUpdate()
    Dim intAgency As Integer
    
    
    Me.txtProviders = DLookup("Provider", "tblAgencies", "AgencyID='" & Me.cboAgencyID & "'")
    
    'LOOKUP THE VALUE FOR THE OPTION FRAME GROUP
    Select Case Me.cboAgencyID.Column(1)
        Case "College"
            intAgency = 1
        Case "District"
            intAgency = 2
        Case "CTSO"
            intAgency = 3
        Case "CBO"
            intAgency = 4
        Case "Leadership"
            intAgency = 5
        Case "Public"
            intAgency = 6
        Case Else
            intAgency = 0
            
    End Select
    
    Me.fraAgencies.Value = intAgency
End Sub

I am setting the value of the Provider textbox by using the DLookup function. Reason? It was easy to use:

Me.txtProviders = DLookup("Provider", "tblAgencies", "AgencyID='" & Me.cboAgencyID & "'")

So based on whatever agency is selected, lookup in the “tblAgencies” table the value in the “Provider” column.

Then I am using the value in the “AgencyType” field to select the integer value of the AgencyType I want to select in my option frame.

That’s all. Let me know if you have any questions.


Comments are closed.