search shows original form (97/sr1)

[jenferkel]

How do I use an already created unbound search box, which is attached to a query, to pull up the original form that the record was created with, instead of showing it in datasheet view? I want to user to be able to perform the search using a search box, and have the original form/s appear, depending on what the user was searching on.

[WendellB]

I'm not sure I understand what you mean by an unbound search box. Is it a drop-down combo box that is unbound, and you have a rowsource for the combo box that gives you a unique key for each record? If that's what you are dealing with then you can use the combo box wizard and have it create code that will display the data record based on the choice you make in the combo box. Does that help?

[jenferkel]

Oops. Sorry for not being more specific. I created a form that acts as a search mechanism. There are currently 6 unbound text boxes on the form. I can use these boxes to enter in my search info. I then click the OK button, which is then bound to a search query. When the query completes, it shows it in a datasheet view. What I would like to do, is use this search form, enter in the search info, click OK and then have that Onclick event proc. go out and pull up the original form that was created that matches the search info.

[WendellB]

Ah - now I think I understand. I presume the criteria in your query looks something like
FieldXX = forms![MyForm]![MyComboBox1] and FieldYY = forms![MyForm]![MyComboBox2] AND ....
Or are you constructing SQL on the fly?
The latter let's you ignore combo boxes that are empty (no selection), so it's a bit more flexible.

________________________________
I then click the OK button, which is then bound to a search query.

You can't bind a button to a query, so I'm not sure you mean, but I suspect you are actually doing a Docmd.OpenQuery or something like that. If you want a form to open, you can bind your form to the search query and execute a DoCmd.OpenForm. Or you can create the SQL statement for your form RecordSource on the fly in the OnOpen event of the form. Yet another alternative is to create a filter statement in code based on the selections in the search form, and then apply the filter. Lots of ways to skin the cat - the more flexible ones involve writing some VBA and some SQL statements. Hope this helps.

[jenferkel]

I have an OnClick event when I click on the search button. It is a DoCmd.OpenQuery. When I click on the "Search" button, it runs the Search query and finds results depending on what I've entered into the unbound text boxes. When the query find's it's results, it shows the results in a datasheet view. After the user views the datasheet results and finds the item in need, what would be really cool is if the user could click on the desired item, using that same results datasheet and have it open up the form that was used to create that item. Or fill a new form with all the field information to make it appear like it was the original form. Any ideas on how to do this?

[jacksonmacd]

Wendell alluded to building SQL on the fly. If you are at all comfortable doing *that*, here's a scenario that would accomplish your goal:

- use your search form as you are doing currently
- OnClick search button -- build the SQL statement
- open the form (NOT the query) in datasheet mode, using the dynamic SQL statement as the "OpenArgs" of the OpenForm statement
- in the OnOpen event of the form, use the OpenArgs property of the form to dynamically set the recordsource of the form according to the SQL statement that you built previously.

This would display the records in a datasheet for your users to select the record of interest. Once they've done that, they switch the form into form view. Two methods I've used for doing that: 1) train them to right-click on the form's title bar and choose "Form view", or 2) create a custom menu bar with "Form View" and "Datasheet View" buttons.

Using this method presumes you are comfortable with building SQL on the fly.



Hope this helps.

[WendellB]

Sorry for not responding sooner, but it looks like Jack as pretty much hit the nail on the head. One question reamains in my mind however. Is it your intention to display more data in the form view than you are displaying in the query datasheet? If not, then why not just display the records in a form and let the user step through them till they find the desired record. That does mean that the user would have to be comfortable with using the navigation controls on a form. Just a thought.