Filter continuous form by combo box

[revolution9540]

I cannot figure out how to filter a continuous form listing values existing in only one table. I want to use a Combo Box to accomplish this. I put custom values into the Combo Box as follows:

• Dell Laptops
• HP Minis
• Surfaces
• Projectors

However, I cannot generate the correct code to make this work. Here is what I have so far:

Private Sub Combo30_AfterUpdate()
If (Me.[Combo30].Value = "Dell Laptops") Then
    Me.Filter = "Brand_Type_Model_Number = '*Dell*'" & Chr(34) & Me.Combo30 & Chr(34)
    Me.FilterOn = True
End If


End Sub

Basically, I want to filter the form based on items that exist on the Brand/Type/Model_Number field that contain the word Dell, if the value in Combo30 is "Dell Laptops". I get this error when I try to select Dell Laptops from the Combo Box:


Could someone help me out?

[June7]

Use LIKE operator with wildcards.

Why do you include the combobox value in the concatenation? Will any data match on "*Dell*Dell Laptops"? This construct requires values to have 'Dell Laptops' in their string.

Me.Filter = "Brand_Type_Model_Number LIKE '*Dell*'"

[revolution9540]

Use LIKE operator with wildcards.

Why do you include the combobox value in the concatenation? Will any data match on "*Dell*Dell Laptops"? This construct requires values to have 'Dell Laptops' in their string.

Me.Filter = "Brand_Type_Model_Number LIKE '*Dell*'"

Although this got rid of that error message, I am now prompted to enter a parameter. I do not wish to do this, I would just like to filter:


Here is my code now:

[June7]

Appears Access is not finding Brand_Type_Model_Number field.

Post the form's RecordSource SQL statement.

[revolution9540]

Record source is simply "Loaner Equipment", which is the name of the table the continuous form is displaying. Brand_Type_Model_Number is a field within that table, so I don't see why Access can't find it.

I did, however, just make a query to try to troubleshoot this. Now the Record Source is:

SELECT [Loaner Equipment].[Brand/Type/Model Number] FROM [Loaner Equipment];

I get the exact same message as in post #3.

[June7]

The field is Brand/Type/Model Number not Brand_Type_Model_Number.

Me.Filter = "[Brand/Type/Model Number] LIKE '*Dell*'"

Advise no spaces and special characters/punctuation (underscore is exception) in naming convention. Also, no reserved words as names.

[revolution9540]

Now it works! The reason why I put the underscores is because that's the way the Access autofilled it as I was typing it in, so I assumed that it was an Access convention.

Even though I have both special characters and spaces in the name, it still works. Does this mean that I will run into problems in the future?

Thanks for your help!

[June7]

It means will have to remember to enclose the name in [] to define the name.

[revolution9540]

One last question, how do I modify the VBA so I can exclude entries that have the word "Projector" in them? I have a Dell Projector that comes when I filter Dell laptops. I'd like to put in the code a statement that excludes the entry if it contains "Projector" along with "Dell". I am brand new to Access and VBA, so I have a hard time finding this information on Google.

[June7]

Me.Filter = "[Brand/Type/Model Number] LIKE '*Dell*' AND NOT [Brand/Type/Model Number] LIKE "*Projector*"

That's the issue with pattern matching. What about other Dell products? Do you want to exclude them?

Having Brand/Type/Model all in one field is not good design. Discrete data should be in separate fields.

[revolution9540]

Thanks so much, June7!

This is just one of many tables on the database. This particular table is a smaller one for items that we loan out, most of which are laptops. But there is one projector.