Help with a MACRO build for Report Print Previews

[dgaletar]

This should be fairly simple... I am probably making it harder than it needs to be.

Here's what I have:

• 3 tables named "Reports - Parts Main", "Reports - Filters Used", "Reports - Belts Used", and "Reports - Batteries Used"
• 3 correlating forms named "Reports - Parts Main", "Reports - Filters Used", and "Reports - Batteries Used"
• The entries in the "Reports - Parts Main" table/form are:
  
  • Filters
  • Batteries
  • Belts
  • Tires
  
  
• The entries in the "Reports - Filters Used" table/form are:
  
  • "Air Filters by Mower"
  • "Fuel Filters by Mower"
  • "Oil Filters by Mower"
  
  
• The entries in the "Reports - Belts Used" table/form are:
  
  
  
  • "Deck Belts by Mower"
  • "Hydro Belts by Mower"
  • "PTO Belts by Mower"
  
  
• The entries in the "Reports - Batteries Used" table/form are:
  
  • "Batteries by Mower"

I also have the following reports:

• "Air Filters by Mower"
• "Batteries by Mower"
• "Fuel Filters by Mower"
• "Deck Belts by Mower"
• "Hydro Belts by Mower"
• "PTO Belts by Mower"
• "Tire Size Report"

What I would like is this:

• When you click on the "View Parts Used Reports" button on the home page, that it would close that form and open the "Reports - Parts Main" form.
  
  • When in the "Reports - Parts Main" form, if you click on the:
    
    • "Filters" line, that it would close that form and open the correlating form
    • "Batteries" line, that it would close that form and open the correlating form
    • "Belts" line, that it would close that form and open the correlating form
    
    
  • But, if you click on the "Tires" line, that it would close that form and open the correlating Report, the "Tire Size Report".

I think that this would all be done with a MACRO, which is why I am using this forum.

Any and all help would be GREATLY appreciated!

DG

[Ruegen]

when you open report (you can make a button with a wizard) in the "where" section of the macro you can place

="WhateverYourMowerTableID=" & me.button

the macro will see it like this

WHERE "23 = 23"



however the button has to have an initial value or get it's value form somewhere like the record you are currently on

so ="WhateverYourMowerTableID=" & me.YourRecordsCurrentID

then tell it to run another command to close the previous form.

Generally I use a combo box to pick what I want to filter a report by

then use that to be the value to filter a report by.

I can add date ranges too by putting in two text boxes on my form as date fields (this helps to narrow down a report).

[dgaletar]

I am either not explaining it right, or not understanding your response. (Please bear with me. This is DEFINITELY not my strong suit!)

I don't understand

WHERE "23 = 23"

. I am talking about starting from scratch on the home page... no records open. I click on the button that I have there. That button has an "Embedded Macro" in the "On Click" line.

The embedded Macro looks like this:



The Form that it opens looks like this:

• When you select the first line, "Filters", it will open a similar form titled "Reports - Filters Used". That form, instead of what you see above, will display "Air Filters by Mower", "Fuel Filters by Mower", and "Oil Filters by Mower" on it.
  
  • When you select the first line, "Air Filters by Mower", it will open the actual report titled "Air Filters by Mower" in the Print Preview mode
  • When you select the second line, "Fuel Filters by Mower", it will open the actual report titled "Fuel Filters by Mower" in the Print Preview mode
  • When you select the third line, "Oil Filters by Mower", it will open the actual report titled "Oil Filters by Mower" in the Print Preview mode
  
  
• Likewise, when you select the second line, "Batteries", it will directly open the actual report titled "Batteries by Mower" in the Print Preview mode
• Belts would be setup the same as Filters, and Tires would be setup the same as Bateries

Does any of this help to make more sense???

DG

[Ruegen]

I am either not explaining it right, or not understanding your response. (Please bear with me. This is DEFINITELY not my strong suit!)

I don't understand . I am talking about starting from scratch on the home page... no records open. I click on the button that I have there. That button has an "Embedded Macro" in the "On Click" line.

The embedded Macro looks like this:



The Form that it opens looks like this:

• When you select the first line, "Filters", it will open a similar form titled "Reports - Filters Used". That form, instead of what you see above, will display "Air Filters by Mower", "Fuel Filters by Mower", and "Oil Filters by Mower" on it.
  
  • When you select the first line, "Air Filters by Mower", it will open the actual report titled "Air Filters by Mower" in the Print Preview mode
  • When you select the second line, "Fuel Filters by Mower", it will open the actual report titled "Fuel Filters by Mower" in the Print Preview mode
  • When you select the third line, "Oil Filters by Mower", it will open the actual report titled "Oil Filters by Mower" in the Print Preview mode
  
  
• Likewise, when you select the second line, "Batteries", it will directly open the actual report titled "Batteries by Mower" in the Print Preview mode
• Belts would be setup the same as Filters, and Tires would be setup the same as Bateries

Does any of this help to make more sense???

DG

sort of - you want the words or string say, filters, to open the report called filters.

I am guessing you have a listbox for the list to select from?

each listbox has a column. Column 1 should be the ID, column 2 should be the description field i.e. Filters (and what is visible).

If you want the form to open to the same you have to stuff the string "filters" (because it is a string) into a variable

then call the variable instead of the form name in the macro

You can call temp variables and when finished opening the report - remove the tempvariable (you don't want to keep it after finished with it).

so on the button you could make a temp var, then give it the value of column(2) - this would be YourListBoxName.column(2)

then you can use that to open your form by putting the variable in as the name of the report - it will then open the report because the variable is the string "filters" which is equivalent of typing filters as the form name

this way no mater what you click, tempvar will become that and then the form should open to what is clicked.

you could make the variable when you click the listbox value or when you press the button.

update :

if you do the tempvar on the listbox - you can customise what the value of it will be so click on filter and the stored value will be "Reports - Filters Used"

[dgaletar]

sort of - you want the words or string say, filters, to open the report called filters.

Actually, I want the word or string say, filters, to open a similar form named "Reports - Filters Used" that lists the reports that exist for the filters (ie: "Air Filters by Mower", "Fuel Filters by Mower", and "Oil Filters by Mower").

Then, I want the words or string say, Air Filters by Mower, to open the report called "Air Filters by Mower".

But, for the word "Batteries" and the word "Tires", I want the words or string say, Batteries / Tires, to open the related report called "Batteries" / "Tires".

I am guessing you have a listbox for the list to select from?

I don't think that I am using a listbox for this. Instead I believe that it is an "unbound object".

Here you can see the property sheet for the main form, "Reports - Parts Main" form:


Here you can see the "unbound object" with it's properties:


The "Run Report" button has an [Event Procedure] in the "On-Click" command line:

Option Compare Database

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

    Dim stDocName As String

    stDocName = "MacroPartsMain"
    DoCmd.RunMacro stDocName

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
    
End Sub

And here's the MACRO for the reports (I know that the MACRO has a different name. I messed up the original and am just using this one as an example of how it was laid out):


The best way that I can think to recap is with a flow chart:


Does this help at all???

[dgaletar]

OK, well, the good news is that I somehow figured out how to open each of the specific reports from the "View Reports" form. This is how I did it:

1. TABLES:
   
   • I created 5 new tables: "tblRprtPartsMain", "tblRprtFiltersUsed", "tblRprtBatteriesUsed", "tblRprtBeltsUsed", and "tblRprtTiresUsed".
   • The structure for each of these tables is:
     
     • ReportID - Autonumber
     • ReportName - Text
     • ReportDesc - Text
     
     
   • All are setup the same
   
   
2. QUERIES:
   
   • I then created 5 new queries: "Add or Delete Parts Main Reports", "Add or Delete Filters Main Reports", "Add or Delete Batteries Main Reports", "Add or Delete Belts Main Reports", and "Add or Delete Tires Main Reports".
   • The SQL for these queries is:
     
     • SELECT tblRprtPartsMain.ReportName, tblRprtPartsMain.ReportID, tblRprtPartsMain.ReportDesc
       FROM tblRprtPartsMain;
     
     
   • All are setup the same
   
   
3. FORMS:
   
   1. I then created 10 forms:
      
      1. The first 5 are designed to allow edits to the respective tables, and are titled: "frmRprtListPartsMain", "frmRprtListFiltersMain", "frmRprtListBatteriesMain", "frmRprtListBeltsMain", and "frmRprtListTireSizesMain".
         
         • The record source for these is: "tblRprtPartsMain" (etc.), and they are viewed in Datasheet format.
         
         
      2. The second 5 are designed to function as a sort of switchboard to open the respective reports. They are titled: "View Parts Main", "View Filter Types", "View Battery Types", "View Belt Types", and "View Tire Sizes".
         
         • They are designed with the following:
           
           • A header section
           • An "Unbound Object" named "lstReports"
           • A button named "Edit Report List..."
           • A button called "Open Report..."
           
           
         • The header section has a small image and the words "View Reports"
         • The "Unbound Object" is labeled "Select a Report to Open", and has the following key properties:
           
           • CONTROL SOURCE: BLANK
           • EVENTS Tab: empty
           • The "Unbound Object" has:
             
             • a ROW SOURCE of "Add or Delete Tires Main Reports"
             • a ROW SOURCE TYPE of "Table/Query"
             • bound to column 1
           
           
         • The button named "Edit Report List..." has the following "On Click" function:
           
           • View Tire Size Reports Macros.EditReportList : On Click
           
           
         • The button named "Open Report..." has the following "On Click" function:
           
           • View Tire Size Reports Macros.cmdOpenReport : On Click
   
   
4. REPORTS:
   
   1. I then created the following reports:
      
      1. FILTERS (3): "Air Filters by Mower", "Fuel Filters by Mower", and "Oil Filters by Mower".
      2. BATTERIES (1): "Batteries by Mower"
      3. BELTS (3): "Deck Belts by Mower", "Hydro Belts by Mower", and "PTO Belts by Mower".
      4. TIRES (1): "Tire Size Report"
   
   
5. MACROS:
   
   1. I then created the following 4 Macros: "View Filter Reports Macros", "View Belt Reports Macros", "View Battery Reports Macros", and "View Tire Size Reports Macros".
   2. This is the design for the "View Filter Reports Macros":

Now, with all of that being said, if you open the form "View Filter Types", it displays the 3 types of filters that I have listed in the "tblRprtFiltersUsed". If you select any of the filters and then click on the "Open Report..." button, it closes the "View Filter Types" and open the respective filter report.

PERFECT!

But, as you can see, I don't have a way to open the "View Filter Types" form from the "View Parts Main" form.

I'm trying to keep the forms looking consistant.

Any ideas on how to do this???

DG