Macro Looping

M

miacino

Registered User.
Local time
Today, 05:55
Joined
Jun 5, 2007
Messages
106
I have a macro that consists of several queries. I am looking to have it run through all the queries based on records in a table. (i.e., run all the queries from the 1st record in the table, then all the queries using the 2nd record in the table, etc.)

Is this do-able?
 
Are these queries UPDATE, APPEND or DELETE queries?
 
The macro is actually to print several reports on patients. I want to run all the reports for patient1, then loop back and print all the reports for patient2, etc.
 
So the macros run reports and NOT queries, like you originally said???

In other words you use

Action: OpenReport
Argument - ReportName: ReportNameHere

and then you do another and another?

Sorry - EDIT -

I'll post in a second, some VBA which can help with this.
 
If you have reports that don't include criteria on who it is for, you can use this VBA code to cycle through the table and print the reports for each person:
Code: 
Function PrintAllPatientReports() 
   Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("YourTableWithPatientInfoHere")
 
Do Until rst.EOF
    DoCmd.OpenReport "YourReport[B]1[/B]NameHere", , , "[PatientIDFieldNameHere]=" & rst!PatientIDFieldFromTableHere
    DoCmd.OpenReport "YourReport[B]2[/B]NameHere", , , "[PatientIDFieldNameHere]=" & rst!PatientIDFieldFromTableHere
    DoCmd.OpenReport "YourReport[B]3[/B]NameHere", , , "[PatientIDFieldNameHere]=" & rst!PatientIDFieldFromTableHere
    DoCmd.OpenReport "YourReport[B]4[/B]NameHere", , , "[PatientIDFieldNameHere]=" & rst!PatientIDFieldFromTableHere
    rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing

So you substitute PatientIDFieldNameHere with the field name in your report's underlying recordsource that corresponds to the Patient ID. Keep that field name in square brackets, especially if it has spaces or special characters in the name.

You also substitute PatientIDFieldFromTableHere with the actual field name from the table of patients which will match up to the PatientIDFieldNameHere which is in your report's underlying recordsource.

If the PatientID is text then you have to add quotes to get the code to work:
Code: 
Function PrintAllPatientReports() 
   Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("YourTableWithPatientInfoHere")
 
Do Until rst.EOF
    DoCmd.OpenReport "YourReport[B]1[/B]NameHere", , , "[PatientIDFieldNameHere]=" & Chr(34) & rst!PatientIDFieldFromTableHere & Chr(34)
    DoCmd.OpenReport "YourReport[B]2[/B]NameHere", , , "[PatientIDFieldNameHere]=" & Chr(34) & rst!PatientIDFieldFromTableHere & Chr(34)
    DoCmd.OpenReport "YourReport[B]3[/B]NameHere", , , "[PatientIDFieldNameHere]=" & Chr(34) & rst!PatientIDFieldFromTableHere & Chr(34)
    DoCmd.OpenReport "YourReport[B]4[/B]NameHere", , , "[PatientIDFieldNameHere]=" & Chr(34) & rst!PatientIDFieldFromTableHere & Chr(34)
    rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing

I hope that helps.
 
Thanks! Am trying! Not too familiar with coding or VB.
Can I put that code in on the "on click" build code part of a command button I create?
 
Doesn't seem to be working. This is the code I'm using. (The CCMC# is a number, not text.) Do I need to name something "PrintAllPatientsReports"?
-----------------------------------
Private Sub Command0_Click()
PrintAllPatientReports()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("APPOINTMENTS")

Do Until rst.EOF
DoCmd.OpenReport "EN-Frontsheet (all)-temp", , , "[CCMC#]=" & rst!ccmc#
DoCmd.OpenReport "temp-shortstature (all)", , , "[CCMC#]=" & rst!ccmc#


rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Function
---------------------------------------------------

 
miacino said:
Doesn't seem to be working. This is the code I'm using. (The CCMC# is a number, not text.) Do I need to name something "PrintAllPatientsReports"?
-----------------------------------
Private Sub Command0_Click()
PrintAllPatientReports()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("APPOINTMENTS")

Do Until rst.EOF
DoCmd.OpenReport "EN-Frontsheet (all)-temp", , , "[CCMC#]=" & rst!ccmc#
DoCmd.OpenReport "temp-shortstature (all)", , , "[CCMC#]=" & rst!ccmc#


rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Function
---------------------------------------------------
Click to expand...

A couple of things.

1. Get rid of this part: PrintAllPatientReports()

2. Because your fields have special characters (which is BAD, BAD, BAD, BAD, BAD practice - did I mention it was bad? :D ) you have to include your field names in square brackets:
Code: 
DoCmd.OpenReport "EN-Frontsheet (all)-temp", , , "[CCMC#]=" & rst![COLOR=red][B][[/B][/COLOR]ccmc#[COLOR=red][B]][/B][/COLOR]

You should not use special characters (or spaces really) in object names or field names. So your - in the report namd and the ( and ) should not be there either really.
 
Getting there! Thank you so much! Am am getting a msg "Open Report action was cancelled" when it hits a report that doesn't need to be printed. (as indicated by the underlying query)... Ex: a patient that doesn't have allergies, the allergy form won't print....
The code doesn't just keep going it halts...
Anything we can do to modify?? :)
 
In the report's On No Data event put:

Code: 
Cancel = True

On the vba put an error handler in -

Code: 
Private Sub Command0_Click()
Dim rst As DAO.Recordset
 
[B][COLOR=red][I]On Error GoTo Command0_Err[/I][/COLOR][/B]
 
Set rst = CurrentDb.OpenRecordset("APPOINTMENTS")
 
Do Until rst.EOF
DoCmd.OpenReport "EN-Frontsheet (all)-temp", , , "[CCMC#]=" & rst![ccmc#]
DoCmd.OpenReport "temp-shortstature (all)", , , "[CCMC#]=" & rst![ccmc#]
 
 
rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing
 
[COLOR=red][I][B]Command0_Exit:[/B][/I][/COLOR]
[COLOR=red][I][B] Exit Sub[/B][/I][/COLOR]
 
[COLOR=red][I][B]Command0_Err:[/B][/I][/COLOR]
[COLOR=red][I][B] If Err.Number = 2501 Then[/B][/I][/COLOR]
[COLOR=red][I][B]    Resume Next[/B][/I][/COLOR]
[COLOR=red][I][B] Else[/B][/I][/COLOR]
[COLOR=red][I][B]    Msgbox Err.Description, vbExclamation, "Error #: " & Err.Number[/B][/I][/COLOR]
[COLOR=red][I][B]    Resume Command0_Exit[/B][/I][/COLOR]
[COLOR=red][I][B] End If[/B][/I][/COLOR]
End Function
 
You've been a huge help!

Here is my current code (abbreviated version):
--------------------
Private Sub Command0_Click()
Dim rst As DAO.Recordset

On Error GoTo Command0_Err
Set rst = CurrentDb.OpenRecordset("APPOINTMENTS")

Do Until rst.EOF
diabetes patients reports....
DoCmd.OpenReport "FRONT_GLOBAL", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "FRONT_DB", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_DB1", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_DB2", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_TYPE2DB1", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_TYPE2DB2", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_DB_2WK", , , "[CCMC#]=" & rst![CCMC#]
endo patients reports....
DoCmd.OpenReport "FRONT_ENDO", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_GH_TEACH", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_INJ", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_CGMS", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_CGMS_DATA", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "TEMP_THYROID", , , "[CCMC#]=" & rst![CCMC#]
DoCmd.OpenReport "FU_APPTS", , , "[CCMC#]=" & rst![CCMC#]


rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Command0_Exit:
Exit Sub

Command0_Err:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
Resume Command0_Exit

End If
End Sub
----------------------------------------

One more question!
In the APPOINTMENTS table that it is looping in, there is PATIENT_TYPE. Is there a way to put in that if the patient type = "Diabetes" to only run through the first half of the reports anc continue looping, and if patient type="Endo" to run only the second part of the reports and continue looping?

Appreciate your help!!
Michele
 
You would need to change to this:
Code: 
Do Until rst.EOF
[I][COLOR=#ff0000]diabetes patients reports....[/COLOR][/I] 
   DoCmd.OpenReport "FRONT_GLOBAL", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "FRONT_DB", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_DB1", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_DB2", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_TYPE2DB1", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_TYPE2DB2", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_DB_2WK", , , "[CCMC#]=" & rst![CCMC#]
rst.MoveNext
Loop
[I][COLOR=red]endo patients reports....[/COLOR][/I]
[COLOR=black]rst.MoveFirst[/COLOR]
Do Until rst.EOF
   DoCmd.OpenReport "FRONT_ENDO", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_GH_TEACH", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_INJ", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_CGMS", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_CGMS_DATA", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_THYROID", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "FU_APPTS", , , "[CCMC#]=" & rst![CCMC#]
rst.MoveNext
Loop
 
But I still want them to run in the order of the table. I.e., I don't want it to run all the diabetes ones first and then all the endo.

I just want it to skip over running the endo reports IF the field (patient_type) in the appointments table is "diabetes".

Is this at all do-able?
:o
 
miacino said:
But I still want them to run in the order of the table. I.e., I don't want it to run all the diabetes ones first and then all the endo.

I just want it to skip over running the endo reports IF the field (patient_type) in the appointments table is "diabetes".

Is this at all do-able?
:o
Click to expand...

Okay, well - then it would be:
Code: 
Do Until rst.EOF
[COLOR=blue][B]Select Case rst!patient_type[/B][/COLOR]
[COLOR=blue][B]Case "diabetes"[/B][/COLOR]
[I][COLOR=#ff0000]diabetes patients reports....[/COLOR][/I] 
   DoCmd.OpenReport "FRONT_GLOBAL", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "FRONT_DB", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_DB1", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_DB2", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_TYPE2DB1", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_TYPE2DB2", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_DB_2WK", , , "[CCMC#]=" & rst![CCMC#]
[COLOR=blue][B]Case "endo"[/B][/COLOR]
[I][COLOR=red]endo patients reports....[/COLOR][/I]
   DoCmd.OpenReport "FRONT_ENDO", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_GH_TEACH", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_INJ", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_CGMS", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_CGMS_DATA", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "TEMP_THYROID", , , "[CCMC#]=" & rst![CCMC#]
   DoCmd.OpenReport "FU_APPTS", , , "[CCMC#]=" & rst![CCMC#]
[COLOR=blue][B]End Select[/B][/COLOR]
rst.MoveNext
Loop
 
You must log in or register to reply here.

Similar threads

C
Access 21 problem with where command
2
Replies
24
Views
1,452
Isaac
Isaac
D
Solved Creating a button to run a Delete Query
Replies
6
Views
651
Pat Hartman
P
D
Subform requery is starting a new record
Replies
4
Views
668
dullster
D
C
Strange behavior after macro conversion
Replies
14
Views
1,182
Gasman
Gasman
T
Solved Import Filepath Batch
Replies
12
Views
805
Tophan
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom