Listing multiple records

[clickster]

I am building an order form that will pull up and display info from a table. I have ticket numbers that are unique. However, the customer names, products, and order dates are not unique. I would like to create a form that will allow me to view information by entering the order date, product, or company name and having all tickets (records)relating to that date, company, or product be listed. When I click on the appropriate record, I would like to open a form (I can design it on my own since it simply pulls the selected record) that displays the selected record. Can anyone give me the basic steps that I would go through to set this up? I apologize for asking such a complicated question.

 

[BoxHead]

I think this is along the lines of what you're looking for.

You can use unbound combo boxes on forms for filtering. Set a combobox on the form and set it's row source to

SELECT DISTINCT DateField FROM TableName

Use the name of your DateField and your table name. This will give you a combobox listing the unique dates in your table.

On the AfterUpdate event of the combobox,run

Me.Filter = "[DateField] =' " & ComboName.Value & "'"
Me.FilterOn = True

You can do the same thing with the ProductName and CompanyName fields. You should consider using a 'Reset' button to set the FilterOn property to False.

The difference between the method that I use and your original question is that I open the form with all records from the table and use the filter property to limit or expand my views. You seem to be looking for a popup form that would define the filter before the main form is open.

You can do that by having the Main Form's Load or Open events refer back to the Criteria form for its filter values. I haven't found this useful because it requires the user to close the main form and reselect criteria from the criteria form. You might want to look at the Activate event of the main form if your users will be toggling back and forth between the two forms. Run your filter code there and put a command button on each form to set focus to the other form.

I seem to recall that when I used the form's OnActivate event, I had to minimize the main form when I left it and maximize it when I returned to get the event to fire.

HTH
John

Use what you have,
Learn what you can,
Create what you need.