Using ApplyFilter in Form...help please

[playfuljade]

Hello. I am building a program in Access for manufacturing puposes. This program will be used for truck engines such as CAT, Mercedes...ect. The problem that I am having is this:

I want to use the ApplyFilter in a form (named Filter), to allow the end user to select the engine type to see all of the inventory of one engine type without having to scroll through thousands of lines. I have already set up the combo box filter (with "CAT";"60 Series";"MBE" for the engine types to choose from) in the form [MoveToPreShip] and I have a macro built for it as well. Here is what I have completed so far:

Macro Name - EngineType: On Change
Condition - [Forms]![MoveToPreShip]![Filter]="60 Series"
[Forms]![MoveToPreShip]![Filter]="CAT"
[Forms]![MoveToPreShip]![Filter]="MBE"
Action - ApplyFilter

I usually just use an ApplyFilter on a form to filter dates of shipments in programs but this one has me stumped. My unique number is the serial number for the engines and since each of the type of engines are manufactured from different suppliers, the serial numbers all start with different numbers or letters. (ex: for CAT engines, all engine serial numbers either start with "KCA*****";"KCB*****";"BXS*****";"MXS*****" and the 60 series engines all start with "7********"...ect.)
Could someone point me in the right direction on an expression to use? I would greatly appreciate it!

Let me know if I need to leave more information. Thanks!

[StepUP]

Try this code in the After Update event for the EngineType field:

DoCmd.ApplyFilter , "[EngineType ] Like '" & [Forms]![MoveToPreShip]![Filter]=" & "*'"

[playfuljade]

Thank you John! That did help...I just have to figure out how to get it working to where the end user will not have to type in the engine type in the message box...but thank you sooo much!

[StepUP]

What message box? There should be no reason to have to type in anything after the ApplyFilter Method. It should just show all the records that start with the matching letters of the engine type in your combo box.

BTW I just noticed that I said "After Update event for the EngineType field". It would actually be in the After Update event of whatever you called the combo box field (i.e. that should NOT be the actual engine type field from the record set).

[playfuljade]

Hello John. I am rather new to VBA code. My knowledge is a "hit" and "miss", depending on what I have learned from what I have researched and learned on my own. I am not sure how to list criteria in VBA or the Where expression in a macro yet. I am still struggling to find some answers, as there is no one that I can turn to for help here at work.
The filter box right now is applied to a macro that I have built for all engine types. I wanted to use the Where expression in the macro to filter the information...with the "like "KCA","KCB" statements as I do in queries, but unfortunately, I am not getting the results...lol. Could you help with me with this? I know that this is where my problem lies.

Thank you again!

[StepUP]

OK...did you actually write in the example code in thr After Update event of the combo box?

If you are not sure how to do it, go into the properties of the combo box, click on the "Event" tab, click the button to the far right side with the dots for the "After Update" event, and then chose "Code Builder" from the dialog box. Then type in the code that I gave earlier in the thread.

Hope that helps.

John

[playfuljade]

Hello John. Thank you for your response. I have put the code in the AfterUpdate. I'm just was unsure how to add in all of the different engine types and their prefixes since I now have 5 different manufacturers. I was just hoping that I could build them directly in the macro since I have a different engine type listed on different lines in the maco. can I build it in the "where" as an expression? Can this be done?

Thank you again for your time.

[StepUP]

The best way to handle that is to create a new table with your list of engines types and have the table be the row source for the combo box.

I find the best practice is to turn on the "limit to list" property for the combo box and then give the users a way to add to the list. Create a separate form based on the list table and perhaps on a double click of the combo box, open the form so that they can enter new values.

[playfuljade]

John, I forgot to thank you! Thanks!

Beth