By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,317 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,317 IT Pros & Developers. It's quick & easy.

Combo Boxes

P: 10
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?
Jul 30 '07 #1
Share this Question
Share on Google+
18 Replies


NeoPa
Expert Mod 15k+
P: 31,707
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.
Jul 30 '07 #2

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jul 30 '07 #3

P: 10
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.
Jul 31 '07 #4

JKing
Expert 100+
P: 1,206
Hi, have a look at this tutorial it explains how to link combo boxes / listboxes.

Cascading Combo / List Boxes
Jul 31 '07 #5

P: 10
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_Calledby_AfterUpdate()
  2.     With Me![cbo_deptselect]
  3.         If IsNull(Me!cbo_Calledby) Then
  4.             .RowSource = " "
  5.         Else
  6.             .RowSource = "Select [dept] " & _
  7.                          "From tbl_trial " & _
  8.                          "Where [calledby_id]=" & Me!cbo_Calledby
  9.         End If
  10.         Call .Requery
  11.     End With
  12.  
  13. End Sub
Jul 31 '07 #6

JKing
Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_Calledby_AfterUpdate()
  2. With Me![cbo_deptselect]
  3. If IsNull(Me!cbo_Calledby) Then
  4. .RowSource = " "
  5. Else
  6. .RowSource = "Select DISTINCT [dept] " & _
  7. "From tbl_trial " & _
  8. "Where [calledby_id]=" & Me!cbo_Calledby
  9. End If
  10. Call .Requery
  11. End With
  12.  
  13. End Sub
  14.  
Give that a go and let me know how it turns out.
Jul 31 '07 #7

P: 10
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?
Jul 31 '07 #8

JKing
Expert 100+
P: 1,206
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.
Jul 31 '07 #9

P: 10
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.
Jul 31 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jul 31 '07 #11

P: 10
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.
Jul 31 '07 #12

NeoPa
Expert Mod 15k+
P: 31,707
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.
Jul 31 '07 #13

P: 10
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_Submit_Click()
  2.    On Error GoTo Err_cmd_Submit_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_maininfofilter"
  8.     stLinkCriteria = ""
  9.     If Not IsNull(Me.cbo_deptselect) Then
  10.         stLinkCriteria = "Dept = " & Me.cbo_deptselect                                  
  11.     End If
  12.     If Not IsNull(Me.cbo_Calledby) Then
  13.         If stLinkCriteria = "" Then
  14.             stLinkCriteria = "[Called By] = " & Me.cbo_Calledby
  15.         Else
  16.             stLinkCriteria = stLinkCriteria & " And [Called By] = " & Me.cbo_Calledby
  17.         End If
  18.     End If
  19.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  20.  
  21. Exit_cmd_Submit_Click:
  22.     Exit Sub
  23.  
  24. Err_cmd_Submit_Click:
  25.     MsgBox Err.Description
  26.     Resume Exit_cmd_Submit_Click
  27. End Sub
Aug 1 '07 #14

JKing
Expert 100+
P: 1,206
What are the data types of the fields Dept and [Called By]? Are they numbers or are they text?
Aug 1 '07 #15

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

They are both text fields.
Aug 1 '07 #16

JKing
Expert 100+
P: 1,206
When using SQL strings with VBA variables of the text type need to be enclosed with single quotes.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_Submit_Click()
  2.    On Error GoTo Err_cmd_Submit_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_maininfofilter"
  8.     stLinkCriteria = ""
  9.     If Not IsNull(Me.cbo_deptselect) Then
  10.         stLinkCriteria = "Dept = '" & Me.cbo_deptselect  & "'"                               
  11.     End If
  12.     If Not IsNull(Me.cbo_Calledby) Then
  13.         If stLinkCriteria = "" Then
  14.             stLinkCriteria = "[Called By] = '" & Me.cbo_Calledby & "'"
  15.         Else
  16.             stLinkCriteria = stLinkCriteria & " And [Called By] = '" & Me.cbo_Calledby & "'"
  17.         End If
  18.     End If
  19.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  20.  
  21. Exit_cmd_Submit_Click:
  22.     Exit Sub
  23.  
  24. Err_cmd_Submit_Click:
  25.     MsgBox Err.Description
  26.     Resume Exit_cmd_Submit_Click
  27. End Sub
Give that a go.
Aug 1 '07 #17

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_Submit_Click()
  2.    On Error GoTo Err_cmd_Submit_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim stLinkCriteria As String
  6.  
  7.     stDocName = "frm_maininfofilter"
  8.     stLinkCriteria = ""
  9.     If Not IsNull(Me.cbo_deptselect) Then
  10.         stLinkCriteria = "Dept = '" & Me.cbo_deptselect  & "'"                               
  11.     End If
  12.     If Not IsNull(Me.cbo_Calledby) Then
  13.         If stLinkCriteria = "" Then
  14.             stLinkCriteria = "[Called By] = '" & Me.cbo_Calledby & "'"
  15.         Else
  16.             stLinkCriteria = stLinkCriteria & " And [Called By] = '" & Me.cbo_Calledby & "'"
  17.         End If
  18.     End If
  19.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  20.  
  21. Exit_cmd_Submit_Click:
  22.     Exit Sub
  23.  
  24. Err_cmd_Submit_Click:
  25.     MsgBox Err.Description
  26.     Resume Exit_cmd_Submit_Click
  27. 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?
Aug 1 '07 #18

P: 10
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_Calledby_AfterUpdate()
  2.     With Me![cbo_deptselect]
  3.         If IsNull(Me!cbo_Calledby) Then
  4.             .RowSource = " "
  5.         Else
  6.             .RowSource = "Select DISTINCT [dept] " & _
  7.                          "From tbl_trial " & _
  8.                          "Where [calledby_id]=" & Me!cbo_Calledby
  9.         End If
  10.  
  11.     Call .Requery
  12.     End With
  13.  End Sub
  14. Private Sub cbo_deptselect_AfterUpdate()
  15.    With Me![cbo_boat]
  16.         If IsNull(Me!cbo_deptselect) Then
  17.             .RowSource = " "
  18.         Else
  19.             .RowSource = "Select DISTINCT [boat] " & _
  20.                          "From tbl_trial " & _
  21.                          "Where [calledby_id]= '" & Me!cbo_Calledby & "'"
  22.         End If
  23.     Call .Requery
  24.     End With
  25. End Sub
Aug 1 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.