Macro challenging - this is a good one!

[Jackblack1]

If I have a Run query button on a form, and I want the query to run in the background and not display the query form, How do I do this?

Utlimately, I would like to refresh the form by clicking the refresh button on the form and have it run the query in the background. I have the code for the two listed below, can anyone help me join them. How could I combine the two macro so that it does this.

If you really want to be slick, The query i am running is a subform in a form, can you get it so the subform updates when you click the refresh button?

Form name = Duplicates
SubForm name = lstGratuityduplicates

Thanks Jim


The code I have so far linked to the run query button.

Private Sub run_query_Click()
On Error GoTo Err_run_query_Click

Dim stDocName As String

stDocName = "Find_duplicates_entries_Gratuities_List"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_run_query_Click:
Exit Sub

Err_run_query_Click:
MsgBox Err.Description
Resume Exit_run_query_Click

End Sub


For the refresh button, the code is:

Private Sub openclose_Click()
On Error GoTo Err_openclose_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Close

stDocName = "duplicates"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_openclose_Click:
Exit Sub

Err_openclose_Click:
MsgBox Err.Description
Resume Exit_openclose_Click

End Sub

 

[Norie]

Jack

So you didn't really solve your problem?

Have you looked at the Requery method?

 

[Jackblack1]

I haven't solve the problem, I think I may be taking to many steps for this one. I just wanted to update the subform that I have in the form, based on the query. I know it won't update itself automatically, so I figured the refresh button may cut down the steps. Any ideas to make this easier.

Jack

 

[Norie]

Jack

Like I said take a look at the Requery method.

You can use that to update the Record Source of a form (or sub form).

You could drive this using one of the events of one of the controls on the main form.

 

[Jackblack1]

You are a genius! One quick question there is a little pause when updating is there anything I can do to speed it up a little, if not thats ok, better then what I first came up with..

 

[Norie]

I don't think you can speed it up.

 

[Jackblack1]

Hi

Thanks for your help, one more question, when this subform gets updated I have to pick through the list and find the duplicates, what I would like to do is, when I delete the duplicate, have a text box that will display the one deleted so that I can match it up with its equal. Then when finish it disappears.

Jim