Cascade 13 Combo Boxes...

[Adam]

Im new to Access, but heres what's going on:

I have a Spreadsheet with about 13 columns, each column a different
specification for offshore drilling rigs. So i have 13 different combo boxes.

I have set up a form with 13 combo boxes, each pertaining to a column in my
table. The first three combo boxes are Rig Name, Owner, and World Region. I
have it working thus far to where i can use the drop down menu of combo box
(CB) 1 and choose a Rig Name out of the drop down. I can then proceed to CB 2
and it takes CB 1 info and narrows down the list to the Owner of the Rig in
CB 1. I can proceed through all 13 CB as longas i go in order and it will
work fine.

What i need to do is have this set up to where i can go in a random order
through the 13 combo boxes. I have been doing all of this without using
visual basic, and would prefer to not have to learn that program, but i have
a feeling it would be easiest in the long run.

Thanks for the help.

 

[Klatuu]

You can do what you want, but not without some VBA.
The technique is different for this. You don't use cascading combos. You
use all the combos to construct a filtering string then you use that string
to set the form's filter.

 

[Adam]

Could you point me in the direction of some good help on the filtering you
talk of, ive searched some on the forums and havent come up with much help,
unless everything is just too foreign to me to realize its helpful. Thanks!

Adam

 

[Klatuu]

I can show you some examples from one of my applications. This is fairly
involved, so it may seem daunting, but it will be a good learning situation
for you.

First order of business is how to style the row source for the combo boxes.
You will want to inlcude "All" as one of the options in the combo so the user
will know that the field for that combo will not be included in the filter.
You do that with a Union query that include the text to present and if the
combo is a multi column combo where the actual value is not what the user
sees. In this example, the combo's row source is from a table of status
codes. The first column is the primary key field of the status code table
and the second column is description of the code:

SELECT 0 As ID, "(All)" As Dummy FROM dbo_InitStatus UNION SELECT
dbo_InitStatus.InitStatID, dbo_InitStatus.InitStatDescr FROM dbo_InitStatus;

The InitStatID is an Autonumber (Long Integer) primary key field and
intiStatusDescr is the plain text that describes it. The recordset being
filtered carries the value of initStatID as a foreign key. Since Autnumber
fields start with 1, I use 0 as the value and the word (All) for the user to
see.

In this example, there is only one field:

SELECT "(All)" As Dummy FROM dbo_Initiative UNION SELECT DISTINCT
InitPriority FROM dbo_Initiative WHERE InitPriority IS NOT NULL;

All the combo boxes are unbound.

When a user selects a value from the list, The After Update event of the
combo fires. I have this function in the After Update event text box of the
properties dialog for all the combos:

=SetInitFilters()

This function provides the filterin for the records in the form:

Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard"
GoTo SetInitFilters_Exit

End Function

This is the code for the AddAnd function that puts the word And in the
filter string ass needed:

Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmDashBoard"
GoTo AddAnd_Exit

End Function

Then I have a command button on the form to clear all the filters:

Private Sub cmdClearFilters_Click()
With Me
.cboPriority = "(All)"
.cboOrigQtr = "(All)"
.cboCurrQtr = "(All)"
.cboInitStatus = 0
.cboInitType = 0
.cboCenter = DLookup("[DefaultCenter]", "tblClientVersion")
.Recalc
.subInitiative.Form.FilterOn = False
.subInitiative.Form.Requery
End With

End Sub

I also call the clear filters sub from the form load event so they are
properly initiaized.

Feel free to post back with questions.

 

[Tony Toews [MVP]]

This is entirely a matter of style but I leave the combo boxes empty.
Mostly because I figure it's easier to see if something is being
filtered if all the other boxes are empty.

I do things slightly differently

If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority

Whereas I use
strFilter = " AND [InitPriority] = " & .cboPriority
.....

And the following Mid removes the " AND "
.subInitiative.Form.Filter = mid(strFilter, 6)

It's a bit of a trick but because I always comment it I figure that's
fine.

.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery

I haven't needed the requery.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Klatuu]

The requery doesn't need to be there. I guess I left it in during some
testing.
You are correct. It is a matter of style. My users prefer explicit prompts
leaving the combo empty seems to bother them.
--
Dave Hargis, Microsoft Access MVP

This is entirely a matter of style but I leave the combo boxes empty.
Mostly because I figure it's easier to see if something is being
filtered if all the other boxes are empty.

I do things slightly differently

If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority

Whereas I use
strFilter = " AND [InitPriority] = " & .cboPriority
.....

And the following Mid removes the " AND "
.subInitiative.Form.Filter = mid(strFilter, 6)

It's a bit of a trick but because I always comment it I figure that's
fine.

.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery

I haven't needed the requery.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/