Refresh query results

[sdzc]

Refresh query results** Updated with db example

This is probably a simple one for somebody:

I have a query based off of parameters on a form. A command button on the form then runs the query.

I would like to be able to change the parameters on the form and re-run the query without having to close the query.

Possible?

Thanks!

Also, I am not that good with code and will need fairly detailed instructions...

 

[WayneRyan]

sdzc,

Just use the AfterUpdate event for the last of your parameter fields and
just do a --> Me.Requery

You don't have to "Close" the form's current recordset, Access will just
change context. The previous data is in the tables, but not necessarily
in the form's current recordset.

Wayne

 

[sdzc]

Just use the AfterUpdate event for the last of your parameter fields and
just do a --> Me.Requery

The last drop down box gets its choices from a table. Will the Me.Requery just change that or will it actually requery the query and get new results based on the changed parameters in any of the five fields?

I would have thought the refresh would have been tied to the button that calls the query.

 

[WayneRyan]

s,

You can do a few things here:

The --> Me.Requery is for the form.
The --> SomeParameterBox.Requery is for your parameter boxes.

Assume that you have three controls:

1st - Combo1 for Department; Criteria = Like "*" & Forms![YourForm]![Combo1] & "*"
2nd - Combo2 for Employee; Criteria = Like "*" & Forms![YourForm]![Combo2] & "*"
3rd - TextBox for Description; Criteria = Like "*" & Forms![YourForm]![TextBox] & "*"

If you defaulted them to "", then your form would open up with all records.

When someone selects a Department from Combo1, you can do 2 things:
1) Requery the whole form --> Me.Requery
2) Requery Combo2 if it is dependent on Combo1 --> Me.Combo2.Requery

You can do either, or both.

Wayne

 

[sdzc]

I appreciate your help, but I am not sure I am explaining this correctly based on your response.

I click a button and it runs query. The query results (looks like a spreadsheet) then comes up behind the form.

I want to adjust some parameters on the form and press the button on the form again to get new results in the (spreadsheet) query result.

I tried to add Me.Requery to the After Update portion of the Form properties, but it did not cause the query results to change. I did close out of the form and save changes.

Hopefully this clarifies a little. Thanks again for your help!

 

[sdzc]

Just wondering if anyone has any ideas on this.

Another thought was to show the query results at the bottom of the form in a box (subform?), but how to get it to show the datasheet view from the query that way actually in the form?

 

[sdzc]

Thought I might ask again to see if I can get some additional ideas or help.

Thanks!!

 

[adevera]

Can you post your DB so we can look at it and figure out the problem ?

Thanks

 

[sdzc]

db example attached. Looking for help!!

I have attached a sample db that is laid out similarly to the one I have.

In the attached example, go to the form and search "Blue" as the only criteria in the Vehicle Color box. Run the query.

After that enter Toyota in Vehicle Make (Blue remains as the color) and click the run query button again. It does not search for Blue Toyotas, it does not refresh/requery anything. In order to do the search for Blue Toyotas, you have to close the datasheet for the query.

I would like to just be able to press the button after changing the criteria on the form without having to manually close the datasheet.

Thanks Again!!

 

[sdzc]

Still looking for assistance!!

I added a sample database, so if anyone has any ideas, I would appreciate the help.

 

[adevera]

I've looked on your database and you are opening a query base on your criteria.

The thing I can suggest is that put this query in a subform and have a subform property to display it as Datasheet.

If you dont want to do this, you have to create procedure to determine if the query is open or not after you hit the button.

 

[Epic]

because the sql has finished the job

If you don't want to close the query and open it again ... i do not know why... then you have to save the search results into a table, bound the table fields to a form then requery the form or filter each time you changed anything.



or you can use the results of your closed query to create/populate your search results table.