Find correct report and print

[Debbie37]

Please Help!!!
I need to find/print monthly Preventative Maintenance Reports for equipment where the report name is the same as a field from a query [ProcedureName] and [datedue].
My problem is that each checklist (report) is different and is determined by EquipType + ModelName=[ProceureName]. So, I need to filter by date and get the name of the report = to [ProcedureName] and send the correct data to each report and print......can someone please help me...!!!

 

[FancyPrairie]

I'm not sure I understand what you are trying to do. Do you want to open a report based on a value from a query (i.e. [ProcedureName]? If so, then issue the following command:

DoCmd.OpenReport rst![ProcedureName] & rst![DateDue]

If you are trying to determine the names of the reports available in your database, run the following query:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764));

If this doesn't answer your question, could you give me an example of what you are trying to do.

 

[Debbie37]

Sorry for the confusion, let me try again. I want to run a query (by dates) that will give me a list of the report checklists that need to be printed for the month. In this query is the field name [ProcedureName] which is the name of the procedure and the saved report. Based on this name I want Access to find the correct report (there may be more than one per report checklist) and print. The following sends the first record (actually 2 of the same name) correctly, but I need it to continue through the recordset. I know it needs a do...loop statement but I just can't seem to get it! I do have a query that lists the report names as you you have also suggested above, but I am not sure if that is what I need. Thanks tons!!!

Private Sub Command24_Click()
Dim RprtName As String
Dim ProcedName As String
Dim oRs As Object

Set oRs = Me.Recordset.Clone

RprtName = [ProcedureName]
ProcedName = "[equipnameid]=" & Me![EquipNameID]

     oRs.MoveFirst
    DoCmd.OpenReport RprtName, acPreview, , ProcedName
    oRs.MoveNext
       
End Sub

This gives me two reports, BedsideMonitors_4200, but I also need the IVPumps_4100, etc....

 

[FancyPrairie]

I'm in class this week so I only have a couple of minutes to digest your problem. I'll look at it more tonight. But you might try this

Private Sub Command24_Click()
Dim RprtName As String
Dim ProcedName As String
Dim oRs As Object

Set oRs = Me.RecordsetClone

RprtName = [ProcedureName]
ProcedName = "[equipnameid]=" & Me![EquipNameID]

oRs.MoveFirst

while not oRs.eof 'ADDED THIS LINE

DoCmd.OpenReport RprtName, acPreview, , ProcedName
oRs.MoveNext

wend 'ADDED THIS LINE
End Sub

 

[Debbie37]

I appreciate your time, to be sure!!!! I tried your suggestion and I got 3 sets (6) of the same report (there are only 3 records, two are the same ProcedureName, =6). It only sees one report, aghhh. Anything else you can suggest I am willing to try anything at this point! Thanks again

 

[FancyPrairie]

Sorry, I was in such a hurry (instructor began class) that I didn't look close enough at your code. The "While" statement will loop thru all of the records in the recordset until it reaches the end (oRs.eof). I'm assuming each record contains an EquipNameID field. Therefore, you need to build "ProcedName" inside the While statement. Again, I assuming the name of the report will be the same for each iteration of the loop, just the ProcedName will change (which is the Where part of the OpenReport method). Me![EquipNameID] is the value on the form but in the recordset it would read oRs![EquipNameID]. If my assumptions are true, then the following code should work. Or at least it may lead you in the right direction. If not, let me know.


RprtName = [ProcedureName]

oRs.MoveFirst

while not oRs.eof 'ADDED THIS LINE

ProcedName = "[equipnameid]=" & oRs![EquipNameID] 'CHANGED THIS LINE

DoCmd.OpenReport RprtName, acPreview, , ProcedName
oRs.MoveNext

wend

 

[Debbie37]

I apreciate your preserverance....here we go...the [procedname] AND report names will change, always equal to each other (i.e. BedsideMonitor_4200, IVPump_4100, Ventillator). Each different piece of equipment has a different preventative maintenance checklist. So while many models of the same type may require PM service, there will also be others. I may have 10 BedsideMonitors, 3 IVPumps etc. all due in the same month. I have been able to find/open, print and close one type of report, I need to find the rest and do the same. Can you help? I am sorry I am unable to convey this less painfully then I have...

 

[FancyPrairie]

I'm sorry but I'm having a hard time visualizing what you're doing. Can you give me a better example. For example, can you list the names of the reports, and the possible equipment ID's. You say you have 10 Bedside Monitors, does that mean there are 10 reports, 1 for each monitor? Since you can print one report but can't print the others, can you show me what the OpenReport command would look like if you could "find the rest and do the same". For example,

Docmd.OpenReport Report01, acPreview, , ProcedName
Docmd.OpenReport Report02, acPreview, , ProcedName
Docmd.OpenReport Report03, acPreview, , ProcedName

What does "ProcedName" look like?

Here's what I think I understand.

Report Names Equipment ID
------------------- ----------------
BedsideMonitor_4200 1,2,3
BedsideMonitor_4300 2,4,5
BedsideMonitor_4400 1,7,8
...
IVPump_4100 1,2,8
IVPump_4200 3,7,9
IVPump_4300 4,2,2

 

[Debbie37]

EquipNameTable
EquipNameID
EquipName
(This is a look-up table)

EquipTable Reports
EquipID every procedname will
EquipType = the name of a
Model report
Dept
ProcedName

Yes, if 10 BesideMonitors need to be serviced, I would need to print 10 separate reports (checklists) to service them. There may be 10, 15 or more different reports (pm procedure checklists) and many of each that need to be printed. It opens the first one (all that need to be printed for one type), but then does not continue and send the others. Does this make sense? The code (above) works just not for all the records in the recordset.

 

[Guest_imported]

Private Sub Command24_Click()
Dim RprtName As String
Dim ProcedName As String
Dim oRs As Object
Set oRs = Me.Recordset.Clone

oRs.MoveFirst 'go to first record

while not oRs.eof
'set the name of the report to print for this record
RprtName = [ProcedureName]
ProcedName = "[equipnameid]=" & Me![EquipNameID]
'Print it
DoCmd.OpenReport RprtName, acPreview, , ProcedName
    oRs.MoveNext
Wend
      
End Sub

 

[Debbie37]

I REALLY appreciate the help, but I think I am ready to give up! Here is what happens: (I have only four records in my test database, 2 BedsideMonitors_4200 and 2 IVPumps_4100) Using the [equipnameid] field restricts the records sent to the same type (i.e. only the BedsideMonitor records go to the saved report of the same name) while just in preview, but then no others are sent. If I open AND print the records all four records go to the first report it finds BesideMonitors_4200, even the IVPumps_4100 that should open the IVPumps_4100 report. Can this even be done? I am sooooo frustrated, I am doing something wrong and I can't figure out what. Any more suggestions are welcome...PLEASE!!!

 

[FancyPrairie]

Your last post cleared things up some. However, in your first post you said you want to also filter on date due. I don't see a date due in your tables. This is what I now think you're telling me. In the EquipTable you have a field labeld "ProcedName" which is the names of the reports. Each record of the EquipTable contains not only the report name (i.e. ProcedName) but also the equipment ID. What I think you want to do is print out all of the records that have the same ProcedName and whose equipment ID matches the one listed on your form. It also looks like the RecordSource of your report is refering to the table EquipNameTable. If this is the case, the following code should handle it. (You must set a Reference to Microsoft DAO 3.6 Object Library for this code to work.)

By the way, what you're trying to do is doable. It's just hard trying to visualize what somebody else is trying do. I know it's clear to you. But the light bulb has not gone on for me yet. Your last post did help.

Private Sub Command24_Click()

dim dbs as DAO.Database
dim rst as DAO.Recordset 'ProcedName only
dim strEquipNameIDs as string

strEquipNameIDs = "[EquipNameID] = " & Me![EquipNameID]

'*********************************************************
'* Create a recordset that contains only ProcedName *
'* Then loop thru the recordset to open a report based *
'* on the report name referenced by ProcedName *
'*********************************************************

set dbs = CurrentDb
set rst = dbs.OpenRecordset("Select Distinct ProcedName from EquipTable;&quot

while not rst.eof

Docmd.OpenReport rst!PocedName,acPreview,,strEquipNameIDs

wend
End Sub

If this doesn't work, then
1. show me some actual data in the table EquipTable.
2. Show me which fields are being entered on the form?

 

[Debbie37]

Thanks for sticking around! I tried the above code and it never fully opened the report and locked up the database. What you have gathered so far is correct except for: since the button will be on the main form for convenience, it will run a query (MonthlyReports) to gather and restrict the recordset by date to those that have PM procedures due for the month. This is the same RecordSource for all of the reports. The data in the query MonthlyReports is as follows:
EquipID DeptName EquipName
1 Nursing Bedside Monitor
2 Operating Room IV Pump
3 Lab IV Pump
PMDue Model ProcedName
2/15/2002 4200 BedsideMonitor_4200
3/05/2002 4100 IVPump_4100
2/28/2002 4100 IVPump_4100
EquipNameID
2
1
1
[EquipID] uniquely identifies each record (pk)and [EquipNameID] uniquely identifies each type of equipment in the look-up table. All of the above fields (plus a few others...SerialNum, Manufac, etc) are also on the report. The ReportNames are BedsideMonitor_4200, IVPump_4100, etc., there will be lots more! When trying your code I changed things to reflect what I described above, to no avail. Does this clear things up for you? I know this is difficult to visualize, I am having trouble myself I am just getting over the flu and thank you for your patience as I have struggled to get out a clear picture, but this is as much as I can tell you unless you have something specific you would like to know... like the exact location of this darn project to put us both out of our misery?? LOL Again, thanks tons and tons.

 

[FancyPrairie]

I don't have time to evaluate your last message (I'll look at it tonight). However, I made a mistake in my code (I always forget to do it). You need to add the line "rst.MoveNext" after the "Docmd.OpenReport" and prior to the "wend" statement. Otherwise the "While" loop executes in an infinite loop. That's why it looked like your database locked up on you.

The other possible problem is the Select statement in the OpenRecordset statement. If one of the records contains a null (blank) for "ProcedName", then the DoCmd.OpenReport will fail.

I'm pretty sure what I suggested should work (or at least shed some light on what you are trying to do). One thing you can do is to use the debugger to examine the code as you process it. If you've never used the debugger, do the following. Enter the word "stop" on the line immediately before the "Docmd.OpenReport" command. Then execute the code. The program will then pause at the "stop" command. Hold the cursor over the value you want to examine (a help tip showing the value of the variable will be displayed). The best way to do it is to open the Immediate window and type in "?rst!PocedName" and then hit return. To continue to step thru the code on line at a time, press F8. To continue without stepping thru each line of code, press F5. To set a break point (stop at line command), select the line and press F9 (toggles breakpoint on/off).

Give what I suggested a try and see if that gets you closer to your solution.

 

[Debbie37]

WE (you) are getting closer....!!!! I have to run (I have to teach this afternoon), but wanted to tell you that it opens both reports (only have two made, IVPumps_4100 and BedsideMonitor_4200 for right now, but there will be lots more)but it sends the records in the order they appear in the recordset. i.e. IVPumps_4100 opened the BedsideMonitor_4200 report. I played around a little but it didn't make a difference. In the statement strequipnameid=....is there any way to use a variable for the report name = procedname? Would that work? My success on getting variables to work (referencing) is sketchy at times. I will look for your post later this evening. Happy day...we (you) are getting closer, you have no idea how this has made my day. I was really beginning to think I wouldn't be able to deliver this piece of it...you are FAB!

 

[FancyPrairie]

Sorry I didn't get back with you last night (got home late from my daughter's Invention Fair at school). Glad we're getting closer. I believe the problem is with the RecordSource of your report(s). They are not referring to the correct [ProcedName]. You are setting the Where clause of the Report in the OpenReport statement ([EquipNameID]). However, you're not specifying the [ProcedName]. I assumed it was set in the RecordSource of the Report.

1. Make sure the RecordSource of the Report is setup correctly or set the Where clause of the OpenReport Method as follows:
strEquipNameIDs = "([EquipNameID] = " & Me![EquipNameID] & &quot AND ([ProcedName] = '" & rst!PocedName & "')"
(NOTE the single quotes)

2. Is there a reason why you need to develop a bunch of reports. Seems like you can do what you want with 1 report. It appears that the columns are all the same just data would be different. The other thing that would be different in each report would be the title, which represents [ProcedName]. If so, [ProcedName] could be what you group on in the report (see Sorting and Grouping) via the View Menu item.

3. Did you try using debug. If so, did it shed some light on anything?

Class is starting (short class today), let me know how the above plays out and we'll go from there.

 

[Debbie37]

Sorry this is late, but I have GREAT news! It works! Happy days are here again.......Here is what finally got us there:

Private Sub Command24_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset 'ProcedName only
Dim strEquipNameIDs As String
'*********************************************************
'* Create a recordset that contains only ProcedName *
'* Then loop thru the recordset to open a report based *
'* on the report name referenced by ProcedName *
'*********************************************************
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select Distinct procedName, equipnameid from MonthlyReports;&quot

While Not rst.EOF
'Stop
strEquipNameIDs = "([equipnameid] = " & rst![EquipNameID] & &quot AND ([ProcedName]= '" & rst!ProcedName & "')"

DoCmd.OpenReport rst!ProcedName, acPreview, , strEquipNameIDs
rst.MoveNext

Wend
End Sub

For strEquipnameIDs I changed it to look at the recordset instead of just the current record and added equipnameid to the SELECT statement. Also, I learned that if you state/define your where clause before you set rst it results in an error.....object variable or With block not set

Now, to answer your last questions. The reason I need to create so many reports is because although the equipment and related fields will remain the same the body of the report (checklist) will be different for every equip/model type. I chose not to put these reports/checklists in Word and do a merge because I am less familar with restricting and controlling events etc. than I am w/ Access (believe it or not). Besides, someone told me that after repeated merges Word can corrupt itself....didn't want to take any chances.

In answer to your last question, I did use the debugger and it was VERY helpful. Thanks for the tip! Thank you so much!!!

If you are up to one more question/problem I'd appreciate it, if not I will write a separate post. Work Order#s are determined by the two-digit month, two-digit year and an incrementing number (based on the number of repairs for that month). For example, the first repair for this month would be 03021, then 03022, 03023, 03024, etc. I have two separate fields: one is a date field (mmyy)[WrkOrdrDate] and the other a number field [WrkOrdr#]. How do I - 1. get the number field to auto-increment 2. have it restart on the first day of the month. In playing around I could get it to increment by one for the current record, not for the recordset. Any ideas? If your not up to it I understand and THANKS tons and tons for sticking with me this last week!!
Have a GREAT day!

 

[FancyPrairie]

Really glad it's finally working for you. Too bad it took so long to figure it out.

Concerning the Work Order #s, will need a little more information.

1. Are the 2 fields [WrkOrdrDate] and [WrkOrdr#] defined in a table, form or both?

If the fields are defined in a table and exist on a form, then use the OnCurrent event of the form to check to see if the field [WrkOrdr#] needs to be incremented. If you only want it to increment on New records then check the variable Me.NewRecord. For example,

If (Me.NewRecord) then
If (DatePart("d",Date) = 1) and (not already reset) then
[WrkOrdr#] = 1
'set flag to indicate already reset
Else
[WrkOrdr#] = [WrkOrdr#] + 1
End If
End If

There are several things you have to consider concerning the above code.
1. If more than 1 user can be running this code at the same time, you will have contention problems.
2. Some how you need to know that you have already reset the [WrkOrdr#] to 1 at the beginning of the month (so that you don't reset it again just because the date indicates the first day of the month).

Off hand, I might set up a table with the 2 fields, the current work order number and the date it was reset. Then, I would open the record (locked so other user's can't update while I'm trying), increment [WrkOrdr#], if today is the first day of the month set [WrkOrdr#] to 1 and set the date field to todays date. Then immediately write the record back out.

2 things you might consider (you may not have control over this). With the mmyy syntax, the date 12021 (December 2002) would come after 01051 (Janurary 2005) if you were to sort the data by the Work Order Dates. If this is a problem, change the Work Order Date to the format yymm. Also, I would avoid using the # sign in the names of fields. Access uses the # sign as a wildcard (for Like clauses).