Combo Boxes

[djenkins728]

I'm new to this forum as well as Access 2003 and I have tried to read through some of the posts in relation to my question, but I need a more thorough understanding.

I have 4 combo boxes that I want to populate the values no matter which one the end user starts with-- cbo_Dept, cbo_Boat, cbo_Type, cbo_POC-total records 10,000. For the POC I used a query for my values instead of values from my main table because a POC can have more than one occurence of the other 3 values and I only wanted to list each name once. How can I point back to the main table for the other values to populate based on the selection from the query of POC and then vice versa if they don't start with the POC?

[NeoPa]

You have (accidentally) posted this question in the Access Articles section. This is NOT an article.
I'm moving this to the main Access questions forum.

MODERATOR.

[NeoPa]

I think you need to reread this to yourself and post it again so that it makes sense. You really can't talk about a whole bunch of items referred to simply by their initials and expect anyone to understand what you mean.

[djenkins728]

I'm not sure what you are referring to but I only listed the names of the combo boxes which are department, type, boat, and point of contact. And if this is your method of welcoming a Newbie, it's not welcoming.

[JKing]

Hi, have a look at this tutorial it explains how to link combo boxes / listboxes.

Cascading Combo / List Boxes

[djenkins728]

The tutorial helped out whole lot and I really appreciate it JKing, but I just have one question. When a name is chosen it brings up all occurences of the department information (ie if John Smith in dept L90 made 13 calls it lists L90 13x). How can I make the dept number only appear once? My code is listed below

Private Sub cbo_Calledby_AfterUpdate()
    With Me![cbo_deptselect]
        If IsNull(Me!cbo_Calledby) Then
            .RowSource = " "
        Else
            .RowSource = "Select [dept] " & _
                         "From tbl_trial " & _
                         "Where [calledby_id]=" & Me!cbo_Calledby
        End If
        Call .Requery
    End With
 
End Sub

[JKing]

Hey there, glad you found the tutorial useful. It's always more beneficial to power through something on your own with a little guidance. To get around this you can use the DISTINCT keyword. This should eliminate the duplicates.

Private Sub cbo_Calledby_AfterUpdate()
With Me![cbo_deptselect]
If IsNull(Me!cbo_Calledby) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [dept] " & _
"From tbl_trial " & _
"Where [calledby_id]=" & Me!cbo_Calledby
End If
Call .Requery
End With
 
End Sub
 

Give that a go and let me know how it turns out.

[djenkins728]

JKing you are awesome and again I appreciate your assistance. Now here is my dilemna of where I am stuck- how do I add the other two combo boxes boat and type to the code and have them perform the same and then upon a click oinformation to a form?

Then if a user decides not to start with the cbo_calledby, but instead with cbo_deptselect for instance will the code work in reverse?

[JKing]

This part I'm not too sure about. If you add code to each afterupdate event of all your combo boxes everytime you move a combo box it will refresh the others and it will just be a big circle. I'm not saying it can't be done in this fashion I just think it would be over complicated and there is probably a better way to handle this.

I can't think of anything great at the moment but maybe one of the other experts will have a good idea.

In the meantime perhaps you could elaborate a little more on the purpose of this form and what exactly it is you're trying to accomplish. The better we know the scenario the easier it is to come up with a solution that fits.

[djenkins728]

Okay I'll do my best to explain. Based on the selection criteria of the calledby, boat, type, and dept combo boxes, this determines which records from my tbl_maininfo match the criteria. After the user clicks a command button then my form frm_MainInfo opens and displays all data tied to the records.

[MMcCarthy]

Okay I'll do my best to explain. Based on the selection criteria of the calledby, boat, type, and dept combo boxes, this determines which records from my tbl_maininfo match the criteria. After the user clicks a command button then my form frm_MainInfo opens and displays all data tied to the records.

The only way I can think to do what you want is to create an option frame of a set of radio buttons to allow the user to first choose which option they wish to start with:
calledby, boat, type, or dept

Now you will need to set up the code to dynamically change the row source of each of the combo boxes depending on that choice. Each of the cascades will have to be coded using CASE statements depending on this choice.

The logic of this is very complicated. Are you sure you want to go down this route? If so, you want to carefully work out the logic of what you are trying to do first.

[djenkins728]

The only way I can think to do what you want is to create an option frame of a set of radio buttons to allow the user to first choose which option they wish to start with:
calledby, boat, type, or dept

Now you will need to set up the code to dynamically change the row source of each of the combo boxes depending on that choice. Each of the cascades will have to be coded using CASE statements depending on this choice.

The logic of this is very complicated. Are you sure you want to go down this route? If so, you want to carefully work out the logic of what you are trying to do first.

Okay, I will work on that this evening and get back with you on tomorrow. Thanks so much.

[NeoPa]

I'm not sure what you are referring to but I only listed the names of the combo boxes which are department, type, boat, and point of contact. And if this is your method of welcoming a Newbie, it's not welcoming.

This is not my "method of welcoming a newbie". It's my job of moderating badly posted questions.
If you are still not sure what I'm referring to try reading your original post again as I suggested. You'll find it makes very little sense (even the sentences aren't properly formed). Obviously POC could mean nothing to anyone but you until your subsequent post clarified that point at least, if not what the question was about.

[djenkins728]

I've gotten this far with my code, but when I click the submit button it asks for a parameter value instead of opening the frm_maininfofilter. I would rather continue with the If/Then logic instead of using Select/Case since I'm more comfortable with it. Can any one figure out what's wrong with my code?

Private Sub cmd_Submit_Click()
   On Error GoTo Err_cmd_Submit_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_maininfofilter"
    stLinkCriteria = ""
    If Not IsNull(Me.cbo_deptselect) Then
        stLinkCriteria = "Dept = " & Me.cbo_deptselect                                  
    End If
    If Not IsNull(Me.cbo_Calledby) Then
        If stLinkCriteria = "" Then
            stLinkCriteria = "[Called By] = " & Me.cbo_Calledby
        Else
            stLinkCriteria = stLinkCriteria & " And [Called By] = " & Me.cbo_Calledby
        End If
    End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmd_Submit_Click:
    Exit Sub
 
Err_cmd_Submit_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Submit_Click
End Sub

[JKing]

What are the data types of the fields Dept and [Called By]? Are they numbers or are they text?

[djenkins728]

What are the data types of the fields Dept and [Called By]? Are they numbers or are they text?

They are both text fields.

[JKing]

When using SQL strings with VBA variables of the text type need to be enclosed with single quotes.

Private Sub cmd_Submit_Click()
   On Error GoTo Err_cmd_Submit_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_maininfofilter"
    stLinkCriteria = ""
    If Not IsNull(Me.cbo_deptselect) Then
        stLinkCriteria = "Dept = '" & Me.cbo_deptselect  & "'"                               
    End If
    If Not IsNull(Me.cbo_Calledby) Then
        If stLinkCriteria = "" Then
            stLinkCriteria = "[Called By] = '" & Me.cbo_Calledby & "'"
        Else
            stLinkCriteria = stLinkCriteria & " And [Called By] = '" & Me.cbo_Calledby & "'"
        End If
    End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmd_Submit_Click:
    Exit Sub
 
Err_cmd_Submit_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Submit_Click
End Sub

Give that a go.

[djenkins728]

When using SQL strings with VBA variables of the text type need to be enclosed with single quotes.

Private Sub cmd_Submit_Click()
   On Error GoTo Err_cmd_Submit_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_maininfofilter"
    stLinkCriteria = ""
    If Not IsNull(Me.cbo_deptselect) Then
        stLinkCriteria = "Dept = '" & Me.cbo_deptselect  & "'"                               
    End If
    If Not IsNull(Me.cbo_Calledby) Then
        If stLinkCriteria = "" Then
            stLinkCriteria = "[Called By] = '" & Me.cbo_Calledby & "'"
        Else
            stLinkCriteria = stLinkCriteria & " And [Called By] = '" & Me.cbo_Calledby & "'"
        End If
    End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmd_Submit_Click:
    Exit Sub
 
Err_cmd_Submit_Click:
    MsgBox Err.Description
    Resume Exit_cmd_Submit_Click
End Sub

Give that a go.

Thanks JKing. I don't receive the parameter value box anymore, but when the form opens it's blank. Within this code where would I add the other two combo boxes, cbo_boat and cbo_type?

[djenkins728]

I'm still trying to add the two other combo boxes for the selection criteria and then open a form. The other two are cbo_boat and cbo_type. Since cbo_dept updates after the selection of cbo_calledby I added code to attempt to update cbo_boat, but nothing happens. Can someone help?

Private Sub cbo_Calledby_AfterUpdate()
    With Me![cbo_deptselect]
        If IsNull(Me!cbo_Calledby) Then
            .RowSource = " "
        Else
            .RowSource = "Select DISTINCT [dept] " & _
                         "From tbl_trial " & _
                         "Where [calledby_id]=" & Me!cbo_Calledby
        End If
 
    Call .Requery
    End With
 End Sub
Private Sub cbo_deptselect_AfterUpdate()
   With Me![cbo_boat]
        If IsNull(Me!cbo_deptselect) Then
            .RowSource = " "
        Else
            .RowSource = "Select DISTINCT [boat] " & _
                         "From tbl_trial " & _
                         "Where [calledby_id]= '" & Me!cbo_Calledby & "'"
        End If
    Call .Requery
    End With
End Sub