Combo box help needed please

G

Guest

(Last post didn't seem success. My apology if double-post occurs)
The code below filters records only after BOTH cboState and cboCity have been
selected. But how can I amend it so the records can be first filtered right
after
cboState selection is made, then if the users prefer, they can filter the
records further using cboCity?

--- start of my code ---
Private Sub cboState_AfterUpdate()
' Filter the list of cboCity under selected State
Me.cboCity.RowSource = "SELECT City FROM" & _
" qryDomesticOffices WHERE State = """ & Me.cboState & """ " & _
" ORDER BY City"
Me.cboCity = Me.cboCity.ItemData(0)
End Sub

Private Sub cboCity_AfterUpdate()
' Filter records under selected State and City
DoCmd.ApplyFilter , "City = '" & cboCity & "'"
End Sub
--- end of my code ---
 
W

Wayne Morgan

See if these two modifications will do what you want. Currently, you're only
filtering on the city name. If the same city name exists in more than one
state, you'll get the city from all of the states. This will filter by state
after the state selected and filter by city and state after the city
selection.
--- start of my code ---
Private Sub cboState_AfterUpdate()
' Filter the list of cboCity under selected State
Me.cboCity.RowSource = "SELECT City FROM" & _
" qryDomesticOffices WHERE State = """ & Me.cboState & """ " & _
" ORDER BY City"
Me.cboCity = Me.cboCity.ItemData(0)
Click to expand...
DoCmd.ApplyFilter , "State = '" & cboState & "'"
End Sub

Private Sub cboCity_AfterUpdate()
' Filter records under selected State and City
Click to expand...
DoCmd.ApplyFilter , "State = '" & cboState & "' And City = '" & cboCity &
"'"
End Sub
--- end of my code ---
Click to expand...
 
G

Guest

Thanks, Wayne. It works beautifully for my example.
However, it doesn't work on another example that I have below, the form just
became blank after the 1st combo updates. This example is slightly different
from last example (which works well) because both combo's rowsources are of
different queries (namely qryCategory and qryType respectively) and the form
is based on another query qryMaster. Whereas the last example's combos have
the same rowsource as the form. Is this what causes the problem here?

--- start of my code ---
Private Sub cboCategory_AfterUpdate()
' Filter type list of cboType under selected category
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category
DoCmd.ApplyFilter , "Category = '" & cboCategory & "'"
Click to expand...
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Category = '" & cboCategory & "' And Type = '" & cboType > & "'"
Click to expand...
End Sub
--- end of my code ---
 
W

Wayne Morgan

What data type is each of these? As written, the values of cboCategory and
cboType should be text. When you set the Row Source for cboType, it appears
that the value in cboCategory may be numeric. If so, that would modify the
two filter statements as following:

DoCmd.ApplyFilter , "Category = " & cboCategory

DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = '" & cboType
& "'"

Remove the quotes around cboType as well if that value is numeric.

DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = " & cboType

Also, verify that there are no typos in the field names and that there are
records to be returned with the selections made.

--
Wayne Morgan
MS Access MVP


Sam Kuo said:
Thanks, Wayne. It works beautifully for my example.
However, it doesn't work on another example that I have below, the form
just
became blank after the 1st combo updates. This example is slightly
different
from last example (which works well) because both combo's rowsources are
of
different queries (namely qryCategory and qryType respectively) and the
form
is based on another query qryMaster. Whereas the last example's combos
have
the same rowsource as the form. Is this what causes the problem here?

--- start of my code ---
Private Sub cboCategory_AfterUpdate()
' Filter type list of cboType under selected category
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category
DoCmd.ApplyFilter , "Category = '" & cboCategory & "'"
Click to expand...
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Category = '" & cboCategory & "' And Type = '" &
cboType > & "'"
Click to expand...
End Sub
--- end of my code ---
Click to expand...
 
G

Guest

Happy New Year, Wayne.
The values of cboCategory is numeric and the values of cboType is text.

cboCategory property:-
RowSource: SELECT DISTINCTROW [CategoryID], [Category] FROM qryCategory;
ColumnWidth: 0cm;2.54cm

cboType property:-
RowSource: qryReidProductDrawings

So I've modified the cboCategory filter statement as you suggest, but it
returns a run-time error '2501' for the line > DoCmd.ApplyFilter , "Category
= " & cboCategory
What is wrong now? Thanks alot for you kind help

--- start of my code ---
Private Sub cboCategory_AfterUpdate()
' Clear cboDWGNo, cboVersionStatus, cboFormerDWGNo and cboJobNo (show as
blank)
Me.cboDWGNo = Null
Me.cboVersionStatus = Null
Me.cboFormerDWGNo = Null
Me.cboJobNo = Null
' Clear the list of cboVersionStatus
Me.cboVersionStatus.RowSource = qryReidProductDrawings
' Filter type list of cboType under selected category
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category (this line returns an error)
DoCmd.ApplyFilter , "Category = " & cboCategory
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = '" & cboType
& "'"
End Sub
--- end of my code ---
 
W

Wayne Morgan

Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category (this line returns an error)
DoCmd.ApplyFilter , "Category = " & cboCategory<<

It appears that cboCategory contains the value of the field CategoryID, not
the value of the field Category. Verify the field names used for the form.

--
Wayne Morgan
MS Access MVP


Sam Kuo said:
Happy New Year, Wayne.
The values of cboCategory is numeric and the values of cboType is text.

cboCategory property:-
RowSource: SELECT DISTINCTROW [CategoryID], [Category] FROM qryCategory;
ColumnWidth: 0cm;2.54cm

cboType property:-
RowSource: qryReidProductDrawings

So I've modified the cboCategory filter statement as you suggest, but it
returns a run-time error '2501' for the line > DoCmd.ApplyFilter ,
"Category
= " & cboCategory
What is wrong now? Thanks alot for you kind help

--- start of my code ---
Private Sub cboCategory_AfterUpdate()
' Clear cboDWGNo, cboVersionStatus, cboFormerDWGNo and cboJobNo (show as
blank)
Me.cboDWGNo = Null
Me.cboVersionStatus = Null
Me.cboFormerDWGNo = Null
Me.cboJobNo = Null
' Clear the list of cboVersionStatus
Me.cboVersionStatus.RowSource = qryReidProductDrawings
' Filter type list of cboType under selected category
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category (this line returns an error)
DoCmd.ApplyFilter , "Category = " & cboCategory
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = '" &
cboType
& "'"
End Sub
--- end of my code ---

Wayne Morgan said:
What data type is each of these? As written, the values of cboCategory
and
cboType should be text. When you set the Row Source for cboType, it
appears
that the value in cboCategory may be numeric. If so, that would modify
the
two filter statements as following:

DoCmd.ApplyFilter , "Category = " & cboCategory

DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = '" &
cboType
& "'"

Remove the quotes around cboType as well if that value is numeric.

DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = " &
cboType

Also, verify that there are no typos in the field names and that there
are
records to be returned with the selections made.
Click to expand...
Click to expand...
 
G

Guest

Thanks for pointing out the problem, Wayne :)

Regards,
Sam

Wayne Morgan said:
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category (this line returns an error)
DoCmd.ApplyFilter , "Category = " & cboCategory<<

It appears that cboCategory contains the value of the field CategoryID, not
the value of the field Category. Verify the field names used for the form.

--
Wayne Morgan
MS Access MVP


Sam Kuo said:
Happy New Year, Wayne.
The values of cboCategory is numeric and the values of cboType is text.

cboCategory property:-
RowSource: SELECT DISTINCTROW [CategoryID], [Category] FROM qryCategory;
ColumnWidth: 0cm;2.54cm

cboType property:-
RowSource: qryReidProductDrawings

So I've modified the cboCategory filter statement as you suggest, but it
returns a run-time error '2501' for the line > DoCmd.ApplyFilter ,
"Category
= " & cboCategory
What is wrong now? Thanks alot for you kind help

--- start of my code ---
Private Sub cboCategory_AfterUpdate()
' Clear cboDWGNo, cboVersionStatus, cboFormerDWGNo and cboJobNo (show as
blank)
Me.cboDWGNo = Null
Me.cboVersionStatus = Null
Me.cboFormerDWGNo = Null
Me.cboJobNo = Null
' Clear the list of cboVersionStatus
Me.cboVersionStatus.RowSource = qryReidProductDrawings
' Filter type list of cboType under selected category
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category (this line returns an error)
DoCmd.ApplyFilter , "Category = " & cboCategory
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = '" &
cboType
& "'"
End Sub
--- end of my code ---

Wayne Morgan said:
What data type is each of these? As written, the values of cboCategory
and
cboType should be text. When you set the Row Source for cboType, it
appears
that the value in cboCategory may be numeric. If so, that would modify
the
two filter statements as following:

DoCmd.ApplyFilter , "Category = " & cboCategory

DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = '" &
cboType
& "'"

Remove the quotes around cboType as well if that value is numeric.

DoCmd.ApplyFilter , "Category = " & cboCategory & " And Type = " &
cboType

Also, verify that there are no typos in the field names and that there
are
records to be returned with the selections made.
Click to expand...
--- start of my code ---
Private Sub cboCategory_AfterUpdate()
' Filter type list of cboType under selected category
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryType WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
' Filter records under selected category
DoCmd.ApplyFilter , "Category = '" & cboCategory & "'"
End Sub

Private Sub cboType_AfterUpdate()
' Filter records under selected category and type
DoCmd.ApplyFilter , "Category = '" & cboCategory & "' And Type = '" &
cboType > & "'"
End Sub
--- end of my code ---
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Could someone help me modify the example below to fit my situation?

I have two tables (tblSeries and tblDocTypes). The only thing they have in
common is a field "Series Code".

I also have a form on which I have two combo boxes. One for Record Series
and one for Document Type. These boxes are linked to the corresponding field
in their table ("Record Series" = "Series Name" from tblSeries and "Document
Type" = "Series Name" from tblDocTypes).

What I would like is when a selection is made from the combo box "Document
Type" it will list only that "Record Series" it belongs with. Here is some
data from the two tables:

tblDocTypes tblSeries
Series Code Series Name Series Code Series Name
ACC Assets ACC Accounting
ACC Banking DEV Development
ACC Capital COR Corporate
COR Stocks INS Insurance
COR Partnership HR Human Resources
DEV Associations
DEV Building

So, if "Assets" was chosen as a "Document Type" then "Accounting" would be
returned in the "Record Series" box.

Hope this makes sense. Thanks in advance for the help!

Ember

:

See if these two modifications will do what you want. Currently, you're only
filtering on the city name. If the same city name exists in more than one
state, you'll get the city from all of the states. This will filter by state
after the state selected and filter by city and state after the city
selection.

--- start of my code ---
Private Sub cboState_AfterUpdate()
' Filter the list of cboCity under selected State
Me.cboCity.RowSource = "SELECT City FROM" & _
" qryDomesticOffices WHERE State = """ & Me.cboState & """ " & _
" ORDER BY City"
Me.cboCity = Me.cboCity.ItemData(0)
DoCmd.ApplyFilter , "State = '" & cboState & "'"
End Sub
--- end of my code ---

Wayne Morgan
MS Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo box help 2
synchronize combo boxes 2
[Forms]![frmNameOfForm].[cboControlName] -- need help w/ exception 1
Question for John Vinson 5
Combo boxes and append query or what? 5
requery combo box on subform 3
Filter Command Button 2
Filter tabular form by combo box 0

Top