Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Cascading Combo Box 1

Status
Not open for further replies.
djenkins728

djenkins728

Programmer
Aug 2, 2007
7
US
Okay I'm new and frustrated and tired, so please I hope you can help. I already viewed Utterangel's db on cascading combo boxes and it helped a lot. What I don't understand is --on my form after the user selects the cbo_calledby, cbo_ship, and cbo_dept how can I get the cbo_hull to work (which is named call-intype). I've included the code I have so far. Again thanks and any help is appreciated.

Private Sub cbo_Calledby_AfterUpdate()
With Me![cbo_deptselect]
If IsNull(Me!cbo_Calledby) Then
.RowSource = " "

Else
.RowSource = "Select DISTINCT [dept] " & _
"From tbl_MainInfo " & _
"Where [calledby_id]=" & Me!cbo_Calledby
End If

Call .Requery
End With
End Sub
Private Sub cbo_deptselect_AfterUpdate()
With Me![cbo_shipselect]
If IsNull(Me!cbo_deptselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [ship] " & _
"From tbl_MainInfo " & _
"Where [dept] like '*" & Me!cbo_deptselect & "*'"
End If
Call .Requery
End With
End Sub
Private Sub cbo_Hull_AfterUpdate()
With Me![cbo_Calledby, cbo_deptselect, cbo_shipselect]
If IsNull(Me!cbo_shipselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [Call-InType] " & _
"From tbl_MainInfo " & _
"Where [ship]=" & Me!cbo_shipselect
End If
Call .Requery
End With
End Sub
Private Sub cbo_shipselect_AfterUpdate()
With Me![cbo_Hull]
If IsNull(Me!cbo_shipselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [Call-InType] " & _
"From tbl_MainInfo " & _
"Where [ship]=" & Me!cbo_shipselect
End If
Call .Requery
End With

End Sub

 
Sort by date Sort by votes
What exactly do you want to happen after the user selects hull? It appears you're resetting the rowsource for all of the first three boxes after you update hull. I thought you said your users already selected them. Also, the rowsource in the code for cbo_shipselect and cbo_hull are the same.



Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!
 
Upvote 0 Downvote
With Me![cbo_Calledby, cbo_deptselect, cbo_shipselect]

That's incorrect syntax, you can't reference an "array" of controls with Me. You need to reference each control by its name:

cbo_Calledby.RowSource = ""
cbo_deptselect.RowSource = ""
cbo_shipselect.RowSource = ""

etc.


 
Upvote 0 Downvote
Minor point but "Call" is a reserved word. May or may not cause a problem

Call Statement


Transfers control to a Sub procedure, Function procedure, or dynamic-link library (DLL) procedure.

Syntax

[Call] name [argumentlist]

The Call statement syntax has these parts:

Part Description
Call Optional; keyword. If specified, you must enclose argumentlist in parentheses. For example:
Call MyProc(0)
name Required. Name of the procedure to call.
argumentlist Optional. Comma-delimited list of variables, arrays, or expressions to pass to the procedure. Components of argumentlist may include the keywords ByVal or ByRef to describe how the arguments are treated by the called procedure. However, ByVal and ByRef can be used with Call only when calling a DLL procedure. On the Macintosh, ByVal and ByRef can be used with Call when making a call to a Macintosh code resource.
Click to expand...
 
Upvote 0 Downvote
Okay thanks so much VBAPrincess, JoeatWork and Majp. VBAPrincess to answer your question, I want the cbo_hull to display the procedures affiliated with whatever the previous combo box selections are. I was not sure as to how to write the correct syntax in order to do that so the duplication was me experimenting with different things. I do want my form (frm_MainInfoFilter) upon the click of a command button to open up with fields in relation to the combo boxes selection. So should I use a before update instead of an afterupdate for cbo_hull since this is the last selection by the user?
 
Upvote 0 Downvote
I have done some recoding (highlighted in red); I'm not sure if it's correct BUT when I go to select the cbo_hull the parameter value box pops up. What else should I do?

Private Sub cbo_Calledby_AfterUpdate()
With Me![cbo_deptselect]
If IsNull(Me!cbo_Calledby) Then
.RowSource = " "

Else
.RowSource = "Select DISTINCT [dept] " & _
"From tbl_MainInfo " & _
"Where [calledby_id]=" & Me!cbo_Calledby
End If

Call .Requery
End With
End Sub
Private Sub cbo_deptselect_AfterUpdate()
With Me![cbo_shipselect]
If IsNull(Me!cbo_deptselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [ship] " & _
"From tbl_MainInfo " & _
"Where [dept] like '*" & Me!cbo_deptselect & "*'"
End If
Call .Requery
End With
End Sub

Red Text

Private Sub cbo_Hull_AfterUpdate()
Dim strSQLSF As String
Me.RecordSource = strSQLSF

strSQLSF = "SELECT DISTINCT * FROM tbl_MainInfo "
strSQLSF = strSQLSF & " WHERE tbl_MainInfo.Calledby_id = '" & cbo_Calledby & "' And "
strSQLSF = strSQLSF & " tbl_MainInfo.dept = '" & cbo_deptselect & "' And "
strSQLSF = strSQLSF & " tbl_MainInfo.ship = '" & cbo_shipselect & "'"

End Sub

Private Sub cbo_shipselect_AfterUpdate()
With Me![cbo_Hull]
If IsNull(Me!cbo_shipselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [CallInType] " & _
"From tbl_MainInfo " & _
"Where [ship]=" & Me!cbo_shipselect
End If
Call .Requery
End With

End Sub
 
Upvote 0 Downvote
You are assigning strSQLSF to the form's RecordSource before strSQLSF has anything in it. Change the order:
Code: 
Private Sub cbo_Hull_AfterUpdate()
    Dim strSQLSF As String
    
    strSQLSF = "SELECT DISTINCT * FROM tbl_MainInfo "
    strSQLSF = strSQLSF & " WHERE tbl_MainInfo.Calledby_id = '" & cbo_Calledby & "' And "
    strSQLSF = strSQLSF & " tbl_MainInfo.dept = '" & cbo_deptselect & "' And "
    strSQLSF = strSQLSF & " tbl_MainInfo.ship = '" & cbo_shipselect & "'"
    
    Me.RecordSource = strSQLSF
End Sub

Also, if any of your parameter fields (Calledby_id, dept, ship) are numbers as opposed to text, you don't want the single quotes around them (if they are text, leave it as is).

 
Upvote 0 Downvote
Thanks JoeAtWork, but I'm still getting the parameter value box on this when it's time to select cbo_hull. And yes all the fields are 'text' value. Is there something in my code that's causing this?

Private Sub cbo_Calledby_AfterUpdate()
With Me![cbo_deptselect]
If IsNull(Me!cbo_Calledby) Then
.RowSource = " "

Else
.RowSource = "Select DISTINCT [dept] " & _
"From tbl_MainInfo " & _
"Where [calledby_id]=" & Me!cbo_Calledby
End If

Call .Requery
End With
End Sub
Private Sub cbo_deptselect_AfterUpdate()
With Me![cbo_shipselect]
If IsNull(Me!cbo_deptselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [ship] " & _
"From tbl_MainInfo " & _
"Where [dept] like '*" & Me!cbo_deptselect & "*'"
End If
Call .Requery
End With
End Sub
Private Sub cbo_Hull_AfterUpdate()
Dim strSQLSF As String

strSQLSF = "SELECT DISTINCT * FROM tbl_MainInfo "
strSQLSF = strSQLSF & " WHERE tbl_MainInfo.Calledby_id = '" & cbo_Calledby & "' And "
strSQLSF = strSQLSF & " tbl_MainInfo.dept = '" & cbo_deptselect & "' And "
strSQLSF = strSQLSF & " tbl_MainInfo.ship = '" & cbo_shipselect & "'"

Me.RecordSource = strSQLSF
End Sub



Private Sub cbo_shipselect_AfterUpdate()
With Me![cbo_Hull]
If IsNull(Me!cbo_shipselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [CallInType] " & _
"From tbl_MainInfo " & _
"Where [ship]=" & Me!cbo_shipselect
End If
Call .Requery
End With

End Sub
 
Upvote 0 Downvote
Which parameter is it asking for? Double-check that the parameter name matches the name of the control.

 
Upvote 0 Downvote
After I select the ship and go to the hull combo box, the parameter box pops up listing the name of the ship that I just selected along with a blank space to enter something else.
 
Upvote 0 Downvote
I've stopped the parameter box from displaying by adding the 'like' into the code, but now I don't have any data displaying for the cbo_hull. What did I do wrong?

Private Sub cbo_Calledby_AfterUpdate()
With Me![cbo_deptselect]
If IsNull(Me!cbo_Calledby) Then
.RowSource = " "

Else
.RowSource = "Select DISTINCT [dept] " & _
"From tbl_MainInfo " & _
"Where [calledby_id]=" & Me!cbo_Calledby
End If

Call .Requery
End With
End Sub
Private Sub cbo_deptselect_AfterUpdate()
With Me![cbo_shipselect]
If IsNull(Me!cbo_deptselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [ship] " & _
"From tbl_MainInfo " & _
"Where [dept] like '*" & Me!cbo_deptselect & "*'"
End If
Call .Requery
End With
End Sub
Private Sub cbo_Hull_AfterUpdate()
Dim strSQLSF As String

strSQLSF = "SELECT DISTINCT * FROM tbl_MainInfo "
strSQLSF = strSQLSF & " WHERE tbl_MainInfo.Calledby_id = '" & cbo_Calledby & "' And "
strSQLSF = strSQLSF & " tbl_MainInfo.dept = '" & cbo_deptselect & "' And "
strSQLSF = strSQLSF & " tbl_MainInfo.ship = '" & cbo_shipselect & "'"

Me.RecordSource = strSQLSF
End Sub
Private Sub cbo_shipselect_AfterUpdate()
With Me![cbo_Hull]
If IsNull(Me!cbo_shipselect) Then
.RowSource = " "
Else
.RowSource = "Select DISTINCT [CallInType] " & _
"From tbl_MainInfo " & _
"Where [ship] like '*" & Me!cbo_shipselect & "*'"
End If
Call .Requery
End With

End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JaineyBlue
  • Locked
  • Question
Cascading combo boxes in subform
Replies
11
Views
523
JaineyBlue
JaineyBlue
DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
4K
Norman Sherlock
Norman Sherlock
puforee
  • Locked
  • Question
DoCm. SendObject: Email To: Depends on Combo Box Selection but I can't get it to work
Replies
6
Views
181
puforee
puforee
Evecuz
  • Locked
  • Question
Wildcard "*" in combobox for searching
Replies
2
Views
141
Andrzejek
Andrzejek
ali32uk
  • Locked
  • Question
If between 2 dates then colour text box
Replies
4
Views
123
missinglinq
missinglinq

Part and Inventory Search

Sponsor

Back
Top