Using Option Groups Where First Option is No Filter

[DaneTrain24]

I created a search form as well as an option box. My options in the box are: All, Item Type, and Model. If the option All is selected, I essentially want it to act as if no filter was selected. While if Item or Model is selected, I want to search through their respective columns.


My current code is:


Private Sub Frame11_AfterUpdate()
Select Case Frame11
Case 1 ' All
Me.Filter = ""


Me.FilterOn = False
Case 2 ' Item Type
Me.Filter = "Item Type = 'Item Type'"
Me.FilterOn = True
Case 3 ' Model
Me.Filter = "Model = 'Item Type'"
Me.FilterOn = True

End Select


At my current stage, the option group does not work at all. When I select Item Type or Model, it doesn't search in their respective columns, it search throughout every column (which is only what I want to happen when All is selected)


Could anyone help on this?

[June7]

Your code searches for the literal string 'Item Type' in both cases 2 and 3.

You probably want to concatenate form control name to get its value as the search parameter.

Also, the [Item Type] field name has a space so delimit it with []. Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

Recommend you name controls like tbxItem and tbxModel.

Me.Filter = "[Item Type] = '" & Me.tbxItem & "'"

Me.Filter = "Model ='" & Me.tbxModel & "'"

[DaneTrain24]

Thanks for your help. I've changed the code to what is shown below and I've changed my field names to tbxItemType and tbxModel, yet there is still no response when I change my option button from "All" to either "Item Type" or "Model". The search simply filters through all of the columns. Am I making a mistake when linking the option buttons to the search function? Should I have something in the Event tab for each option button?

Private Sub Frame11_AfterUpdate()
Select Case Frame11
Case 1 ' All
Me.Filter = ""
Me.FilterOn = False
Case 2 ' Item Type
Me.Filter = "[Item Type] = " ' & Me.tbxItemType & " '"
Me.FilterOn = True
Case 3 ' Model
Me.Filter = "Model = " ' & Me.tbxModel & "'"
Me.FilterOn = True

End Select
End Sub

[June7]

You did not change field names - you changed textbox names.

Concatenation syntax is wrong. The first apostrophe is misplaced. Don't put a space in front of the second. See my example again.

[DaneTrain24]

Hmm, okay I've changed it to what you have and it still doesn't seem to work. I appreciate your help with this.

Also, when I attempt to debug, a Compile Error comes up, "Method or date member not found" and it highlights ".tbxItemType". I've changed my textbox to txtItemType so I'm not sure why it cannot find it.


Private Sub Frame11_AfterUpdate()
Select Case Frame11
Case 1 ' All
Me.Filter = ""
Me.FilterOn = False
Case 2 ' Item Type
Me.Filter = "[Item Type] = '" & Me.tbxItemType & "'"
Me.FilterOn = True
Case 3 ' Model
Me.Filter = "Model = '" & Me.tbxModel & "'"
Me.FilterOn = True

End Select
End Sub

[June7]

Is the name tbxItemType or txtItemType?

[DaneTrain24]

tbxItemType

[June7]

Okay, but you stated you changed to txtItemType.

I don't know why code fails. Would have to review db. If you want to provide, follow instructions at bottom of my post.

[DaneTrain24]

Attached is my database

Procurement Database Copy.accdb

[June7]

Code doesn't work because it is behind main form but filter needs to be applied to subform. Also, referencing data control in subform as filter parameter is unusual.

Me.[sub151 ColesObj].Form.Filter = ""

Me.[sub151 ColesObj].Form.Filter = "[Item Type]='" & Me.[sub151 ColesObj].Form.tbxItemType & "'"

Alternative is not to use form/subform, just a single form in Continuous view with controls arranged like datasheet and filter controls in form header.

You didn't provide the spreadsheet so I have no data to test with.



Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[DaneTrain24]

Spreadsheet is included in the tables section. It doesn't seem to work. You can test by entering "Washer" in the search bar while having "Item Type" selected in the option group. You can see that both dishwasher and washer appear, rather than just washer. Is this because the search is not looking for exact text?

[June7]

Tables section shows a link but you did not provide the Excel file so I have nothing for the link to work with.

Yes, LIKE operator and wildcard does partial match. But the code is not using the search bar input as filter parameter.

So the only filtering occurring is the static parameters in the form RecordSource.

Possibly the code should be:

Me.[sub151 ColesObj].Form.Filter = "[Item Type]='" & Me.txtKeyword & "'"

[DaneTrain24]

Hmm, doesn't seem to work. Here is the excel file you could use for testing.

Copy of 151 Coles St Procurement Sheet Test.zip

[DaneTrain24]

I've recreated the form as you suggested by using a single form with continuous view.

Can you explain the code I need to link the option group to only search by column? The form is pulling from a table with columns "Item Type" and "Model" as well as others. My three option buttons are All, Item Type, and Model.

[June7]

Maybe:

Private Sub btnSearch_Click()
Me.FilterOn = False
Select Case Frame11
    Case 1 ' All
        Me.Filter = ""
    Case 2 ' Item Type
        Me.Filter = "[Item Type] LIKE '*" & Me.txtKeyword & "*'"
    Case 3 ' Model
        Me.Filter = "Model LIKE '*" & Me.txtKeyword & "*'"
End Select
Me.FilterOn = True
End Sub

Remove the code behind Option Group.