Form field dependent on cbo

[jamphan]

Is there a way to make a field on a form dependent on what the user selects from 2 other cbo? By that I mean I would want the user if they selected in cbo1 Easter and they select cbo2 Regional President in the form field I would want it to auto return the name John Smith because in my table he is the Eastern Regional President. I am not sure if I can do this by query or is there an easy way to do this?

 

[gromit]

Hi -

1. Yes, you can make a field dependent on one or more cbo. In general, you use some Visual Basic in the AfterUpdate event of the cbo. The code checks the value of the cbo and then determines the value to put into the other field.

2. How to implement this for your situation depends on the data structure you have in place. There needs to be some way to look up the title based on the two descriptors ("Eastern" and "Regional President"). I might step back and first look at how you are storing the information and what you want to do.

3. Do you really have a "grid" of titles, i.e. an eastern regional president, a western regional president, an eastern vice president, a western one, an eastern policy secretary, a western one, too? If so, then two variables (and two cbo's) can uniquely identify each title.

4. On the other hand, if you just have a general collection of titles, then a single cbo might be a better way to go. You might be able to tie this to a query and just do a simple look up.

Let me know if you still have questions.

- g

 

[jamphan]

I do have a grid of titles. In my table I have 3 fields. One for region, one for title and one for name. So on my form when the user selects the correct combination I would like the name to appear. What kind of code do I need to use to get this to happen?

 

[gromit]

Here is one approach, assuming that you cmbRegionID as the combo box for the region, cmbTitleID for the title and a text box OfficerName for the person's name.

' Set after update for Region combo
Private Sub cmbRegionID_AfterUpdate()
    UpdateTitle
End Sub

' Set after update for Title combo
Private Sub cmbTitleID_AfterUpdate()
    UpdateTitle
End Sub

Private Sub UpdateTitle()

    Dim varName As Variant ' declare a variable for the name

    ' Check if either combo box is null
    If (IsNull(Me.cmbRegionID) Or IsNull(Me.cmbTitleID)) Then
        varName = ""
    Else
        ' Look up the title
        varName = DLookup("[Name]", "tblOfficers", "[RegionID] = " & _
        Me.cmbRegionID & " AND [TitleID] = " & _
        Me.cmbTitleID)
    End If

    ' if the person is not found, then indicate it
    If (varName = "" Or IsNull(varName)) Then
        Me.OfficerName = "(None found)"
    ' Otherwise, put name into the text box
    Else
        Me.OfficerName = varName
    End If

End Sub

There is probably a little better performance to be gained by replacing the DLOOKUP function with a SQL query into the database, but it involves setting up a recordset.

hth,

-g