combo boxes [code improvement]

G

Guest

This may sound a bit mouthful, but the desired outcome is simple. I'm not
code literate so could someone please help! Many thanks :)
The following codes sychronize two combo boxes, where the latter is limited
by the selection of the former. Right now records are filtered only after
both selections. But what I really want is:
1) record in form filtered after 1st combo box, and filtered futher after
2nd combo box.
2) have a OK button next to both combo boxes, so that filter doesn't take
place until the button is clicked.

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub
------------------
I also have another combo box in the same form which provides an alternative
mean of record finding. But I would like the previous 2-combo-box-set to be
cleared (display as blank) if this combo box is later used.

Private Sub cboDWGNo_AfterUpdate()
' Remove the filter.
Me.FilterOn = False
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[DWGNo] = '" & Me![cboDWGNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
M

Michel Walsh

Hi,


To define and use a filter, through code, you do something that may
looks like:


Me.Filter=" FieldName=" & numericalValue
Me.FilterOn=True

To deactivate a filter, use Me.FilterOn=False

To remove (erase) data in a combo box where a Not In List is "active",
try:

Me.ComboBoxName.Value = Null


Be careful in that case, to handle the NULL. As example,

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub
Click to expand...


should be



Private Sub cboCategory_AfterUpdate()

If IsNull(Me.cboCategory) Then

Me.cboType.RowSource = "SELECT Type FROM qryTypeCurrent ORDER BY
Type "

Else

Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End If

End Sub




Otherwise, if cboCategory, your actual code produces the SQL statement:


SELECT Type FROM qryTypeCurrent WHERE CategoryID =

which is an error ( CategoryID = to 'what' ). I assumed that if
Me.cboCategory IS NULL, then the WHERE clause has to be eliminated.




Hoping it may help,
Vanderghast, Access MVP



Sam Kuo said:
This may sound a bit mouthful, but the desired outcome is simple. I'm not
code literate so could someone please help! Many thanks :)
The following codes sychronize two combo boxes, where the latter is
limited
by the selection of the former. Right now records are filtered only after
both selections. But what I really want is:
1) record in form filtered after 1st combo box, and filtered futher after
2nd combo box.
2) have a OK button next to both combo boxes, so that filter doesn't take
place until the button is clicked.

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub
------------------
I also have another combo box in the same form which provides an
alternative
mean of record finding. But I would like the previous 2-combo-box-set to
be
cleared (display as blank) if this combo box is later used.

Private Sub cboDWGNo_AfterUpdate()
' Remove the filter.
Me.FilterOn = False
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[DWGNo] = '" & Me![cboDWGNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Click to expand...
 
G

Guest

Thanks for your hints, Michel
The "If" doesn't seem to have any effect on the performance of the
two-combobox-set, because there's no null value in cboCategory, cboType
remains blank unless a selection in cboCategory is made.

However, could you please explain what the "numbericalValue" is in the code
you suggest so that cboCategory filters record in main form based on its
selection.
Me.Filter=" FieldName=" & numericalValue
Click to expand...

The ControlSource of all fields in the main form is based on one query
called qryDrawings

The RowSource of cboCategory is from another query:
SELECT DISTINCTROW [CategoryID], [Category] FROM qryCategoryCurrent ORDER BY
[Category];



Michel Walsh said:
To define and use a filter, through code, you do something that may
looks like:

Me.Filter=" FieldName=" & numericalValue
Me.FilterOn=True

To deactivate a filter, use Me.FilterOn=False

To remove (erase) data in a combo box where a Not In List is "active",
try:

Me.ComboBoxName.Value = Null

Be careful in that case, to handle the NULL. As example,
Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub
Click to expand...

should be

Private Sub cboCategory_AfterUpdate()

If IsNull(Me.cboCategory) Then

Me.cboType.RowSource = "SELECT Type FROM qryTypeCurrent ORDER BY
Type "

Else

Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End If

End Sub

Otherwise, if cboCategory, your actual code produces the SQL statement:

SELECT Type FROM qryTypeCurrent WHERE CategoryID =

which is an error ( CategoryID = to 'what' ). I assumed that if
Me.cboCategory IS NULL, then the WHERE clause has to be eliminated.

Hoping it may help,
Vanderghast, Access MVP



Sam Kuo said:
This may sound a bit mouthful, but the desired outcome is simple. I'm not
code literate so could someone please help! Many thanks :)
The following codes sychronize two combo boxes, where the latter is
limited
by the selection of the former. Right now records are filtered only after
both selections. But what I really want is:
1) record in form filtered after 1st combo box, and filtered futher after
2nd combo box.
2) have a OK button next to both combo boxes, so that filter doesn't take
place until the button is clicked.

Private Sub cboCategory_AfterUpdate()
Me.cboType.RowSource = "SELECT Type FROM" & _
" qryTypeCurrent WHERE CategoryID = " & Me.cboCategory & _
" ORDER BY Type"
Me.cboType = Me.cboType.ItemData(0)
End Sub

Private Sub cboType_AfterUpdate()
DoCmd.ApplyFilter , "Type = '" & cboType & "'"
End Sub
------------------
I also have another combo box in the same form which provides an
alternative mean of record finding. But I would like the previous 2-combo-box-set to be cleared (display as blank) if this combo box is later used.
Private Sub cboDWGNo_AfterUpdate()
' Remove the filter.
Me.FilterOn = False
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[DWGNo] = '" & Me![cboDWGNo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Click to expand...
Click to expand...
 

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

Add a 3rd combo box 7
combo box doesnt filter record? 1
Filter - a quick question 2
Combo Box not refreshing after changing Row Source in code 0
Combo Box Filter 1
Link Combo box and form 5
2 Combo Box Filter for a Form 1
my combo boxes 2

Top