Multiple queries, reports - pause between

[Guest]

Hi,
I am running multiple queries to merge/purge/clean up data. I would like to
string the different events in a vba script but I'm not sure how to pause
between events. For example, I run a query that deletes the old data,
another that copies new data from source, another that copies data from
another source, then a query that pulls up records with missing data elements
- which I would like to stop at so I can look at and fix or make decisions
on. Then I have the next query or a report, etc.

Can any one tell me a good way to pause so I can finish with one query
before going on the next. I did this with a macro and then converted it to
VBA as below:

DoCmd.SetWarnings False
If (MsgBox("Are you sure?", 1) <> 1) Then
Exit Function
End If
' Zap liens.
DoCmd.OpenQuery "ZAPRECS", acViewNormal, acEdit
' Zap Updebtor
DoCmd.OpenQuery "ZapUpDebtor", acViewNormal, acEdit
' Zap Upsecured
DoCmd.OpenQuery "ZapUpSecured", acViewNormal, acEdit
' Append 19D
DoCmd.OpenQuery "19dAppend", acViewNormal, acEdit
' Append ADM
DoCmd.OpenQuery "AdmAppend", acViewNormal, acEdit
' Append Keydata
DoCmd.OpenQuery "AppendKeydata", acViewNormal, acEdit
' Query to check for missing elements

DoCmd.OpenQuery "CkBlanks", acViewNormal, acEdit
' Delete records with REASON filed in (not keyed)

DoCmd.OpenQuery "DelReason", acViewNormal, acEdit
' Query to get keyer counts (print)
DoCmd.OpenQuery "KeyerCount", acViewNormal, acEdit
' DB1Upload - copy debtors to updebtor table
DoCmd.OpenQuery "DB1Upload", acViewNormal, acEdit
' DB2Upload - copy co-debtors to updebtor table
DoCmd.OpenQuery "DB2Upload", acViewNormal, acEdit
' PL1Upload - copy secured party to upsecured table
DoCmd.OpenQuery "PL1Upload", acViewNormal, acEdit
' back up liens to C:\images\oldzips\sentbk.dbf (overwrites)
DoCmd.TransferDatabase acExport, "dBase 5.0", "c:\images\OLDZIPS",
acTable, "Liens", "SentBK.dbf", False
' Run upload report.
DoCmd.OpenReport "UCC Upload Report", acViewPreview, "", "", acNormal

Thanks in advance.

Bonnie

 

[John W. Vinson]

Hi,
I am running multiple queries to merge/purge/clean up data. I would like to
string the different events in a vba script but I'm not sure how to pause
between events. For example, I run a query that deletes the old data,
another that copies new data from source, another that copies data from
another source, then a query that pulls up records with missing data elements
- which I would like to stop at so I can look at and fix or make decisions
on. Then I have the next query or a report, etc.

If the list of queries will always be the same, you could just use your
existing code and put a Msgbox after each:

Dim iAns As Integer

<run a query>
iAns = MsgBox("Keep going?", vbYesNo)
If iAns = vbNo Then GoTo BailOut
<run another query>
....

BailOut: Msgbox "Queries interrupted"

John W. Vinson [MVP]

 

[Guest]

Thanks for responding John. I had the worst time finding this response -
something was stopping me from seeing anything from early in the day 6/5 in
the community but it seems to be okay now.

I tried doing what you suggested but the problem is that I would like to fix
some of the records that I am pulling up with one or two of the queries
before I go on and the MsgBox precludes me from doing that. Is there some
way to run a query from one Macro, finish it up and then go on to the next
Macro that runs a query, etc.?

Thanks,

Bonnie

 

[John W. Vinson]

Thanks for responding John. I had the worst time finding this response -
something was stopping me from seeing anything from early in the day 6/5 in
the community but it seems to be okay now.

I tried doing what you suggested but the problem is that I would like to fix
some of the records that I am pulling up with one or two of the queries
before I go on and the MsgBox precludes me from doing that. Is there some
way to run a query from one Macro, finish it up and then go on to the next
Macro that runs a query, etc.?

Well... I don't do Macros so I'm not sure what I'd recommend there. I thought
these were *ACTION* queries, which don't "pull up" anything you can edit - are
you actually opening query datasheets and editing them? What do you mean by
"finishing it up"??

John W. Vinson [MVP]

 

[Guest]

Hi John,

Some of them are action queries but there are two that are just showing me
some records that I need to make a decision about (like whether to remove the
record or change the data, etc.) and then go on to the next process. I can
use VBA if that is a better choice.

Thanks,


Bonnie

 

[John W. Vinson]

Hi John,

Some of them are action queries but there are two that are just showing me
some records that I need to make a decision about (like whether to remove the
record or change the data, etc.) and then go on to the next process. I can
use VBA if that is a better choice.

You could base a Form (datasheet or continuous view) on the Select queries;
rather than opening the query, open the Form in Dialog mode (see the help for
the OpenForm action). This will stop the macro or the code from continuing
until you close the form.

John W. Vinson [MVP]

 

[Guest]

You could base a Form (datasheet or continuous view) on the Select queries;
rather than opening the query, open the Form in Dialog mode (see the help for
the OpenForm action). This will stop the macro or the code from continuing
until you close the form.

John W. Vinson [MVP]

 

[Guest]

Oh good idea. I think that will work. I'll work on it today. I may come
back with more questions but thanks very much for the help.

Bonnie

 

[Guest]

Hi John,

I'm having a little trouble with this, not sure what I'm doing wrong. I
have set the form to open in Dialog mode and according to Access Help, the
Macro should suspend running until I close it, but it continues to run the
next lines. It does require me to close the datasheet to preview/print the
preceeding reports - but it has popped them up behind the datasheet. Any
ideas?

Thanks for all your help.

Bonnie

 

[Guest]

Hi John,

I don't know why that didn't work as we expected but I just thought of an
easy way to do it - I'll put an ON CLOSE event in the form to continue the
rest of the processing. I converted it to module and started fooling with it
and realized that I overlooked that simple answer.

Again, thanks for all your help.

Bonnie

 

[John W. Vinson]

Hi John,

I'm having a little trouble with this, not sure what I'm doing wrong. I
have set the form to open in Dialog mode and according to Access Help, the
Macro should suspend running until I close it, but it continues to run the
next lines. It does require me to close the datasheet to preview/print the
preceeding reports - but it has popped them up behind the datasheet. Any
ideas?

Again... I rarely do macros at all, so I have no experience with opening a
dialog form from a macro. I'd really suggest doing it in code. It's no harder
to write (at least for something linear like this) than a macro; lets you trap
errors; and I *know* that opening the form in Dialog will stop the code.

John W. Vinson [MVP]