Return results from a parameter query using a custom form back into the original form....

[daddy.dobie]

Ok, this is my last obstacle on my first Access project. What I have
is a form that users can scroll through all of the shops we deal with,
about 900. On the bottom of this form are buttons A-Z so filter the
form to only shops that start with those letters, and an ALL button to
list all the shops again. I made a search button to be able to bring
up a custom form where users can search by any part of a shop name,
address, or zip. I have a marco attached to a search button to run the
parameter query. Everything works fine, except that the filtered
result show up in a spreadsheet view. Could someone please provide
some code or instructions to get the filtered search results to show
up back in the original form? Thanks in advance.....

 

[Chris2]

Ok, this is my last obstacle on my first Access project. What I have
is a form that users can scroll through all of the shops we deal with,
about 900. On the bottom of this form are buttons A-Z so filter the
form to only shops that start with those letters, and an ALL button to
list all the shops again. I made a search button to be able to bring
up a custom form where users can search by any part of a shop name,
address, or zip. I have a marco attached to a search button to run the
parameter query. Everything works fine, except that the filtered
result show up in a spreadsheet view. Could someone please provide
some code or instructions to get the filtered search results to show
up back in the original form? Thanks in advance.....

When you say, "I have a marco", are you using an MS Access macro with
OpenTable or Open Query? That would open a table or query in
datasheet view.

You should be manipulating a recordset for the form created using VBA.

The search button should run VBA code that executes the form's
recordset again based on the changing needs of the user as dictated by
clicks on the A-Z filter buttons (or that requeries it).

That's my best guess, it's hard to know exactly based on the
information available.


Sincerely,

Chris O.

 

[daddy.dobie]

Ok, let me try to be more specific. I have a form that lists about 26
different pieces of information for body shops. Going across the
bottom of this form are buttons A-Z. When a user clicks a button, it
filters the form to just shops body shops that start with that letter.
Right now I have 3 separate buttons. One for Shop Name, Address, and
Zip. These 3 buttons run off of a parameter query. The user clicks how
they want to search, the parameter box box pops up asking for info,
the enter it, hit the button on the parameter query dialog box, and
the filtered results are back in the original form. What I'm trying to
do is to put all 3 of those buttons, the Shop Name, Address, and Zip
all into 1 custom dialog box, and have those results returned to the
original form. So basically I'm after the code to have all 3
parameters entered into the same box, and return the results back to
the form. Everything works good now, except that the results are
displayed in a datasheet. The set up right, user clicks on Search
button, search button has an embedded macro to open Form1. Form1 has
the 3 boxes to enter in shop name, address, and zip. The Ok button
runs the query with the following parameters:

Like [Forms]![Form1]![txtCustomerID] & "*" Or Is Null, and the same
for the other 2 fields. The only part I can't get is for the results
to open back up n the form. Any help with the code would be great!!!

 

[daddy.dobie]

Ok, I have 3 parameter queries that run off command buttons. If I
click the Search Shop button, the default dialog pops up asking the
user to enter a shop name. Clicking the Search address button opens
the default dialog asking for Shop Address. They are different queries
at this point.

What I want in the end, is for the user to click 1 Search button, have
a custom dialog box pop up with all 3 options, be able to click an ok
button, and have those results appear back in the form.

I guess it's really a Filter By Form with a custom dialog box is what
I'm after. You can download what I'm working on at
http://perfectexposure.net/mine/headache.mdb.

What I'm after is to have the final results back in the form
frmCustomers. Does this help more?

 

[Chris2]

When you say, "I have a marco", are you using an MS Access macro with
OpenTable or Open Query? That would open a table or query in
datasheet view.

You should be manipulating a recordset for the form created using VBA.

The search button should run VBA code that executes the form's
recordset again based on the changing needs of the user as dictated by
clicks on the A-Z filter buttons (or that requeries it).

That's my best guess, it's hard to know exactly based on the
information available.

Sincerely,

Chris O.

Ok, let me try to be more specific. I have a form that lists about 26
different pieces of information for body shops. Going across the
bottom of this form are buttons A-Z. When a user clicks a button, it
filters the form to just shops body shops that start with that letter.
Right now I have 3 separate buttons. One for Shop Name, Address, and
Zip. These 3 buttons run off of a parameter query. The user clicks how
they want to search, the parameter box box pops up asking for info,
the enter it, hit the button on the parameter query dialog box, and
the filtered results are back in the original form. What I'm trying to
do is to put all 3 of those buttons, the Shop Name, Address, and Zip
all into 1 custom dialog box, and have those results returned to the
original form. So basically I'm after the code to have all 3
parameters entered into the same box, and return the results back to
the form. Everything works good now, except that the results are
displayed in a datasheet. The set up right, user clicks on Search
button, search button has an embedded macro to open Form1. Form1 has
the 3 boxes to enter in shop name, address, and zip. The Ok button
runs the query with the following parameters:

Like [Forms]![Form1]![txtCustomerID] & "*" Or Is Null, and the same
for the other 2 fields. The only part I can't get is for the results
to open back up n the form. Any help with the code would be great!!!

I apologize.

When I read: "These 3 buttons run off of a parameter query."

I am not able to determine what you mean.

This appears to be ambiguous the way I am reading it.

Command buttons do not "run off of" queries. Command buttons run
queries (or VBA code, etc.).

Also, I do not see how 3 command buttons with different names could
all be running one query in your current setup, they are all seeking
different data entities (Shop Name, Address, Zip), and each would need
to call upon a different parameter query.

----------------------------

I see that your new form is opening in Datasheet view.

What is your new form's Default View property currently set to?

Is it set to "Datasheet View"?


Sincerely,

Chris O.

 

[Chris2]

Ok, I have 3 parameter queries that run off command buttons. If I
click the Search Shop button, the default dialog pops up asking the
user to enter a shop name. Clicking the Search address button opens
the default dialog asking for Shop Address. They are different queries
at this point.

I suspected that this was the setup, but I was not sure.

Thank you for clarifying.

What I want in the end, is for the user to click 1 Search button, have
a custom dialog box pop up with all 3 options, be able to click an ok
button, and have those results appear back in the form.

For that to work the way you want it (for results to appear back on
frmCustomers), Form SearchBox will have to return only one row.
Query1 is set up to return many rows. frmCustomers only displays one
row's worth of information, and it would also have to be changed.

I apologize, but from the way I am reading your description, and
considering the way your forms are built, there appears to be a
contradition (one row display vs. a query that will return multiple
rows that you wish to display). Can you provide further
clarification?

I guess it's really a Filter By Form with a custom dialog box is what
I'm after. You can download what I'm working on at
http://perfectexposure.net/mine/headache.mdb.

What I'm after is to have the final results back in the form
frmCustomers. Does this help more?

Your OK button on Form SearchBox runs Macro2 using the On Click event.
Macro2 does OpenQuery on Query1. It is set for Datasheet View. This
is exactly like double clicking on Query1 yourself. It launches a
Datasheet View of the results for Query1.

Query1 is inaccessible. Any attempt to go into Design View or to
execute it shuts down MS Access. Clicking the OK button on Form
SearchBox does the same thing (shuts down MS Access).

When I use the Immediate Window to access its SQL indirectly, I find:

SELECT Customers.CustomerID
,Customers.CompanyName
,Customers.ContactName
,Customers.ContactTitle
,Customers.Address
,Customers.City
,Customers.Region
,Customers.ZipCode
,Customers.Country
,Customers.Phone
,Customers.Fax
FROM Customers
WHERE (((Customers.CustomerID)
LIKE [Forms]![SearchBox]![txtCustomerID] & "*")
AND ((Customers.Address)
LIKE [Forms]![SearchBox]![txtAddress] & "*")
AND ((Customers.ZipCode) Like [Forms]![SearchBox]![txtZip] &
"*"));

I removed the unneed () (MS Access can be pesky, at times).

SELECT Customers.CustomerID
,Customers.CompanyName
,Customers.ContactName
,Customers.ContactTitle
,Customers.Address
,Customers.City
,Customers.Region
,Customers.ZipCode
,Customers.Country
,Customers.Phone
,Customers.Fax
FROM Customers
WHERE (Customers.CustomerID
LIKE [Forms]![SearchBox]![txtCustomerID] & "*")
AND (Customers.Address
LIKE [Forms]![SearchBox]![txtAddress] & "*")
AND (Customers.ZipCode
LIKE [Forms]![SearchBox]![txtZip] & "*");

This expression will resolve only for data entered in all three fields
where that data does exist in Table Customers.

I deleted Query1, pasted the above into a new Query I named Query1
again, and now it will run.

When I run frmCustomers, click on Search, I see Form SearchBox launch.
I type in "G", "2", and "9". This brings up the first record, in a
Query's Datasheet View, because that it what this is all currently set
up to do.



I think you need to look at this slightly differently.

I cannot figure out any reason for frmCustomers to be first in this
sequence, it is only displaying one row and Query1 will be returning
many rows.



Step 0:

Create a backup of your database.

Step 1:

Create a copy of frmCustomers called frmCustomers2

Create a copy of SearchBox called SearchBox2

Create a copy of Query1 called Query2.


Step 2:

Change Macro2 to run OpenTable and designate the table to be
frmCustomers2 and set Datasheet View.

Step 3:

Change frmCustomers2 to have a Record Source of Query2.

Step 4:

Change Query2 so that all references to SearchBox are switched to
SearchBox2.

Step 5:

Make sure that the On Click event for the OK button on SearchBox2 is
still Macro2

Step 6:

Run SearchBox.

Enter your data (be careful that all three fields are entered and that
data matching at least one of the 7 row is entered).

Click Ok.

It should pop up frmCustomers showing the rows in question in what
amounts to a filter.

---------------------------

Only one set of data entered into SearchBox (L, 8, 9) will return more
than one row as far as i can tell.

---------------------------

It does occur to me that you didn't want multiple rows or any
appearance of datasheet view, in which case the design of Query1 needs
a review so that you can guarantee that it returns one row (which
means no LIKE operators).


Sincerely,

Chris O.

 

[daddy.dobie]

All I get with doing that is an error saying it can't find
frmCustomer2. Not sure of it matters that you told me to open it with
the OpenTable command though. The reason to be able to return multiple
records back to the form is so the user can scroll through them. We do
talk to a few body shops where there are only a few with the same
name, so just putting in the name and letting them scroll through will
work just fine. The original that I downloaded and am working off of
is here: http://perfectexposure.net/mine/search.mdb. This is for the
most part what I want, but without the dialog boxes popping up which I
can take care of, as well as a few other things I'm going to add. But
when I try to change the combo box to a text box, and add another text
box, it all just stops working.

From what I can see. He's making the query a temp record source for

frmCustomers, but I can't figure out how to apply that to what I need.
Hope this helps a little more....

 

[daddy.dobie]

I forgot the 2 at the end of search. It should have been search2.mdb.
But for now that problem has been solved. Got a huge headache from it,
but now I'm on to getting the Enter key to actually enter instead of
acting like a Tab key. Any ideas on this one? I'll post a new thread
about it also.... Thank you for all your help....

 

[Chris2]

The original that I downloaded and am working off of
is here: http://perfectexposure.net/mine/search.mdb. This is for the

I attempted to download this, but I am getting a page not found error.


Sincerely,

Chris O.

 

[Chris2]

All I get with doing that is an error saying it can't find
frmCustomer2. Not sure of it matters that you told me to open it with
the OpenTable command though. The reason to be able to return multiple
records back to the form is so the user can scroll through them. We do
talk to a few body shops where there are only a few with the same
name, so just putting in the name and letting them scroll through will
work just fine. The original that I downloaded and am working off of
is here: http://perfectexposure.net/mine/search.mdb. This is for the
most part what I want, but without the dialog boxes popping up which I
can take care of, as well as a few other things I'm going to add. But
when I try to change the combo box to a text box, and add another text
box, it all just stops working.
frmCustomers, but I can't figure out how to apply that to what I need.
Hope this helps a little more....

Ok, back the headache db.

The other way to do this is to do the following (backup your db,
etc.).

create a copy of frmCustomers named frmCustomers1

Remove all text boxes and labels on the detail section of
frmCustomers1.

Add a subform named subform1 to frmCustomers1 on the detail section.
Select all defaults when going through the subform wizard (include all
columns you need).

Make sure subform1's Default View is set for Single Form.

You will probably have to widen the size of the control once it
appears on frmCustomers after the wizard is done. frmCustomers1 may
need to be wider (probably will need to be).

Remove Macro2 from the Form SearchBox OK button's On Click event
procedure on its property page (Event tab).

On SearchBox, in Design View, select the OK button. On the properties
page (Event tab), change the On Click value to [Event Procedure].

On SearchBox, rename the text4 textbox (for ZipCode) txtZipCode.

The subform's On Click event procedure in the Visual Basic Editor
should look like this:

Private Sub cmdOK_Click()

Dim strInput As String

strInput = "CustomerID LIKE '" & Form_SearchBox.txtCustomerID & "*'"
strInput = strInput & "AND Address LIKE '" &
Form_SearchBox.txtAddress & "*'"
strInput = strInput & "AND ZipCode LIKE '" &
Form_SearchBox.txtZipCode & "*'"

Form_subform1.Filter = strInput
Form_subform1.FilterOn = True

End Sub

When I run SearchBox by clicking on the Search button of
frmCustomers1, I enter some data, I click on OK, and the correctly
filtered rows appear on the subform on frmCustomers.

When I change the data in SearchBox and click OK, the filter changes,
and correctly.

This appears to me to be what you were looking for.

You can add another button to frmCustomers1, "Clear Filter", and use
it to set the FilterOn property to False.

You can also play with the code in many ways to customize it,
including allow for no data in any of the text boxes, etc.

Sincerely,

Chris O.

 

[Chris2]

I forgot the 2 at the end of search. It should have been search2.mdb.
But for now that problem has been solved. Got a huge headache from it,
but now I'm on to getting the Enter key to actually enter instead of
acting like a Tab key. Any ideas on this one? I'll post a new thread
about it also.... Thank you for all your help....

Check my second reply to your immediately previous post. I sent it
only a few minutes ago.

I think it works exactly the way you wanted it to.

As for the Enter Key . . . I am not sure what you mean.

When you press enter, it indicates you are done with any changes, wish
to "update" the current field, and move on.

What other behavior are you looking for?


Sincerely,

Chris O.