How do I filter a list box in a form based on the value in a text box on a form?

Venturer58
Venturer58 used Ask the Experts™
on
Hello Experts,

I am a Army National Guard Officer trying to build an interim inventory management database solution for the Iraqi Army National Depot here in sunny Iraq. I have used the Northwind Traders DB as s starting point and have done a significant amount of customization. I am an SAP consultant by trade, and my Access skills are a bit rusty. I am using Access 2007.

On the "Orders" form, I turned the "ProductID" in the "Order Details Subform" field into a list box and have the following query pulling the values into it:

SELECT Inventory.[Product ID], Inventory.[Part Description], Inventory.[ANHAM ID], Inventory.[Part Number], Inventory.[Vehicle Class], Inventory.[Qty Available] FROM Inventory ORDER BY Inventory.[Part Description];

I have 2 text boxes in the Order Details form, TextBoxANHAMID and TextBoxPartNumber. I would like the list box to filter the results to what is in the TextBoxANHAMID or the TextBoxPartNumber text box. I've searched all of the forums, but couldn't find a solution.

I attached a screen shot of the form to help you visualize what i'm trying to do.

Thank you for your help in advance, you will be doing a great service to the Iraqi supply system. when we get this up and running, it will help the Iraqi Army the parts they so desperately need much faster.
Screen-Shot-of-Orders.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Im from Iraq,
what you said "I would like the list box to filter the results " 
I didnt see any listbox in your screenshoot.

Author

Commented:
The list box is Product ID

Author

Commented:
to clarify, it may not have been clear above: I want the list box to be filtered by what is listed in either of the two text boxes, so if I type an ANHAM ID or Part Number into either of the two text boxes, the list box only shows that specific item
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Attached is the form in Form View, when I type the number 6 in the ANHAM ID text box as shown in the screen shot, I only want it to show the item I highlighted (ANHAM ID = 6) in my list box.

Thanks in advance.
Screen-Shot-2.docx

Commented:
Put this code in the row source of the listbox, or create a query using this code and make it as the row source of the list box.

SELECT Inventory.[Product ID], Inventory.[Part Description], Inventory.[ANHAM ID], Inventory.[Part Number], Inventory.[Vehicle Class], Inventory.[Qty Available]
FROM Inventory
WHERE (((Inventory.[ANHAM ID])=IIf(IsNull([Forms]![Orders]![TextBoxANHAMID]),[anham id],[Forms]![Orders]![TextBoxANHAMID])) AND ((Inventory.[Part Number])=IIf(IsNull([Forms]![Orders]![TextBoxPartNumber]),[Part Number],[Forms]![Orders]![TextBoxPartNumber])))
ORDER BY Inventory.[Part Description];


Then put this VBA code in the after update of ANHAM ID text box and part number text box

[Order Details Subform].[Form]![Product ID].requery

Author

Commented:
Thanks for the post, I am trying to get it to work now...

Author

Commented:
Looks like the SELECT statement works great, but i'm having trouble with the requery code. The name of the subform that Product ID is in is: "Order Subform for Order Details"

I modified the statement above to reflect the name, and it's erroring out, it seems like it doesn't recognize the name...

Private Sub TextBoxANHAMID_AfterUpdate()
[Order Subform for Order Details].[Form]![Product ID].Requery
End Sub

Is the syntax above correct? I'm not very strong in Visual Basic.

Thanks again!

Commented:
use this:

Form_MasterFormName.SubformName!ProductID.requery

Replace MasterFormName and SubformName
Commented:
Form_Orders.Order_Subform_for_Order_Details![Product ID].Requery

Use  _ in place of space, that is why using space is not recommended for naming objects

Author

Commented:
GOT IT WORKING!!! I removed the masterform name from the syntax above and it performs beautifully. Thank you very much, I really appreciate it!

Author

Commented:
I really appreciate the help, THANKS AGAIN!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial