How to have 1 Sub call a 2nd Sub after closing a query

[gurbaitis]

I have a number of queries that I'm trying to automate. The stickler is that one of the queries, a check of duplicates, opens so that the duplicates are displayed and can be deleted.

What I want to happen is that once that query is closed, it procedes with the rest of them. How do I do that? The first set of them are driven by a Click from a form, but I don't know how to get it to "Continue". (In my example, how to start up CmdFIT1qry3b() when qry03a_FindDUps_tblSourceFIT1 is finished)

Thanks! Here's what I've got:

Option Compare Database
Option Explicit

Private Sub CmdLoadFIT1_Click()
On Error GoTo Err_CmdLoadFIT1_Click


   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qry01_Clear_tblSourceFIT1"
   DoCmd.OpenQuery "qry02_AppendTo_tblSourceFIT1"
   DoCmd.OpenQuery "qry03a_FindDUps_tblSourceFIT1",   acViewNormal, acEdit
   DoCmd.SetWarnings True
    

Exit_CmdLoadFIT1_Click:
    Exit Sub

Err_CmdLoadFIT1_Click:
    MsgBox Err.Description
    Resume Exit_CmdLoadFIT1_Click
    
End Sub


Private Sub CmdFIT1qry3b()
On Error GoTo Err_CmdFIT1qry3b


   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qry03b_Corrections_tblSourceFIT1", acViewNormal, acEdit
   DoCmd.OpenQuery "qry04_BatchPrefix_SMST", acViewNormal, acEdit
   DoCmd.OpenQuery "qry05_BatchPrefix_SMSP", acViewNormal, acEdit
   DoCmd.OpenQuery "qry06_BatchLoad_NonBillableAcctsFIT1"
   DoCmd.SetWarnings True
    

Exit_CmdFIT1qry3b:
    Exit Sub

Err_CmdFIT1qry3b:
    MsgBox Err.Description
    Resume Exit_CmdFIT1qry3b
    
End Sub

 

[vbajock]

Use the query as the recordsource on a form. User deletes data, put a button on the form for them to push when they are done, put the continuation code behind the button.

 

[gurbaitis]

vbajock - true, that will do it. However, I'm trying to be fancy and not make the user have to do that (There are actualy more steps in the real process - I just cut out a couple as an example - so it would require a number of buttons). But thanks!

 

[randy700]

What part of your current code doesn't work?

Randy

 

[gurbaitis]

The sub CmdLoadFIT1_Click executes properly, so queries 01 to 03a. It doesn't do CmdFIT1qry3b - queries 03b-06. Theoreticaly I need a user action event triggered by closing query 03a to start up CmdFIT1qry3b.

 

[TheAceMan1]

How are ya gurbaitis . . .

Obviously you have [blue]break stopping to delete duplicates![/blue] This can be done by using [blue]qry03a_FindDUps_tblSourceFIT1[/blue] as a subquery in a delete query (using the in clause), or it can be done by parsing thru a recordset based on [blue]qry03a_FindDUps_tblSourceFIT1[/blue]. A big question arises here: [purple]Is it your intent to delete all dups, or all dups save the one?[/purple] With this done your code should revert to:

[blue]Private Sub CmdLoadFIT1_Click()
On Error GoTo GotErr
   
   DoCmd.SetWarnings False
   
   DoCmd.OpenQuery "qry01_Clear_tblSourceFIT1"
   DoCmd.OpenQuery "qry02_AppendTo_tblSourceFIT1"
   DoCmd.OpenQuery "qry03a_DeleteDUps_tblSourceFIT1"

   DoCmd.OpenQuery "qry03b_Corrections_tblSourceFIT1", acViewNormal, acEdit
   DoCmd.OpenQuery "qry04_BatchPrefix_SMST", acViewNormal, acEdit
   DoCmd.OpenQuery "qry05_BatchPrefix_SMSP", acViewNormal, acEdit
   DoCmd.OpenQuery "qry06_BatchLoad_NonBillableAcctsFIT1"
   
SeeYa:
   DoCmd.SetWarnings True
   Exit Sub

GotErr:
   MsgBox Err.Description
   Resume SeeYa

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[gurbaitis]

The problem with that is that they're not exact duplicates, so it takes viewing them to make a judgement call. For example McDonalds, 123 Fountain, Hollywood, CA is, for us, a duplicate of McDonalds, 123 W. Fountain Ave., Los Angeles, CA.

So we need the query to show the results (We base on Comany name & Zip - so we'd get a number of McDOnalds in L.A.) and let the user delete or not delete. Again, the delete is just one of many times screens are going to pop up, some action taken or not, and the process continue. My code is just a small portion of a larger number of queries.

 

[TheAceMan1]

Roger that gurbaitis . . .

Semi-automatic ops always causes me to cringe

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]