not sure how to use GroupSort 1

[techsup07]

I have the following code in my report module as the report is generated with VBA based on the data on the form.

'    Select Case Forms!frmScheduled!grpSort
'    Case 1 'Name
'        Me.GroupLevel(0).ControlSource = "ScheduleDate"
'        Me.GroupLevel(1).ControlSource = "What"
'    End Select

i can't figure out how to use the "grpSort" on my frmScheduled so that this GroupLevel will work.

what, where, and how do I specify and use the "grpSort" on my form so the rest of this will work?

 

[maxhugen]

Try:

    ' Set SortOrder property.
    ' False=ASC, True=DESC
    Me.GroupLevel(0).SortOrder = grpSort

Just make sure your grpSort returns either 0 (False) or -1 (True).

Max Hugen
Australia

 

[techsup07]

Hi Max!
humh, ok, not sure but I got a Compile Error: Syntax error

 

[dhookom]

I would expect grpSort to be an option group or list box or combo box. The form would need to be open with a value selected.

    Select Case Forms!frmScheduled!grpSort
    Case 1 'Schedule Date
        Me.GroupLevel(0).ControlSource = "ScheduleDate"
        Me.GroupLevel(1).ControlSource = "What"
    Case 2 'EmployeeID
        Me.GroupLevel(0).ControlSource = "LastName"
        Me.GroupLevel(1).ControlSource = "FirstName"
    End Select

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[techsup07]

Ok, i'm thinking this won't work with what I have.
I am using a calendar form which has to be opened then I generate the Report.

The report displays exactly as it does on the form.

to make this simple or easy for me to try and explain ...
The ADD form where the schedule is entered displays the data as required BUT on the VIEW form it is not and the report is based off the VIEW form.

Private Sub Form_Current()
    '**Purpose: Populate the various text fields with dates relative to selection
    
    Dim datFirst As Date
    Dim datTemp As Date
    Dim strDay As String
    Dim strTemp As String
    Dim intCount As Integer
    Dim intCounter As Integer
    Dim intDayCount As Integer
    Dim ctl As Control
    Dim intTemp As Integer
    Dim strTemp2 As String
    Dim datTemp2 As Date
    Dim i As Long
    Dim tmpText As String
    Dim tmpText2 As String
    Dim eventDate(10000) As Date
    Dim eventID(10000) As String
    Dim eventName(10000) As String
    Dim eventWhat(10000) As String
    Dim events As Long
    Dim myset As Object
    Dim Criteria As String
    Dim condition As Integer

'    Select Case Forms!frmScheduled!grpSort
'    Case 1 'Name
'        Me.GroupLevel(0).ControlSource = "ScheduleDate"
'        Me.GroupLevel(1).ControlSource = "ID"
'    End Select

    'load events table data into array
    events = 0
    
    'Read in qry_Calendar to Array
    Set myset = CurrentDb.OpenRecordset("SELECT tblSchedule.* FROM tblSchedule WHERE (((tblSchedule.ScheduleDate) Is Not Null)) ORDER BY tblSchedule.ScheduleDate, tblSchedule.ScheduleDate;", DB_OPEN_SNAPSHOT)
    Criteria = "[ScheduleDate] <> Null"
    myset.FindFirst Criteria
    Do Until myset.NoMatch
        events = events + 1
        If IsNull(myset![id]) Then
            eventDate(events) = myset![ScheduleDate]
            eventID(events) = myset![id]
            eventName(events) = myset![name]
            eventWhat(events) = myset![What]
        Else
            For i = 1 To Int(DateDiff("d", myset![ScheduleDate], myset![ScheduleDate])) + 1
                eventDate(events + i - 1) = DateAdd("d", i - 1, myset![ScheduleDate])
                eventID(events + i - 1) = myset![id]
                eventName(events) = myset![name]
                eventWhat(events) = myset![What]
            Next i
            events = events + i - 1
        End If
        myset.FindNext Criteria
    Loop
    
    'Read in qry_Calendar2 to Array
    Set myset = CurrentDb.OpenRecordset("SELECT tblSchedule.* FROM tblSchedule WHERE (((tblSchedule.ScheduleDate) Is Null) And ((tblSchedule.ScheduleDate) Is Not Null)) ORDER BY tblSchedule.ScheduleDate, tblSchedule.ScheduleDate;", DB_OPEN_SNAPSHOT)
    Criteria = "[ScheduleDate] <> Null"
    myset.FindFirst Criteria
    Do Until myset.NoMatch
        events = events + 1
        eventDate(events) = myset![ScheduleDate]
        eventID(events) = myset![id]
        eventName(events) = myset![name]
        eventWhat(events) = myset![What]
        events = events + i - 1
        myset.FindNext Criteria
    Loop
      
    'Clear all pre-existing fields
    intCounter = 1
    Do
        Me("label" & intCounter).Caption = ""
        Me("label" & intCounter).ControlTipText = ""
        Me("label" & intCounter).ForeColor = 0
        Me("label" & intCounter).Visible = False
        Me("text" & intCounter).Caption = ""
        Me("text" & intCounter).Visible = False
        Me("text" & intCounter).BackColor = 16777215
        Me("id" & intCounter).Caption = ""
        intCounter = intCounter + 1
    Loop Until intCounter = 38
    
    'Set date
    datFirst = strMonth & ". 1/" & intYear
    
    'Find weekday of first day of month (integer format if possible)
    strDay = Format(datFirst, "ddd")
    
    'Set initial counter depending on day of week the corresponding month starts on
    Select Case strDay
        Case Is = "Mon"
            intCounter = 1
        Case Is = "Tue"
            intCounter = 2
        Case Is = "Wed"
            intCounter = 3
        Case Is = "Thu"
            intCounter = 4
        Case Is = "Fri"
            intCounter = 5
        Case Is = "Sat"
            intCounter = 6
        Case Is = "Sun"
            intCounter = 7
    End Select
        
    'Find number of days in selected month by datediff from 1st day of next month in selected year
    datTemp = DateAdd("m", 1, datFirst)
    intDayCount = DateDiff("d", datFirst, datTemp)
    
    'Populate calendar with date's
    intCount = intCounter
    strTemp = ""
    Do
        Me("label" & intCount).Caption = intCount - intCounter + 1
        Me("label" & intCount).ControlTipText = strMonth & " " & intCount - intCounter + 1 & ", " & intYear
        Me("label" & intCount).Visible = True
        Me("text" & intCount).Visible = True
        
        tmpText = ""
        tmpText2 = ""
        
        'Fixed Events
        If intCount - intCounter + 1 = 25 And strMonth = "December" Then tmpText = Chr(13) + Chr(10) + "Christmas Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 26 And strMonth = "December" Then tmpText = Chr(13) + Chr(10) + "Boxing Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 1 And strMonth = "January" Then tmpText = Chr(13) + Chr(10) + "New Years Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 15 And strMonth = "January" Then tmpText = Chr(13) + Chr(10) + "Martin Luther King's Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 14 And strMonth = "February" Then tmpText = Chr(13) + Chr(10) + "Valentine's Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 19 And strMonth = "February" Then tmpText = Chr(13) + Chr(10) + "President's Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 20 And strMonth = "February" Then tmpText = Chr(13) + Chr(10) + "NO SCHOOL": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 14 And strMonth = "March" Then tmpText = Chr(13) + Chr(10) + "NO SCHOOL": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 28 And strMonth = "May" Then tmpText = Chr(13) + Chr(10) + "Memorial Day": Me("text" & intCount).BackColor = 16777215
                
        If intCount - intCounter + 1 = 25 And strMonth = "December" Then tmpText = Chr(13) + Chr(10) + "Christmas Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 26 And strMonth = "December" Then tmpText = Chr(13) + Chr(10) + "Boxing Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 1 And strMonth = "January" Then tmpText = Chr(13) + Chr(10) + "New Years Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 15 And strMonth = "January" Then tmpText = Chr(13) + Chr(10) + "Martin Luther King's Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 14 And strMonth = "February" Then tmpText = Chr(13) + Chr(10) + "Valentine's Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 19 And strMonth = "February" Then tmpText = Chr(13) + Chr(10) + "President's Day": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 20 And strMonth = "February" Then tmpText = Chr(13) + Chr(10) + "NO SCHOOL": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 14 And strMonth = "March" Then tmpText = Chr(13) + Chr(10) + "NO SCHOOL": Me("text" & intCount).BackColor = 16777215
        If intCount - intCounter + 1 = 28 And strMonth = "May" Then tmpText = Chr(13) + Chr(10) + "Memorial Day": Me("text" & intCount).BackColor = 16777215
        
        For i = 1 To events
            If intCount - intCounter + 1 = Int(Format(eventDate(i), "d")) _
            And strMonth = Format(eventDate(i), "mmmm") _
            And intYear = Int(Format(eventDate(i), "yyyy")) _
            Then
                tmpText = tmpText + Chr(13) + Chr(10) + eventName(i)
                tmpText2 = eventID(i) + " or [ID] = " + tmpText2
                Me("text" & intCount).BackColor = 16777215 '16763080 14474460
            End If
        Next i
        If Len(tmpText2) > 0 Then tmpText2 = Left(tmpText2, Len(tmpText2) - 11)
        Me("text" & intCount).Caption = tmpText
        Me("id" & intCount).Caption = tmpText2
        
        
        
        If Int(Format(Date, "d")) = intCount - intCounter + 1 And strMonth = Format(Date, "mmmm") And intYear = Format(Date, "yyyy") Then
            Me("text" & intCount).BackColor = 8454143 '16765650
        End If
        
                
        
        intCount = intCount + 1
        strTemp = ""
    Loop Until intCount = intCounter + intDayCount
   
End Sub

the report:

Private Sub Report_Activate()
'This checks the main form to see if a text box is visible (ie; the 1st falls on a tuesday vs a Monday)
'If it is visible on the main form, then it will be on this report as well.....

If [Forms]![frmScheduled]![text1].Visible = True Then
    Me.text1.Visible = True
    Me.Label1.Visible = True
Else
    Me.text1.Visible = False
    Me.Label1.Visible = False
End If

If [Forms]![frmScheduled]![text2].Visible = True Then
    Me.text2.Visible = True
    Me.Label2.Visible = True
Else
    Me.text2.Visible = False
    Me.Label2.Visible = False
End If

If [Forms]![frmScheduled]![text3].Visible = True Then
    Me.text3.Visible = True
    Me.Label3.Visible = True
Else
    Me.text3.Visible = False
    Me.Label3.Visible = False
End If

If [Forms]![frmScheduled]![text4].Visible = True Then
    Me.text4.Visible = True
    Me.Label4.Visible = True
Else
    Me.text4.Visible = False
    Me.Label4.Visible = False
End If

If [Forms]![frmScheduled]![text5].Visible = True Then
    Me.text5.Visible = True
    Me.Label5.Visible = True
Else
    Me.text5.Visible = False
    Me.Label5.Visible = False
End If

If [Forms]![frmScheduled]![text6].Visible = True Then
    Me.text6.Visible = True
    Me.Label6.Visible = True
Else
    Me.text6.Visible = False
    Me.Label6.Visible = False
End If

If [Forms]![frmScheduled]![text29].Visible = True Then
    Me.text29.Visible = True
    Me.Label29.Visible = True
Else
    Me.text29.Visible = False
    Me.Label29.Visible = False
End If

If [Forms]![frmScheduled]![text30].Visible = True Then
    Me.text30.Visible = True
    Me.Label30.Visible = True
Else
    Me.text30.Visible = False
    Me.Label30.Visible = False
End If

If [Forms]![frmScheduled]![text31].Visible = True Then
    Me.text31.Visible = True
    Me.Label31.Visible = True
Else
    Me.text31.Visible = False
    Me.Label31.Visible = False
End If

If [Forms]![frmScheduled]![text32].Visible = True Then
    Me.text32.Visible = True
    Me.Label32.Visible = True
Else
    Me.text32.Visible = False
    Me.Label32.Visible = False
End If

If [Forms]![frmScheduled]![text33].Visible = True Then
    Me.text33.Visible = True
    Me.Label33.Visible = True
Else
    Me.text33.Visible = False
    Me.Label33.Visible = False
End If

If [Forms]![frmScheduled]![text34].Visible = True Then
    Me.text34.Visible = True
    Me.Label34.Visible = True
Else
    Me.text34.Visible = False
    Me.Label34.Visible = False
End If

If [Forms]![frmScheduled]![text35].Visible = True Then
    Me.text35.Visible = True
    Me.Label35.Visible = True
Else
    Me.text35.Visible = False
    Me.Label35.Visible = False
End If

If [Forms]![frmScheduled]![text36].Visible = True Then
    Me.text36.Visible = True
    Me.Label36.Visible = True
Else
    Me.text36.Visible = False
    Me.Label36.Visible = False
End If

If [Forms]![frmScheduled]![text37].Visible = True Then
    Me.text37.Visible = True
    Me.Label37.Visible = True
Else
    Me.text37.Visible = False
    Me.Label37.Visible = False
End If


'This snags the populated date box value for each day from the main form

Me.Label1 = [Forms]![frmScheduled]![Label1].Caption
Me.Label2 = [Forms]![frmScheduled]![Label2].Caption
Me.Label3 = [Forms]![frmScheduled]![Label3].Caption
Me.Label4 = [Forms]![frmScheduled]![Label4].Caption
Me.Label5 = [Forms]![frmScheduled]![Label5].Caption
Me.Label6 = [Forms]![frmScheduled]![Label6].Caption
Me.Label7 = [Forms]![frmScheduled]![Label7].Caption
Me.Label8 = [Forms]![frmScheduled]![Label8].Caption
Me.Label9 = [Forms]![frmScheduled]![Label9].Caption
Me.Label10 = [Forms]![frmScheduled]![Label10].Caption
Me.Label11 = [Forms]![frmScheduled]![Label11].Caption
Me.Label12 = [Forms]![frmScheduled]![Label12].Caption
Me.Label13 = [Forms]![frmScheduled]![Label13].Caption
Me.Label14 = [Forms]![frmScheduled]![Label14].Caption
Me.Label15 = [Forms]![frmScheduled]![Label15].Caption
Me.Label16 = [Forms]![frmScheduled]![Label16].Caption
Me.Label17 = [Forms]![frmScheduled]![Label17].Caption
Me.Label18 = [Forms]![frmScheduled]![Label18].Caption
Me.Label19 = [Forms]![frmScheduled]![Label19].Caption
Me.Label20 = [Forms]![frmScheduled]![Label20].Caption
Me.Label21 = [Forms]![frmScheduled]![Label21].Caption
Me.Label22 = [Forms]![frmScheduled]![Label22].Caption
Me.Label23 = [Forms]![frmScheduled]![Label23].Caption
Me.Label24 = [Forms]![frmScheduled]![Label24].Caption
Me.Label25 = [Forms]![frmScheduled]![Label25].Caption
Me.Label26 = [Forms]![frmScheduled]![Label26].Caption
Me.Label27 = [Forms]![frmScheduled]![Label27].Caption
Me.Label28 = [Forms]![frmScheduled]![Label28].Caption
Me.Label29 = [Forms]![frmScheduled]![Label29].Caption
Me.Label30 = [Forms]![frmScheduled]![Label30].Caption
Me.Label31 = [Forms]![frmScheduled]![Label31].Caption
Me.Label32 = [Forms]![frmScheduled]![Label32].Caption
Me.Label33 = [Forms]![frmScheduled]![Label33].Caption
Me.Label34 = [Forms]![frmScheduled]![Label34].Caption
Me.Label35 = [Forms]![frmScheduled]![Label35].Caption
Me.Label36 = [Forms]![frmScheduled]![Label36].Caption
Me.Label37 = [Forms]![frmScheduled]![Label37].Caption




'This snags the populated text box value for each day from the main form

Me.text1 = [Forms]![frmScheduled]![text1].Caption
Me.text2 = [Forms]![frmScheduled]![text2].Caption
Me.text3 = [Forms]![frmScheduled]![text3].Caption
Me.text4 = [Forms]![frmScheduled]![text4].Caption
Me.text5 = [Forms]![frmScheduled]![text5].Caption
Me.text6 = [Forms]![frmScheduled]![text6].Caption
Me.text7 = [Forms]![frmScheduled]![text7].Caption
Me.text8 = [Forms]![frmScheduled]![text8].Caption
Me.text9 = [Forms]![frmScheduled]![text9].Caption
Me.text10 = [Forms]![frmScheduled]![text10].Caption
Me.text11 = [Forms]![frmScheduled]![text11].Caption
Me.text12 = [Forms]![frmScheduled]![text12].Caption
Me.text13 = [Forms]![frmScheduled]![text13].Caption
Me.text14 = [Forms]![frmScheduled]![text14].Caption
Me.text15 = [Forms]![frmScheduled]![text15].Caption
Me.text16 = [Forms]![frmScheduled]![text16].Caption
Me.text17 = [Forms]![frmScheduled]![text17].Caption
Me.text18 = [Forms]![frmScheduled]![text18].Caption
Me.text19 = [Forms]![frmScheduled]![text19].Caption
Me.text20 = [Forms]![frmScheduled]![text20].Caption
Me.text21 = [Forms]![frmScheduled]![text21].Caption
Me.text22 = [Forms]![frmScheduled]![text22].Caption
Me.text23 = [Forms]![frmScheduled]![text23].Caption
Me.text24 = [Forms]![frmScheduled]![text24].Caption
Me.text25 = [Forms]![frmScheduled]![text25].Caption
Me.text26 = [Forms]![frmScheduled]![text26].Caption
Me.text27 = [Forms]![frmScheduled]![text27].Caption
Me.text28 = [Forms]![frmScheduled]![text28].Caption
Me.text29 = [Forms]![frmScheduled]![text29].Caption
Me.text30 = [Forms]![frmScheduled]![text30].Caption
Me.text31 = [Forms]![frmScheduled]![text31].Caption
Me.text32 = [Forms]![frmScheduled]![text32].Caption
Me.text33 = [Forms]![frmScheduled]![text33].Caption
Me.text34 = [Forms]![frmScheduled]![text34].Caption
Me.text35 = [Forms]![frmScheduled]![text35].Caption
Me.text36 = [Forms]![frmScheduled]![text36].Caption
Me.text37 = [Forms]![frmScheduled]![text37].Caption

End Sub

I tried a few options on the Report to apply sorting but am not getting anywhere. Most likely due to all the VBA in this calendar form and report.

 

[dhookom]

I don't know what this has to do with sorting your report.
Also, I'm not sure about this SQL

("SELECT tblSchedule.* FROM tblSchedule WHERE (((tblSchedule.ScheduleDate) Is Null) And ((tblSchedule.ScheduleDate) Is Not Null)) ORDER BY tblSchedule.ScheduleDate, tblSchedule.ScheduleDate;",

If you have a schedule table that you want to display in a report, you might want to look at the sample calendar reports at

http://www.access.hookom.net/Samples.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[techsup07]

gawd! i'm an idiot!

took just a sec to update that which was the easiest thing and right in front of me!

it takes someone else to point out the obvious!

i can't believe I've spent this time and everyone else's for being shortsighted!

it seems I always end up doing it the hard way when all the way the simplest way was right there.


thanks for opening my eyes!