use me! vs report name? (1 Viewer)

[madEG]

Hello,

Generally, I'm lucky enough to not need to make many reports... which is a good thing since I am fairly weak at it.

Sadly, I must be improving since more people are asking for stuff.

"Love the report, can you now sort it three more ways?"

Me: OK...

So I copy paste the report three times, change the sorting, and immediately stumbled upon my amateur ways... I use this line in a where clause of a query:

and dbo_SIP_TimesheetEntries_RMBS.sCostCenterCodeIDf = reports!rptTimeSheetReport!sCostCenterCodeIDf

rptTimeSheetReport is the name of my original report, which I am to sort a few more ways.

I was hoping to do something generic using "me!" so that the other report instances look to themselves for the objects, vs reports!report_name!report_object" and describing the item from the top, so to speak. Of course I'd prefer not to maintain as many queries as I have reports.

Like I said I am a report newb...

I thought this would work...

and dbo_SIP_TimesheetEntries_RMBS.sCostCenterCodeIDf = me!sCostCenterCodeIDf

...since the field sCostCenterCodeIDf is on the report uniquely named... I thought the "me!" would tell the report to look inward at itself - its "me-ness" if you will

No dice. I get errors.

Can someone please push me in the right direction?

Thank you!
-Matt G.

 

[pbaldy]

Me is only valid in VBA code. I would only have one report, and have code in it to set the sorting based on user selection on a form.

 

[madEG]

Thanks, so with that in mind, I went to design view on the report, and removed the three sorts, and left the single grouping item from the "group, sort, and total" area. (MS Access2010).

What was:

sort by lastname, sort by firstname, groupby employeeID, sort by projectcode

now just shows groupby employeeID asc

Then I added this to the on click event for the button that calls the report:

    Dim rpt As Report
    Dim strReportName As String
    strReportName = "rptTimeSheetReport"
    
        DoCmd.OpenReport strReportName, acViewDesign
        Set rpt = Reports(strReportName)
        rpt.OrderByOn = True
        rpt.OrderBy = "slastname, sFirstname, sCostCenterCodeIDf"
        DoCmd.Close acReport, rpt.Name, acSaveYes
    
        DoCmd.OpenReport strReportName, acViewPreview

...and now the report seems to continue to group by properly, but the records seem to be coming out in employee ID order and skipping the name sorts entirely.)

Should I not be doing this in two different places?

I tried some sorting and grouping ideas I dug up to no avail. Is this even close?

    Dim rpt As Report
    Dim strReportName As String
    strReportName = "rptTimeSheetReport"
    
        DoCmd.OpenReport strReportName, acViewDesign
        Set rpt = Reports(strReportName)
        rpt.OrderByOn = True
        rpt.OrderBy(0).ControlSource = "slastname"
        rpt.OrderBy(1).ControlSource = "sFirstname"
        rpt.GroupLevel(0).ControlSource = "sEmployeeID"
        rpt.OrderBy(2).ControlSource = "sCostCenterCodeIDf"
        DoCmd.Close acReport, rpt.Name, acSaveYes
    
        DoCmd.OpenReport strReportName, acViewPreview

and I tried this:

     Dim rpt As Report
     Dim strReportName As String
     strReportName = "rptTimeSheetReport"
     
         DoCmd.OpenReport strReportName, acViewDesign
         Set rpt = Reports(strReportName)
         rpt.OrderByOn = True
         rpt.OrderBy = "slastname, sFirstname, sCostCenterCodeIDf"
         rpt.GroupLevel(0).ControlSource = "sEmployeeID"
        DoCmd.Close acReport, rpt.Name, acSaveYes
     
         DoCmd.OpenReport strReportName, acViewPreview

I think I'm missing something fundamental (other than how to properly make reports!) heh.

Can I get another push?

 

[pbaldy]

I've never tried to do it that way. I do it from within the report, like:

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 'group on company
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3 'sort on date/time, no grouping
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

 

[The_Doc_Man]

In Ac2010, there is the possibility that a report has its sort/group properties built-in from the time you designed it and the OrderBy report property doesn't work in that case. There is a separate control on the report in design mode for Sorting/Grouping. If you used it, my comment applies.

 

[madEG]

Paul, So I understand what I'm trying to do... Does your suggestion take this grouping control area (please see attachment jpg) and reorder it to change that order? Is that what your approach does?

If yes, then I would want to leave in my default sort and group-by stuff (like in the image) and then use your code to "move up and down" the order of the items that group and sort?

Is that what I'm shooting for?

The_Doc_Man, if you could look at that pic too - I think that area seems to override the report's OrderBy property... even though that trick I found that pushes the report through a design mode moment, adding columns to the OrderBy property... it seems to not work/be overridden by the sort/group control area indicated in the pic...

Is that what you are saying?

Thanks for hanging in there with me - I'm gonna get this!

 

[pbaldy]

Paul, So I understand what I'm trying to do... Does your suggestion take this grouping control area (please see attachment jpg) and reorder it to change that order? Is that what your approach does?

That's effectively what it does. In actuality it's more like clicking on the drop downs and changing the field displayed for the given level. GroupLevel(0) is the first one, a second would be GroupLevel(1), etc.

 

[madEG]

Ok, I made it through, and I have two of my three reports working.

    Case 1 '
      Me.GroupLevel(0).ControlSource = "sLastName"
      Me.GroupLevel(1).ControlSource = "sFirstName"
      Me.GroupLevel(2).ControlSource = "sEmployeeID"
      Me.GroupLevel(3).ControlSource = "sCostCenterCodeIDf"
      
      Me.GroupLevel(0).SortOrder = False
      Me.GroupLevel(1).SortOrder = False
      Me.GroupLevel(2).GroupOn = 1
      Me.GroupLevel(3).SortOrder = False
    
    Case 2 '
      Me.GroupLevel(0).ControlSource = "sCostCenterCodeIDf"
      Me.GroupLevel(1).ControlSource = "sLastName"
      Me.GroupLevel(2).ControlSource = "sFirstName"
      Me.GroupLevel(3).ControlSource = "sEmployeeID"
      
      Me.GroupLevel(0).SortOrder = False
      Me.GroupLevel(1).SortOrder = False
      Me.GroupLevel(2).SortOrder = False
      Me.GroupLevel(3).GroupOn = 1
    
    Case 3 '
      Me.GroupLevel(0).ControlSource = Mid("sCostCenterCodeIDf", 7, Len("sCostCenterCodeIDf") - 7)
      'Me.GroupLevel(1).ControlSource = Left("sCostCenterCodeIDf", 5)
      Me.GroupLevel(1).ControlSource = "sLastName"
      Me.GroupLevel(2).ControlSource = "sFirstName"
      Me.GroupLevel(3).ControlSource = "sEmployeeID"

      Me.GroupLevel(0).SortOrder = False
      Me.GroupLevel(1).SortOrder = False
      Me.GroupLevel(2).SortOrder = False
      Me.GroupLevel(3).GroupOn = 1

I'm not sure that I really needed the .GroupOn, but it seemed more complete to push a sort or group on each level.

Ok - hopefully last question...!

The string slicing I am trying to work out to change the sort order on the third CASE on the CostCenterCodes... do I stand any chance of making that work with this approach?

Perhaps I need to get at the value of the grouplevel's control source, but I can't think of how. I looked over the help in F1 (which was better than I thought) but I am not seeing the light. Any ideas?

Thanks again!

regards,
-MattG.

 

[pbaldy]

I would probably create a field in the report's source query with that function, and use that field name in this code. Like:

SpecialSort: Mid([sCostCenterCodeIDf], 7)

Note if the length argument of the Mid() function is omitted, it will grab to the end, which looks like what you're trying to do.

 

[madEG]

pbaldy - today I *actually* remembered to use MID() without the length argument... rather than solving for length /doing that dance.

 

[pbaldy]

Excellent! You're better than I am; I usually have to reinvent the wheel 4-5 times before I remember the shortcuts.

 

[gemma-the-husky]

the easiest way is "quick and dirty"

do it 3 times, and have three different queries

-----
another way is to store the reference in a variable. You can't use the variable directly - you have to "read" the variable in a function

so you get, instead of
and dbo_SIP_TimesheetEntries_RMBS.sCostCenterCodeIDf = me!sCostCenterCodeIDf

use
"and dbo_SIP_TimesheetEntries_RMBS.sCostCenterCodeIDf = " & RequiredCostCentre()

where
RequiredCostCentre() is a function to read the variable

---

then you can get the time to try paul's way of manipulating the report sort criteria.