how to refresh query results

[jw08]

Hi all

I have a form with a 4 combo boxes that I can choose different criteria to get my query results.

I also have a button at the bottom of my form, when I click this button, it will runs all queries to get the results datasheet.

now I want change the criteria in the combo box and I was wondering what should I do to refresh my results datasheets by just clicking my button without going to query datasheet to click 'refresh all'?

Thanks

 

[vbaInet]

You can't refresh queries unless you close and reopen them. The best thing to do is to create a form based on your query, and set the Default View to Datasheet. Then to "refresh" it you will use Me.Requery

 

[jw08]

Thanks

My form is actually based on my query, and it is set to datasheet view.

if I have to use Me.query, where should I put it?

I have tried put me.query in the VBA code under the button on my form, but it doesn't work.

 

[vbaInet]

Note it's Me.Requery, not Me.Query.

Put it in the Click event, click the Code Builder and it will take you to the VBA editor.

 

[pr2-eugin]

On click of the button, Check if the Query (Form in Datasheet view) is opened/Loaded, if so requery else open.. Something like..

If Not CurrentProject.AllForms("[COLOR=Blue]yourFormName[/COLOR]").IsLoaded Then 
    DoCmd.OpeForm "[COLOR=Blue]yourFormName[/COLOR]"
Else
    [COLOR=Blue]yourFormName.[COLOR=Black]Requery
End If

[/COLOR][/COLOR]

 

[vbaInet]

On click of the button, Check if the Query (Form in Datasheet view) is opened/Loaded, if so requery else open.. Something like..

If Not CurrentProject.AllForms("[COLOR=Blue]yourFormName[/COLOR]").IsLoaded Then 
    DoCmd.OpeForm "[COLOR=Blue]yourFormName[/COLOR]"
Else
    [COLOR=Blue]yourFormName.[COLOR=Black]Requery
End If
[/COLOR][/COLOR]

Note that a form in Design View is also loaded.

 

[jw08]

Thanks guys

However, it doesn't work. here is the code for my button, could you please have a look and let me know if it's correct?


Private Sub Command100_Click()
DoCmd.OpenQuery "UniqueCompany"
DoCmd.OpenQuery "JobTitle"
DoCmd.OpenQuery "PublicationDate2"
DoCmd.OpenQuery "ClickThrough"
If Not CurrentProject.AllForms("SearchF2").IsLoaded Then
DoCmd.OpeForm "SearchF2"
Else
SearchF2.Requery
End If
End Sub


Basically, I have 4 combox that associated with 4 queries, and then click the button to run all of those 4 queries and it will give me a datasheet. and I want to change the criteria in the combo box and click the button to refresh my data on the datasheets without click that 'refresh all' button on my datasheet.

Thanks

 

[vbaInet]

Where did you put the criteria?

 

[jw08]

I put criteria in the combox, so I can choose whichever criteria i want from the list of my combox.

 

[vbaInet]

You said your combo box drives your form, i.e. when you select a value in your combo box you want your form to be filtered based on the value in the combo box.

The criteria needs to be in the form's query.

 

[jw08]

sorry I guess it was my mistake, I didn't mean criteria

those 4 combo box contain a list of values that I can choose and then click the button to run the query to get my results datasheet

 

[jw08]

so it's exactly what you've described, my combo box drives your form, i.e. when you select a value in your combo box you want your form to be filtered based on the value in the combo box.

so was my coding correct based on that?

 

[vbaInet]

Ok, so where is the criteria that filters the form? Or you need help with this too?

 

[jw08]

I have sorted out the criteria, my problem is just the button on the form

it's just I want to run the query by clicking the button after I change the value of combox.

currently, I need to click 'refresh all' button on the datasheet every time after I change a value of the combox, I want to just click my button on the form instead of go to datasheet to click 'refresh all'.

Thanks

 

[vbaInet]

I've already given you the code for requerying the form.

You just need to reference the form correctly. See here:

http://access.mvps.org/access/forms/frm0031.htm

 

[jw08]

Thanks

but it doesn't work, and I really don't know why.

now the code for my button is

Private Sub Command100_Click()
DoCmd.OpenQuery "UniqueCompany"
DoCmd.OpenQuery "JobTitle"
DoCmd.OpenQuery "PublicationDate2"
DoCmd.OpenQuery "ClickThrough"
Me.Requery
End Sub

Is it correct?

 

[vbaInet]

You should be opening the forms that the queries are bound to. And like I said before, you requery the relevant form using the right reference. Me refers to the form where the code is placed, so use the link I gave you to get the correct reference.