Query search transparant to user

[Bob B]

I have a query that will return multiple entries if a last name, first name
is entered.
I would like to have the user enter the data on a form and then receive a
list of data without seeing the query form and be able to return to the input
form when finished. How can I do this?

 

[Douglas J. Steele]

Not quite sure what you mean by "seeing the query form".

You can refer to a control on an open form by putting something like
Forms![Customer Info]![CustomerID] as the parameter, as opposed to just
putting something like [Customer Id]

 

[John Vinson]

I have a query that will return multiple entries if a last name, first name
is entered.
I would like to have the user enter the data on a form and then receive a
list of data without seeing the query form and be able to return to the input
form when finished. How can I do this?

The user doesn't need to (and many would say, should not!) see the query
design or query datasheet screens at all.

You can base a Form on a query referencing (the same or another) form for
its criteria, using =[Forms]![formname]![controlname] as a criterion. Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

 

[Bob B]

I don't understand what you mean by:
Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

I have a form called "ClientSearch" with one unbound field called "Text0".
In the criteria of the field I want to look up is:
[Forms]![ClientSearch]![Text0]
Do I need to put something in form or Field of the form?

I have a query that will return multiple entries if a last name, first name
is entered.
I would like to have the user enter the data on a form and then receive a
list of data without seeing the query form and be able to return to the input
form when finished. How can I do this?

The user doesn't need to (and many would say, should not!) see the query
design or query datasheet screens at all.

You can base a Form on a query referencing (the same or another) form for
its criteria, using =[Forms]![formname]![controlname] as a criterion. Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

 

[Bob B]

I don't know if you didn't get my message or you gave up on me. I'm volunteer
trying to help out a non profit organization and appreciate any help I can
get.

I don't understand what you mean by:
Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

I have a form called "ClientSearch" with one unbound field called "Text0".
In the criteria of the field I want to look up is:
[Forms]![ClientSearch]![Text0]
Do I need to put something in form or Field of the form?

I have a query that will return multiple entries if a last name, first name
is entered.
I would like to have the user enter the data on a form and then receive a
list of data without seeing the query form and be able to return to the input
form when finished. How can I do this?

The user doesn't need to (and many would say, should not!) see the query
design or query datasheet screens at all.

You can base a Form on a query referencing (the same or another) form for
its criteria, using =[Forms]![formname]![controlname] as a criterion. Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

 

[John Vinson]

I don't understand what you mean by:
Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

I have a form called "ClientSearch" with one unbound field called "Text0".
In the criteria of the field I want to look up is:
[Forms]![ClientSearch]![Text0]
Do I need to put something in form or Field of the form?

Sorry, Bob - I did reply (I thought) but it evidently got lost.

You can do this all with one Form. Base the form on this query - the
query will be referencing a textbox on the form itself (it's
convenient to put the textbox in the form's Header but this is not
essential). If you make the query criterion

=[Forms]![ClientSearch]![Text0] OR [Forms]![ClientSearch]![Text0] IS
NULL

you'll see all records by default.

So let's say that ClientSearch displays all of the information that
you want to see. Open the form in design view and display the
properteies of Text0 (by the way, you can and should rename the
control to something meaningful). Select its AfterUpdate event, click
the ... icon by it, and choose Code Builder. Edit the two lines Access
gives you to

Private Sub Text0_AfterUpdate()
Me.Requery
End Sub

This will repopulate the form with just the "hits" from the query.


John W. Vinson[MVP]

 

[Bob B]

This doesn't seem to work. This is what I have in my form:
Private Sub Text0_AfterUpdate()
Me.Requery
End Sub

When I execute the query by its self it display's data I want. How does the
form know what query to reference? The query's name is ClientSearch

I don't understand what you mean by:
Open
(or requery) the bound form in the afterupdate event of the control providing
the criterion.

I have a form called "ClientSearch" with one unbound field called "Text0".
In the criteria of the field I want to look up is:
[Forms]![ClientSearch]![Text0]
Do I need to put something in form or Field of the form?

Sorry, Bob - I did reply (I thought) but it evidently got lost.

You can do this all with one Form. Base the form on this query - the
query will be referencing a textbox on the form itself (it's
convenient to put the textbox in the form's Header but this is not
essential). If you make the query criterion

=[Forms]![ClientSearch]![Text0] OR [Forms]![ClientSearch]![Text0] IS
NULL

you'll see all records by default.

So let's say that ClientSearch displays all of the information that
you want to see. Open the form in design view and display the
properteies of Text0 (by the way, you can and should rename the
control to something meaningful). Select its AfterUpdate event, click
the ... icon by it, and choose Code Builder. Edit the two lines Access
gives you to

Private Sub Text0_AfterUpdate()
Me.Requery
End Sub

This will repopulate the form with just the "hits" from the query.


John W. Vinson[MVP]

 

[John Vinson]

This doesn't seem to work. This is what I have in my form:
Private Sub Text0_AfterUpdate()
Me.Requery
End Sub

When I execute the query by its self it display's data I want. How does the
form know what query to reference? The query's name is ClientSearch

The Form's Recordsource property *should be the name of the Query*.
That way you don't need to execute or "open" the query at all!

John W. Vinson[MVP]

 

[Bob B]

This does find the records, but does not display the results. I can tell by
the record count at the bottom of the form. After I enter the search
criteria I would like to display query resluts.

 

[John Vinson]

This does find the records, but does not display the results. I can tell by
the record count at the bottom of the form. After I enter the search
criteria I would like to display query resluts.

Sounds like the Form doesn't have controls bound to the fields in the
Query. Are there textboxes and such on the form? What are their
Control Source properties?

John W. Vinson[MVP]

 

[Bob B]

After I cerated fields in the form and bound them to the fields in the query
and put the search field in the header, and changed the display to continious
the records displayed the way I wanted. Still one problem the cursor goes to
the detail records and the results that are displayed are in a edit mode
where I want them read only.

 

[John Vinson]

After I cerated fields in the form and bound them to the fields in the query
and put the search field in the header, and changed the display to continious
the records displayed the way I wanted. Still one problem the cursor goes to
the detail records and the results that are displayed are in a edit mode
where I want them read only.

Set the Enabled property of each bound control to No, and the Locked
property to Yes.

John W. Vinson[MVP]