Filter form with command button

[NISMOJim]

I have an Inventory form that displays inventory in a datasheet type view, which has filtering textboxes at the top. The first filter criteria field (Cat) uses a combo box to select which category to filter the inventory by (Prototype;Production). Here is the filter code...

Private Sub UpdateFilter()

Dim Cat As String
Dim fltCat As String

Me.Filter = ""
Me.FilterOn = False
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([cbxSCat]) = False And [cbxSCat] <> "" Then
Cat = [cbxSCat]
fltCat = "Cat like '" & Cat & "*' "
If Me.Filter = "" Then
Me.Filter = fltCat
Else
Me.Filter = Me.Filter & "and " & fltCat
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If Me.Filter = "" Then
ClearFilter
Else
Me.FilterOn = True
End If
End Sub

This form works great.

On my Main User Menu form, I have a command button to check out Prototype parts, and another to check out Production parts. Here is what I have in the OnClick property of the menu's Prototype button...

Private Sub cmdProtoCO_Click()

Dim strDocName As String
strDocName = "frmInventory"

DoCmd.OpenForm strDocName

Forms!frmInventory.FilterOn = False
Forms!frmInventory!cbxSCat = "Prototype"
Forms!frmInventory.FilterOn = True

End Sub

When I click this button, it opens the Inventory form, and "Prototype" is displayed in the combobox, but the form isn't filtered. How can I activate the filter in the code so the Inventory form is showing only the Prototype parts when it opens?

Thanks for any & all help.

[Stingaway]

May not be the most elegant solution, but if you only have one type of filter (prototype) you could just create a secondary query that filters the prototypes and have a form for the each type... use the control button to open and close the appropriate forms..

Form 1 - Production, driven by a query that shows only production
Form 2 - Prototypes (same as production, just copy and rename, change the data source for the form to the filtered query)

Command button - on click, close form 1, open form 2. On form 2, the command button closes form 2, opens form 1.

Like I said, not elegant, but functional...

[NISMOJim]

I take it there isn't just a quick line of code I can enter in the Main Menu after

Forms!frmInventory!cbxSCat = "Prototype"

to run the UpdateFilter code on the Inventory form? I tried making the UpdateFilter code a Public Sub instead of a Private Sub, then entered "UpdateFilter" after "Forms!frmInventory!cbxSCat = "Prototype", but that didn't work. I don't understand enough about coding to know how to run code on one form from another form. There never is an easy answer in Access, is there?

[Stingaway]

I try to do as much as I can through the UI and resort to VBA only when absolutely and painfully necessary.

There are probably 37 different ways to do this - another one that comes to mind is to create a hidden text field on your form that contains the filter criteria of your query driving the form. Have the control button populate the form with 'prototype' when it is clicked and requery / refresh all.

Your query would have to check to see if the text field was populated on opening the form, and if it is not populated then select all. Your control button would have to also check to see if the text field was populated with prototype or something to indicate all records so you could switch back and forth.

Either way, there really shouldn't be a need for you to go into VBA at all.

[NISMOJim]

I'm not sure how that would work, since the filtering is done through VBA and not through the query. While I'm not that good at coding, I am trying to learn, and I feel that I am just one correct line away from getting this to work with VBA. I think something needs to replace the line

Forms!frmInventory.FilterOn = True

with something to run the Private Sub code. Other buttons on the inventory form are activated in VBA with just "UpdateFilter" to start that code going. I just need a way to get into one froms coding from another form.

Thanks for your help.

[NISMOJim]

I've made some progress, now I have another problem. I created a new module and made the sub Public (I didn't realize it couldn't be in a forms module to be accessed by a different form), also changing all of the Me. references to the form control paths. The code in the Inventory form just says UpdateFilter, refering to the new Public Sub. The Inventory form filter works perfect, as it did before. Now back to the command button on the main user menu. Here is the code for the button...

Private Sub cmdProtoCO_Click()
Dim strDocName As String
strDocName = "frmInventory"
DoCmd.OpenForm strDocName
Forms!frmInventory!cbxSeeCategory = "Prototype"
UpdateFilter
End Sub

This works a bit like it did before, opening the Inventory form, putting "Prototype" in the filter checkbox, but now I get an error message, where before I didn't...

Run-time error '3075':
Syntax error in string in query expression 'Qty like '*1'

I've looked all through the pages of code and don't see anything like this anywhere. When I hit Debug, the line (in red below) from the Public Sub UpdateFilter is hilighted in yellow. Here is the code for UpdateFilter (minus a few redundant filtering fields)...

Public Sub UpdateFilter()

Dim Category As String
Dim Qty As String
Dim fltCategory As String
Dim fltQty As String
Forms.frmInventory.FilterOn = False
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSCategory]) = False And _
[Forms]![frmInventory]![txtSCategory] <> "" Then
Category = [Forms]![frmInventory]![txtSCategory]
fltCategory = "Cat like '" & Category & "'"
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltCategory
Else
Forms.frmInventory.Filter = fltCategory
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If IsNull([Forms]![frmInventory]![txtSQty]) = False And _
[Forms]![frmInventory]![txtSQty] <> "" Then
Qty = [Forms]![frmInventory]![txtSQty]
fltQty = "Qty like '" & Qty & "'"
If Forms.frmInventory.Filter = "" Then
Forms.frmInventory.Filter = fltQty
Else
Forms.frmInventory.Filter = Forms.frmInventory.Filter & "and " & fltQty
End If
End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If Forms.frmInventory.Filter = "" Then
ClearFilter
Else
Forms.frmInventory.FilterOn = True
End If
End Sub

Does anybody know where the 'Qty like '*1' error might be coming from? I sure don't see it in my code.

Thank you for the help!

[NISMOJim]

Nevermind, I figured it out. I looked closer at the properties of the Category control and saw that it's source came from 2 columns in the table (one hidden). It was showing column 2, but the bound column was column 1. I changed the command button code to enter "1" instead of "Prototype" and all is now well with the world (at least the one going on on my computer screen).
Thank you for your help and suggestions, as always they are appreciated.