Form filter combo box problems

G

Guest

Hi all,

I'm trying to filter records in a form based on a selection in a combo box.
In the after_update event of the combo box, I have:

________________

Private Sub cmbFilterFileType_AfterUpdate()

On Error GoTo cmbFilterFileType_AfterUpdate_Error

If Forms!Issues!cmbFilterFileType = "All" Then
DoCmd.ShowAllRecords
End If

If Forms!Issues!cmbFilterFileType = "Case" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 2"
End If

Exit_cmbFilterFileType_AfterUpdate:
Exit Sub

cmbFilterFileType_AfterUpdate_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)
__________________

If I choose "All", it will requery and pull all the records....fine.
However, if I choose either of the other options, I get an access popup
window that says "Enter Parameter Value" with the following prompt text:

Open Issues.cmbRequestingParty

"Open Issues" is the query that the form is bound to, and there IS a control
on the form named 'cmbRequestingParty', but I have no idea why it's asking me
for a value for that. All I'm trying to do is filter out the records based
on a value with a different name that displays in a different control.

I've looked at the query and I don't see anything wrong with it. I've been
using it for months now and it's never had any problems and all I did was add
a small table to populate the new cmbFileType control but I've looked at the
query in both sql and design mode and I don't see any reason for it to ask me
for this value.

Any ideas?

Thanks,
CW
 
G

Guest

Nevermind. For some unknown reason access thought it would be funny to toss
in some random 'order by' code into the form.

thanks,
CW
 
M

Marshall Barton

Cheese_whiz said:
I'm trying to filter records in a form based on a selection in a combo box.
In the after_update event of the combo box, I have:

Private Sub cmbFilterFileType_AfterUpdate()

On Error GoTo cmbFilterFileType_AfterUpdate_Error

If Forms!Issues!cmbFilterFileType = "All" Then
DoCmd.ShowAllRecords
End If

If Forms!Issues!cmbFilterFileType = "Case" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 2"
End If

Exit_cmbFilterFileType_AfterUpdate:
Exit Sub

cmbFilterFileType_AfterUpdate_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)
__________________

If I choose "All", it will requery and pull all the records....fine.
However, if I choose either of the other options, I get an access popup
window that says "Enter Parameter Value" with the following prompt text:

Open Issues.cmbRequestingParty

"Open Issues" is the query that the form is bound to, and there IS a control
on the form named 'cmbRequestingParty', but I have no idea why it's asking me
for a value for that. All I'm trying to do is filter out the records based
on a value with a different name that displays in a different control.

I've looked at the query and I don't see anything wrong with it. I've been
using it for months now and it's never had any problems and all I did was add
a small table to populate the new cmbFileType control but I've looked at the
query in both sql and design mode and I don't see any reason for it to ask me
for this value.
Click to expand...


on't use those methods, they just do what clicking on the
corresponding menu item does. You would still have to
simulate the manual actions that you would do before using
the menu item.

Even though there are some issues with the Filter property,
it should be ok in your situation:

If Forms!Issues!cmbFilterFileType = "All" Then
Me.FilterOn = False
Else
If Forms!Issues!cmbFilterFileType = "Case" Then
Me.Filter = "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
Me.Filter = "[FileTypeID] = 2"
End If
Me.FilterOn = True
End If

Including data values in your code is a very poor practice.
Instead you should include the filetype number in the table
of file types so your code would be unaware of what it's
searching for.
All 0
Case 1
Issue 2

With the filetype nymber in the combo box's row source query
(ColumnCount of 2, BoundColumn 2 and ColumnWidths ;0), your
code could just be:

Me.Filter = "[FileTypeID] = " & Me!cmbFilterFileType
Me.FilterOn = Me!cmbFilterFileType > 0
 
G

Guest

Thanks for the reply, Marshall.

I'll keep your points in mind. In this case, I was just using a value list
to populate the filter combo box. I think it's the only time I've ever used
one, but I didn't see the point in making a table just for three choices when
I didn't want to store the data and there's never going to be any more
choices added.

I don't altogether follow the point about having to 'simulate' the actions.
I do understand why it's better to setup the code that way though, and I
appreciate the head's up.

CW

Marshall Barton said:
Cheese_whiz said:
I'm trying to filter records in a form based on a selection in a combo box.
In the after_update event of the combo box, I have:

Private Sub cmbFilterFileType_AfterUpdate()

On Error GoTo cmbFilterFileType_AfterUpdate_Error

If Forms!Issues!cmbFilterFileType = "All" Then
DoCmd.ShowAllRecords
End If

If Forms!Issues!cmbFilterFileType = "Case" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 2"
End If

Exit_cmbFilterFileType_AfterUpdate:
Exit Sub

cmbFilterFileType_AfterUpdate_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)
__________________

If I choose "All", it will requery and pull all the records....fine.
However, if I choose either of the other options, I get an access popup
window that says "Enter Parameter Value" with the following prompt text:

Open Issues.cmbRequestingParty

"Open Issues" is the query that the form is bound to, and there IS a control
on the form named 'cmbRequestingParty', but I have no idea why it's asking me
for a value for that. All I'm trying to do is filter out the records based
on a value with a different name that displays in a different control.

I've looked at the query and I don't see anything wrong with it. I've been
using it for months now and it's never had any problems and all I did was add
a small table to populate the new cmbFileType control but I've looked at the
query in both sql and design mode and I don't see any reason for it to ask me
for this value.
Click to expand...


on't use those methods, they just do what clicking on the
corresponding menu item does. You would still have to
simulate the manual actions that you would do before using
the menu item.

Even though there are some issues with the Filter property,
it should be ok in your situation:

If Forms!Issues!cmbFilterFileType = "All" Then
Me.FilterOn = False
Else
If Forms!Issues!cmbFilterFileType = "Case" Then
Me.Filter = "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
Me.Filter = "[FileTypeID] = 2"
End If
Me.FilterOn = True
End If

Including data values in your code is a very poor practice.
Instead you should include the filetype number in the table
of file types so your code would be unaware of what it's
searching for.
All 0
Case 1
Issue 2

With the filetype nymber in the combo box's row source query
(ColumnCount of 2, BoundColumn 2 and ColumnWidths ;0), your
code could just be:

Me.Filter = "[FileTypeID] = " & Me!cmbFilterFileType
Me.FilterOn = Me!cmbFilterFileType > 0
Click to expand...
 
M

Marshall Barton

If you only have 3 choices and you'll almost never be asked
to change them, then a value list may be appropriate. You
can still do what I suggested by using a value list like:
"All",0,"Case",1,"Issue",2
Don't forget to adjust the other properties.
--
Marsh
MVP [MS Access]


Cheese_whiz said:
I'll keep your points in mind. In this case, I was just using a value list
to populate the filter combo box. I think it's the only time I've ever used
one, but I didn't see the point in making a table just for three choices when
I didn't want to store the data and there's never going to be any more
choices added.

I don't altogether follow the point about having to 'simulate' the actions.
I do understand why it's better to setup the code that way though, and I
appreciate the head's up.


Marshall Barton said:
Cheese_whiz said:
I'm trying to filter records in a form based on a selection in a combo box.
In the after_update event of the combo box, I have:

Private Sub cmbFilterFileType_AfterUpdate()

On Error GoTo cmbFilterFileType_AfterUpdate_Error

If Forms!Issues!cmbFilterFileType = "All" Then
DoCmd.ShowAllRecords
End If

If Forms!Issues!cmbFilterFileType = "Case" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
DoCmd.ApplyFilter "", "[FileTypeID] = 2"
End If

Exit_cmbFilterFileType_AfterUpdate:
Exit Sub

cmbFilterFileType_AfterUpdate_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)
__________________

If I choose "All", it will requery and pull all the records....fine.
However, if I choose either of the other options, I get an access popup
window that says "Enter Parameter Value" with the following prompt text:

Open Issues.cmbRequestingParty

"Open Issues" is the query that the form is bound to, and there IS a control
on the form named 'cmbRequestingParty', but I have no idea why it's asking me
for a value for that. All I'm trying to do is filter out the records based
on a value with a different name that displays in a different control.

I've looked at the query and I don't see anything wrong with it. I've been
using it for months now and it's never had any problems and all I did was add
a small table to populate the new cmbFileType control but I've looked at the
query in both sql and design mode and I don't see any reason for it to ask me
for this value.
Click to expand...


on't use those methods, they just do what clicking on the
corresponding menu item does. You would still have to
simulate the manual actions that you would do before using
the menu item.

Even though there are some issues with the Filter property,
it should be ok in your situation:

If Forms!Issues!cmbFilterFileType = "All" Then
Me.FilterOn = False
Else
If Forms!Issues!cmbFilterFileType = "Case" Then
Me.Filter = "[FileTypeID] = 1"
End If

If Forms!Issues!cmbFilterFileType = "Issue" Then
Me.Filter = "[FileTypeID] = 2"
End If
Me.FilterOn = True
End If

Including data values in your code is a very poor practice.
Instead you should include the filetype number in the table
of file types so your code would be unaware of what it's
searching for.
All 0
Case 1
Issue 2

With the filetype nymber in the combo box's row source query
(ColumnCount of 2, BoundColumn 2 and ColumnWidths ;0), your
code could just be:

Me.Filter = "[FileTypeID] = " & Me!cmbFilterFileType
Me.FilterOn = Me!cmbFilterFileType > 0
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

Apply value of a combo box as a filter 3
Need combo box to refresh when moving to new record 2017 1
Access 2007 Combo Box Search 3
Filter Form and Unbound Combo Box 6
Filter unbound search combo box based on filter in form's OnOpenEvent 3
Filtering and Removing Filter based on a combo box 4
Access (XP) crashes on after update of combo box 2
Combo Box to Filter Form Records 2

Top