Search Form Help (1 Viewer)

[MG101]

Hi,

I currently have a database which contains various information for part returns. Among this information is contained a parts tracking number, VIN Number, and Date Code. I want to have a small dialogue box (Form) where a user can enter a tracking number, (or a date code or VIN if tracking number unavailable) to search for a record containing that tracking number. I have already created a replica of my new part entry form to view parts in read only mode only. Any suggestions on how to create small form to search for record based off criteria then open the replica form i have made to that record? Any help is much appreciated! (FYI This is the way I want to do it, I know some people may have suggestions on how to do it differently, but unless they are really easy and very similar I would like help for this method only)

Thanks!

 

[BrotherBook]

MG101-

It sounds like you'll want to create a new form with three text boxes for the user to enter filters and a ListBox based on a query which contains these three fields and any other information you want to show the user to find the right record.

1) Create the query containing the information you want the users to see when searching for records. You'll want it to contain the 3 fields they can filter on as well as the primary key from your return records as the first column.
2) Create three unbound text boxes to capture the user's filters
3) Create a ListBox with the RowSource set to your new query, again with the first column being the primary key from your return records. You can change the formatting on the ListBox to hide the unique key.
3) Set the criteria in your query for each of the fields you are filtering on. i.e. the Tracking Number field in your query would have the criteria

Like "*" & [Forms]![SearchRecords]![TrackingFilter] & "*"

The stars are wildcards so the query can handle when the user has not entered any criteria.

4) For each on your three filters create an OnChange event to requery the ListBox you created. This will filter the ListBox as the user types.

5) Write the code for the OnDoubleClick event of the ListBox using DoCmd.OpenForm, i.e.

DoCmd.OpenForm "The Name of your replica form", , , "ID =" & [Form]![The Name of Your ListBox]

Mike

 

[MG101]

Brother Book,

Thanks for the help, I tried what you suggested and it makes sense but I wasnt able to get it to work. Would you mind looking at my database and seeing what I did wrong?

Thanks!

 

[BrotherBook]

It looks like you didn't have the code to re-query implemented correctly. This is the implementation I had in mind, but not sure if its what you had in mind.

If you don't want to show all the data in the listbox, you could also use a DLookup function to check for the filters, and just have one button to launch the record if it could be found.

Alternatively if you do like the implementation you can add fields to the ListBox query to allow the user to see if its the correct record, i.e. part name, etc.

 

[MG101]

Thanks BrotherBook!

I do like the list because it shows you instantly if a record is available which meets your criteria, but at the same time I was thinking of just having a button that takes you directly to the record. Is it possible for either method to have the search window close after going to the record, then if they want to search another part I can make a button which will bring them back to the search dialouge?.

Thank you for all of your help, I appreciate it!

 

[MG101]

When I close out of the database and re-open it, It wont let me type any numbers in the boxes of the search form and I get a syntax error 3075 for the open form cmd line for the search box.I dont know how to fix it :/

 

[BrotherBook]

The attached should address your request for a button to launch and closing the search window after launching the record.

I'm not sure why you would be getting an error with the code. Let me know if you still have an issue. You'll need to create a button to return to the search screen.