How to create a Lookup form/subform?

[Boham]

Tables = "Purchase Orders", "Purchase Order Details", "Customer Orders", "Customer Order Details"

Forms = "Process Purchase Orders"

Everything is working fine under current requirements however; I am having trouble making the latest requirement work properly... I have the following scenario:

While processing purchase orders, I have a need to possibly update an existing customer order with the unitcost & processingfee. These items are not known until the vendor invoices me...

What I have attempted is to add a button to my process purchase orders form which will display a lookup form inwhich I can select the customer and enter the orderdate then display all orders by this customer within the last 60 days of the order date. I then need to update the details of the order within the form thus updating the table...

So far I can only get the button to open my form displaying ALL records in my "customer orders" table

HELP!!!!

 

[NSNewey]

Assuming you are using a listbox on the lookup form to display the orders, just build an SQL string in code and assign it to the rowSource property of the listbox.

For example if you have a listbox to display the orders and a combobox to select a customer...
To filter the list on the customer selected in the combo box, in the After Update event of the customer combo box enter some code similar to this...

Dim strSQL As String
[COLOR=green]'build the SQL string[/color]
strSQL = "SELECT [field list] FROM tCustOrders WHERE CustomerID = " & Me.ComboCustomer
[COLOR=green]'assign the SQL string to the list's RowSource property[/color]
Me.lstCustOrders.RowSource = strSQL
[COLOR=green]'clear any previous listbox selection[/color]
Me.lstCustOrders = Null

You can build the string with whatever parameters you want the rowsource query to return. I have only used the customerID from the combo box but you could add a date range or anything else to the string.

 

[Boham]

NSNewey, Thank you for your response...

I'm not using a list box, I am using a subform because there are multiple line items and I need to be able to update the field.

I have attempted to attach the forms, they should be viewable at

http://www.box.net/shared/11y52yzwo0

&

http://www.box.net/shared/wwyg2dq0wg

The workflow is... That the data entry person will be entering invoices in the PurchaseOrders Form, occassionally they will come across an invoice that also includes details of a CustomerOrder which the vendor fulfilled for us. At that time, the data entry person needs the ability to click the "UpdateCustomerOrder" button, search for orders placed by a customer in the last 60 days and to be able to update the Unit Cost and/or Processing Fee in that order.

I am still attempting to implement what you gave me above but am struggling still...