Results 1 to 8 of 8
  1. #1
    RobMS is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3

    VBA Detail Format for report is not working

    Hello DBdeveloper community,

    I'm new todevelopment and I've been developing a new database since February for my teamto use. I've been pretty successful (it may not be pretty, but it works so far)searching google whenever i get stuck and fixing any issues I have, butthis one is stumping me hard. I borrowed a code from a previous databasebecause they like a format for a current report, but my tables and queries areset up a little different than the previous database (which i think wasdeveloped about 10 years ago). I've ran through the entire code andreplaced everything (while adding a few criteria to make sure it reads right),but I can't seem to get the data to show on the report. Please help! See belowfor my code and a picture of the report in design mode.

    Code:
    
    
    Code:
    OptionCompare Database
    
    
    
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        
        Dim i, Numdays, ApptTotalTime, Color As Variant
        Dim strDay, strDaysTotal, strSwingsTotal, strMidsTotal AsString
        Dim dtDate, dtDateStart, dtDateStop, dtLastShift As Date
        Dim blnWDDay As Boolean
        Dim strAppt, strAppt1, strAppt2, strAppt3, strAppt4,strAppt5 As String
        Dim strLV, strLV1, strLV2, strLV3, strLV4, strLV5 As String
        Dim strTDY, strTDY1, strTDY2, strTDY3, strTDY4 As String
        Dim strWD, strWD1, strWD2, strWD3, strWD4, strWD5 As String
        Dim strShift, strShift1, strShift2, strShift3, strShift4 AsString
        Dim qryLV, qryTDY, qryAppt, qryWD, qryShift As QueryDef
        Dim rstLV, rstTDY, rstAppt, rstWD, rstShift, rstHoliday AsDAO.Recordset
        Dim dbs As Database
        
        Me!Count = Me!Count + 1
        Numdays = MonthLength
        
        Set dbs = CurrentDb
        
        dtDateStart = Format(DateSerial([Forms]![ReportsF]![YrSel],[Forms]![ReportsF]![MoSel], 1), "m/d/yyyy")
        dtDateStop = Format(DateSerial([Forms]![ReportsF]![YrSel],[Forms]![ReportsF]![MoSel], Numdays), "m/d/yyyy")
        
      'Defines the leave query
        strLV1 = "SELECT MPersStatusQ.USERID,MPersStatusQ.PSStart, MPersStatusQ.PSEnd, MPersStatusQ.MStat FROM MPersStatusQWHERE (((MPersStatusQ.USERID)='"
        strLV2 = "') AND ((MPersStatusQ.PSStart)<=#"
        strLV3 = "#) AND ((MPersStatusQ.PSEnd)>=#"
        strLV4 = "#) AND ((MPersStatusQ.MStat)='L'"
        strLV5 = ")) ORDER BY MPersStatusQ.PSStart"
        strLV = strLV1 & Me!USERID & strLV2 & dtDateStop& strLV3 & dtDateStart & strLV4 & strLV5
        Set qryLV = dbs.CreateQueryDef("", strLV)
        Set rstLV = qryLV.OpenRecordset
       
        'Defines the TDY query
        strTDY1 = "SELECT MMMAQ.USERID, MMMAQ.MStart,MMMAQ.MEnd FROM MMMAQ WHERE (((MMMAQ.USERID)='"
        strTDY2 = "') AND ((MMMAQ.MStart)<=#"
        strTDY3 = "#) AND ((MMMAQ.MEnd)>=#"
        strTDY4 = "#)) ORDER BY MMMAQ.MStart"
        strTDY = strTDY1 & Me!USERID & strTDY2 &dtDateStop & strTDY3 & dtDateStart & strTDY4
        Set qryTDY = dbs.CreateQueryDef("", strTDY)
        Set rstTDY = qryTDY.OpenRecordset
        
        'Defines the Appointment query
        strAppt1 = "SELECT MPersStatusQ.USERID,MPersStatusQ.PSStart, MPersStatusQ.PSEnd, MPersStatusQ.Notes, MPersStatusQ.[AllDay Event], MPersStatusQ.MStat FROM MPersStatusQ WHERE(((MPersStatusQ.USERID)='"
        strAppt2 = "') AND ((MPersStatusQ.PSStart)<=#"
        strAppt3 = "#) AND ((MPersStatusQ.PSEnd)>=#"
        strAppt4 = "#) AND ((MPersStatusQ.MStat)='A'"
        strAppt5 = "))ORDER BY MStatusQ.PSStart"
        strAppt = strAppt1 & Me!USERID & strAppt2 &dtDateStop & strAppt3 & dtDateStart & strAppt4 & strAppt5
        Set qryAppt = dbs.CreateQueryDef("", strAppt)
        Set rstAppt = qryAppt.OpenRecordset
        'Defines the Weekend Duty query
        strWD1 = "SELECT MPersShiftTabQ.USERID,MPersShiftTabQ.PSHStart, MPersShiftTab.ShiftEndDt, MPersShiftTab.ShftID FROMMPersShiftTabQ WHERE (((MPersShiftTabQ.USERID)='"
        strWD2 = "') AND ((MPersShiftTabQ.PSHStart)<=#"
        strWD3 = "#) AND((MPersShiftTabQ.ShiftEndDt)>=#"
        strWD4 = "#) AND ((MPersShiftTabQ.ShftID)='W'"
        strWD5 = "))ORDER BY MPersShiftTabQ.PSHStart"
        strWD = strWD1 & Me!USERID & strWD2 & dtDateStop& strWD3 & dtDateStart & strWD4 & strWD5
        Set qryWD = dbs.CreateQueryDef("", strWD)
        Set rstWD = qryWD.OpenRecordset
        'Defines the Shift query
        strShift1 = "SELECT MPersShiftTabQ.USERID,MPersShiftTabQ.PSHStart, MPersShiftTabQ.ShiftSel, MPersShiftTabQ.ShiftID FROMMPersShiftTabQ WHERE (((MPersShiftTabQ.USERID)='"
        strShift2 = "') AND((MPersShiftTabQ.PSHStart)<=#"
        strShift3 = "#) AND((MPersShiftTabQ.ShiftID)<>'8'"
        strShift4 = ")) ORDER BY MPersShiftTabQ.PSHStart"
        strShift = strShift1 & Me!USERID & strShift2 &dtDateStop & strShift3 & strShift4 & strShift5
        Set qryShift = dbs.CreateQueryDef("", strShift)
        Set rstShift = qryShift.OpenRecordset
        
        Set rstHoliday = Holiday(Numdays, dbs) 'Uses the Holidayfunction at the bottom of this page
        'Cycles through each day of the month, checking the variousqueries to see if that person is on leave, TDY, etc.
        For i = 1 To Numdays
            dtDate =DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], i)
            strDay = "Day" & i
            strDaysTotal = "Day" &i & "DaysTotal"
            strSwingsTotal = "Day"& i & "SwingsTotal"
            strMidsTotal = "Day" &i & "MidsTotal"
            Me(strDay) = ""
            Me(strDay).ForeColor = 0
                   
            Select Case DatePart("w",DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], i)) 'Makesthe weekends grey
                Case 1, 7'Weekend
                   Color = 12632256 ' Colors the square Grey for Weekends and Holidays
                   blnWDDay = True 'Marks them as a weekend
                Case Else
                   Color = 16777215 'Otherwise, the square is White
                   blnWDDay = False
            End Select
            
            With rstHoliday 'Makes the holidaysgrey
                If.RecordCount <> 0 Then
                   .MoveFirst
                   Do While Not .EOF
                       If .Fields(0) = dtDate Then
                           Color = 12632256 'Colors the square Grey
                           blnWDDay = True 'Marks it as a weekend day
                       End If
                       .MoveNext
                   Loop
                   .MoveFirst
                End If
            End With
            
            If blnWDDay Then 'Doesn't countpeople on the weekends or holidays
               Me(strDaysTotal) = ""
               Me(strSwingsTotal) = ""
               Me(strMidsTotal) = ""
            End If
            
            Me(strDay).BackColor = Color
            With rstLV 'If the person is onleave, marks the day with an L and colors it Light Blue
                If.RecordCount <> 0 Then
                   .MoveFirst
                   Do While (Not .EOF) And (Me(strDay) <> "L") 'If it's notalready marked with an L
                       If .Fields(1) <= dtDate And .Fields(2) >= dtDate Then
                           Me(strDay) = "L"
                           Me(strDay).BackColor = 16764057 'Colors the square Light Blue
                       End If
                       .MoveNext
                   Loop
                   .MoveFirst
                End If
            End With
            
            If Me(strDay) = "" Then'If they aren't on leave
                With rstTDY'If they are TDY, marks the day with a T and colors it Light Green
                   If .RecordCount <> 0 Then
                       .MoveFirst
                       Do While (Not .EOF) And (Me(strDay) <> "T") 'If it's notalready marked with a T
                           If .Fields(1) <= dtDate And .Fields(2) >= dtDate Then
                               Me(strDay) = "T"
                               Me(strDay).BackColor = 13434828 'Colors the square Light Green
                           End If
                           .MoveNext
                       Loop
                       .MoveFirst
                   End If
                End With
            End If
            
            If Me(strDay) = "" Then'If they aren't on leave or TDY
                With rstAppt'If they have an appointment longer than 1 hour or All Day, marks the day withan A and colors it Pink
                   If .RecordCount <> 0 Then
                       ApptTotalTime = 0
                       .MoveFirst
                       Do While (Not .EOF) And (Me(strDay) <> "A")
                           If .Fields(1) <= dtDate And .Fields(2) >= dtDate Then
                               If .Fields(2) > .Fields(1) Then 'If appt is more than 1 day
                                   Me(strDay) = "A"
                                   Me(strDay).BackColor = 13408767 ' Pink
                               Else
                                   If .Fields(6) Then 'If All Day is checked on appt form (Appt is all day)
                                       Me(strDay) = "A"
                                       Me(strDay).BackColor = 13408767 'Pink
                                   Else
                                       If (Not IsNull(.Fields(4))) And (Not IsNull(.Fields(3))) Then 'If Start andStop times aren't blank
                                           If ((.Fields(4) - .Fields(3)) * 24) >= 1 Then 'Checks to see if appointmentis longer than 1 hour
                                               Me(strDay) = "A"
                                               Me(strDay).BackColor = 13408767 'Pink
                                           Else
                                               ApptTotalTime = ApptTotalTime + ((.Fields(4) - .Fields(3)) * 24)
                                           End If
                                       End If
                                   End If
                               End If
                           End If
                           .MoveNext
                       Loop
                       .MoveFirst
                   End If
                End With
                
            End If
            
            If (Me(strDay) = "") And(blnWDDay) Then 'If it's a weekend or holiday, and they aren't on leave or TDY
                With rstWD'If the person has weekend duty, marks the day with a W and colors it Yellow
                   If .RecordCount <> 0 Then
                       .MoveFirst
                       Do While (Not .EOF) And (Me(strDay) <> "W")
                           If .Fields(1) <= dtDate And .Fields(2) >= dtDate Then
                               Me(strDay) = "W"
                               Me(strDay).BackColor = 10092543 'Yellow
                           End If
                           .MoveNext
                       Loop
                       .MoveFirst
                   End If
                End With
            End If
            
            If (Me(strDay) = "") And(blnWDDay = False) Then 'If not on leave, TDY, there's no appointment, and itisn't a weekend or holiday
                WithrstShift 'Checks what shift they are on
                   If .RecordCount <> 0 Then
                       .MoveFirst
                       dtLastShift = .Fields(1) 'Start Date
                       ShiftID = .Fields(2) 'Which shift
                       Do While (Not .EOF)
                           If (.Fields(1) > dtLastShift) And (.Fields(1) <= dtDate) Then
                           ShiftID = .Fields(2)
                           End If
                           .MoveNext
                       Loop
                       Select Case ShiftID
                           Case 1 'Days
                               Me(strDay) = "D"
                               Me(strDay).ForeColor = 8388608 'Colors the "D" Dark Blue
                               Me(strDaysTotal) = Me(strDaysTotal) + 1 'Adds one to the number of people onDay Shift for that day
                           Case 2 'Swings
                               Me(strDay) = "S"
                               Me(strDay).ForeColor = 8421376 'Colors the "S" Teal
                               Me(strSwingsTotal) = Me(strSwingsTotal) + 1 'Adds one to the number of peopleon Swings for that day
                           Case 3 'Mids
                               Me(strDay) = "M"
                               Me(strDay).ForeColor = 128 'Colors the "M" Dark Red
                               Me(strMidsTotal) = Me(strMidsTotal) + 1 'Adds one to the number of people onMids for that day
                           Case 4 'Nights (12 hr shift)
                               Me(strDay) = "N"
                               Me(strDay).ForeColor = 26367 'Colors the "N" Orange
                       End Select
                       .MoveFirst
                   End If
                End With
            End If
        Next i
        rstHoliday.Close
        rstShift.Close
        qryShift.Close
        rstAppt.Close
        qryAppt.Close
        rstWD.Close
        qryWD.Close
        rstTDY.Close
        qryTDY.Close
        rstLV.Close
        qryLV.Close
     
    End Sub
    
    
    Private SubGroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
        Dim i, Numdays, Color As Variant
        Dim strDay, strDayLabel, strDayDOW As String
        Dim strDaysTotal, strSwingsTotal, strMidsTotal As String
        Dim dtStartDate, dtStopDate, dtDate As Date
        Dim dbs As Database
        Dim rstHoliday As DAO.Recordset
        
        Me!Count = 0
        Numdays = MonthLength
        
        i = Numdays + 1
        Do While i <= 31
            strDayLabel = "Day" &i & "Label"
            strDayDOW = "Day" & i& "DOW"
            strDay = "Day" & i
            strDaysTotal = "Day" &i & "DaysTotal"
            strSwingsTotal = "Day"& i & "SwingsTotal"
            strMidsTotal = "Day" &i & "MidsTotal"
            Me(strDayLabel).Visible = False
            Me(strDayDOW).Visible = False
            Me(strDay).Visible = False
            Me(strDaysTotal).Visible = False
            Me(strSwingsTotal).Visible = False
            Me(strMidsTotal).Visible = False
            i = i + 1
        Loop
        
        Set dbs = CurrentDb
        Set rstHoliday = Holiday(Numdays, dbs)
    
    
       For i = 1 To Numdays
            dtDate =DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], i)
            strDayLabel = "Day" &i & "Label"
            strDayDOW = "Day" & i& "DOW"
            strDaysTotal = "Day" &i & "DaysTotal"
            strSwingsTotal = "Day"& i & "SwingsTotal"
            strMidsTotal = "Day" &i & "MidsTotal"
            Me(strDaysTotal) = 0
            Me(strSwingsTotal) = 0
            Me(strMidsTotal) = 0
            
            Select Case DatePart("w",DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], i))
                Case 1
                   Color = 12632256 ' Grey for Weekends and Holidays
                   Me(strDayDOW) = "Su"
                Case 2
                   Color = 16777215 'White for all other days
                   Me(strDayDOW) = "M"
                Case 3
                   Color = 16777215
                   Me(strDayDOW) = "Tu"
                Case 4
                   Color = 16777215
                   Me(strDayDOW) = "W"
                Case 5
                   Color = 16777215
                   Me(strDayDOW) = "Th"
                Case 6
                   Color = 16777215
                   Me(strDayDOW) = "F"
                Case 7
                   Color = 12632256
                   Me(strDayDOW) = "Sa"
            End Select
                  
            With rstHoliday
                If.RecordCount <> 0 Then
                   .MoveFirst
                   Do While Not .EOF
                       If .Fields(0) = dtDate Then
                           Color = 12632256 'Grey
                       End If
                       .MoveNext
                   Loop
                   .MoveFirst
                End If
            End With
            
            Me(strDayLabel).BackColor = Color
            Me(strDayDOW).BackColor = Color
            Me(strDaysTotal).BackColor = Color
            Me(strSwingsTotal).BackColor = Color
            Me(strMidsTotal).BackColor = Color
                           
        Next i
        rstHoliday.Close
    End Sub
    
    
    FunctionMonthLength()
        Select Case Forms!ReportsF!MoSel
            Case 1, 3, 5, 7, 8, 10, 12 ' Monthswith 31 Days
                MonthLength= 31
            Case 4, 6, 9, 11 ' Months with 30Days
                MonthLength= 30
            Case Else 'February
                IfDatePart("m", DateAdd("d", 28,DateSerial(Forms!ReportsF!YrSel, Forms!ReportsF!MoSel, 1))) = 2 Then 'Checksfor leap year
                   MonthLength = 29
                  Else: MonthLength = 28
                End If
        End Select
    End Function
    
    
    FunctionHoliday(Numdays As Variant, dbs As Database) As DAO.Recordset
        Dim strHoliday, strHoliday1, strHoliday2, strHoliday3,strHoliday4 As String
        Dim dtStartDate, dtStopDate, dtDate As Date
        Dim qryHoliday As QueryDef
        
        dtDateStart = Forms!ReportsF!BOMDt
        dtDateStop = Forms!ReportsF!EOMDt
        strHoliday1 = "SELECT MPersStatusQ.MStat,MPersStatusQ.HolDt FROM MPersStatusQ WHERE (((MPersStatusQ.HolDt) Between#"
        strHoliday2 = "# And #"
        strHoliday3 = "#) AND ((MPersStatusQ.MStat)='H'"
        strHoliday4 = "))ORDER By MPersStatusQ.HolDt"
        strHoliday = strHoliday1 & dtDateStart & strHoliday2& dtDateStop & strHoliday3 & strHoliday4
        Set qryHoliday = dbs.CreateQueryDef("",strHoliday)
        Set Holiday = qryHoliday.OpenRecordset
        qryHoliday.Close
    End Function
    
    Private Sub Report_NoData(Cancel As Integer)
        MsgBox ("There was no one selected to work for theperiod you selected")
        DoCmd.CancelEvent
    End Sub
    
    


    Attached Thumbnails Attached Thumbnails RosterReport.JPG  

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't know if the following will help with this issue, but you are doing several things wrong. When you multi declare on one line (Dim) you must explicitly Type every variable otherwise only the last is Typed (and I don't mean keyboarding). So in

    Dim strLV, strLV1, strLV2, strLV3, strLV4, strLV5 As String

    only strLV5 is a string, the rest are Variants. It must be as
    Dim strLV As String, strLV2 As String....

    otherwise, all others are of type Variant.

    You should turn on Option Explicit (VB editor, Options>Require Variable Declaration). for future modules and add that to all existing ones, compiling the code and fixing errors as you go. For the life of me, I cannot understand why the default is to be unchecked.

    Do not use Me! use Me. In fact, learn the difference between them and know that any reference preceded by ! will not be checked on compile. So if the control is txtName and you use Me!txtNme a compile won't catch it. It will fail only at run time.

    You say you get no records? Then after fixing the above, put a break on the first line and step through the code and check your variable values as you go. Note that when you do this, a yellow highlighted line has not been executed so you must step off of it to see what happens to them in that line (usually F8).

    It's odd that you have chosen this event for setting so many control values. Do you know how many times this event will run when a report is opening? Why can you not just use the report recordsource property instead of all that code?

    I stopped early in reviewing the code because there is so much of it and I don't understand the placement of it or the need for it.
    Last edited by Micron; 11-13-2020 at 01:12 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Afterthoughts:
    You didn't say how you are opening the report. IIRC, the Format event doesn't run for Report View. That could be why your form has no data since it seems you're not taking advantage of the report recordsource property.

    After a bit more code review I have to wonder if what you ought to be doing is setting the report recordsource and using conditional formatting. Not sure, but I think the condition limit is 3 for design - can't recall if vba provides one or two more.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    RobMS is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3
    To be honest, I haven't done a whole lot of vb in this database, so I'm not sure what is or isn't more appropriate. I'm learning as I go. I basically used a code in our previous database because it worked. I somewhat understand what is supposed to happen when I am editing the code, but I am far from being an expert. I am on a deadline at the moment, so I would rather not try and start from scratch, unless I knew it would be easy. As for not using the query recordset, it's a bit tricky. I created this database from multiple SharePoint tables and part of the main problem I am having while developing this is how the sharepoint creator used two different types of personnel records (a roster based off a key ID and a name selector from a global e-mail listing). If the sharepoint creator had used one or the other, my life would have been so much easier. But, now i have to create wicked queries to get the information to match up. Going back now to make it easier would take too much time..

    I did make all your suggested changes and it helped to the point that I am actually getting errors now. It's currently getting stuck on the Holiday function (Run time error 3061, too few parameters. expected 1) and highlights the Set Holiday line. I don't doubt it is frustrating to read this code and try and figure out the issues, I feel ya--been frustrating me for days now.. but, you have definitely helped already. Have any more tips?

    To answer your second set of questions, the recordset for the report is set to display certain members base on a section selection. It has the members name, their USERID, skill level and a few other things, but the code runs on the format of the detail portion of the report.

    Code:
        Function Holiday(Numdays As Variant, dbs As Database) As DAO.Recordset
        Dim strHoliday As String
        Dim strHoliday1 As String
        Dim strHoliday2 As String
        Dim strHoliday3 As String
        Dim strHoliday4 As String
        Dim dtStartDate As Date
        Dim dtStopDate As Date
        Dim dtDate As Date
        Dim qryHoliday As QueryDef
        
        Set dbs = CurrentDb
            
        dtDateStart = Format(DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], 1), "m/d/yyyy")
        dtDateStop = Format(DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], 30), "m/d/yyyy")
        strHoliday1 = "SELECT MPersStatusQ.HolDt, MPersStatusQ.USERID FROM MPersStatusQ WHERE (((MPersStatusQ.HolDt) Between #"
        strHoliday2 = "# AND #"
        strHoliday3 = "#) AND ((MPersStatusQ.USERID)=Null"
        strHoliday4 = "))ORDER By MPersStatusQ.HolDt"
        strHoliday = strHoliday1 & dtDateStart & strHoliday2 & dtDateStop & strHoliday3 & strHoliday4
        Set qryHoliday = dbs.CreateQueryDef("", strHoliday)
        Set Holiday = qryHoliday.OpenRecordset
        qryHoliday.Close
    End Function
    Quote Originally Posted by Micron View Post
    Don't know if the following will help with this issue, but you are doing several things wrong. When you multi declare on one line (Dim) you must explicitly Type every variable otherwise only the last is Typed (and I don't mean keyboarding). So in

    Dim strLV, strLV1, strLV2, strLV3, strLV4, strLV5 As String

    only strLV5 is a string, the rest are Variants. It must be as
    Dim strLV As String, strLV2 As String....

    otherwise, all others are of type Variant.

    You should turn on Option Explicit (VB editor, Options>Require Variable Declaration). for future modules and add that to all existing ones, compiling the code and fixing errors as you go. For the life of me, I cannot understand why the default is to be unchecked.

    Do not use Me! use Me. In fact, learn the difference between them and know that any reference preceded by ! will not be checked on compile. So if the control is txtName and you use Me!txtNme a compile won't catch it. It will fail only at run time.

    You say you get no records? Then after fixing the above, put a break on the first line and step through the code and check your variable values as you go. Note that when you do this, a yellow highlighted line has not been executed so you must step off of it to see what happens to them in that line (usually F8).

    It's odd that you have chosen this event for setting so many control values. Do you know how many times this event will run when a report is opening? Why can you not just use the report recordsource property instead of all that code?

    I stopped early in reviewing the code because there is so much of it and I don't understand the placement of it or the need for it.
    Attached Thumbnails Attached Thumbnails ReportView.JPG  
    Last edited by RobMS; 11-13-2020 at 03:55 PM. Reason: Updated to answer second set of questions

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    3061 is most often due to something being spelled wrong. The odd time, it is due to (to put it simply) the query side not being able to reconcile what the vba side is saying. If your case is about spelling (carefully check your references such as a form control name, field name, etc - anything you're trying to provide as part of the failing sql in that line) then it is easier and quicker than looking for something that's not a problem. I'll take a closer look while you do that.

    Also try putting a break point on your problem line and add this before Set qryHoliday
    Debug.Print strHoliday
    When your code stops executing at the break, look at the sql in the immediate window (of the vb editor). If it looks OK, copy it to a new query in sql view and switch to datasheet view. If it runs, it's not a spelling error (at least this time). If it balks, it usually highlights the offending portion, or at least raises an error that might provide clues.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That code could be simplified as (perhaps) below. However, I would probably also introduce error handling as you have none. When a procedure in a stack raises an error, Access looks "upstream" in the stack for an error handler. If it finds one, the resulting message may or may not make sense. If it finds none, you'll get the standard message and result. However, I wanted to compare what you have to a simpler version so I left error handling out of it.
    Code:
    Function Holiday(Numdays As Variant) As DAO.Recordset
    
    Dim strHoliday As String
    Dim dtStartDate As Date, dtStopDate As Date, dtDate As Date
    Dim rs as DAO.Recordset '<- could eliminate this but some people object to condensing code too much
    
    Set dbs = CurrentDb
            
    dtDateStart = Format(DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], 1), "m/d/yyyy")
    dtDateStop = Format(DateSerial([Forms]![ReportsF]![YrSel], [Forms]![ReportsF]![MoSel], 30), "m/d/yyyy")
    strHoliday = "SELECT MPersStatusQ.HolDt, MPersStatusQ.USERID FROM MPersStatusQ WHERE "
    strHoliday = strHoliday & "(((MPersStatusQ.HolDt) Between #" & dtDateStart & "# AND #"
    strHoliday = strHoliday & dtDateStop & "#) AND ((MPersStatusQ.USERID)=Null )) ORDER By MPersStatusQ.HolDt"
    
    Set rs = dbs.OpenRecordset (strHoliday)
    Set Holiday = rs 
    
    Set dbs = Nothing
    Set rs = Nothing
    
    End Function
    Comments:
    - what's with formatting and function for the dates? Don't the form controls pass valid date values to the function?
    - why do you need a temporary qdef when you can just open a recordset using your sql?
    - you should specifically type db and rs objects as either DAO or ADO
    - I would not pass a whole database to a function. Every object you create takes up memory space and should be destroyed at the end. Besides, you don't need to.
    - I use multiple variables to create sql strings but only when I need dynamic portions; e.g. strSql & strCriteria & strOrderBy. Your way is not wrong, just lengthier and perhaps slightly more difficult to manage or troubleshoot.

    To eliminate the rs variable and its handling one could use
    Set Holiday = dbs.OpenRecordset (strHoliday)

    BTW - that is air code (from the space between my ears) and might have a problem somewhere that I can't test for.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    RobMS is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3
    So, I made all your suggested changes, but it's still coming up with the too few parameters error. I have a calendar I'm trying to create based off the previous database reports/queries. In the report in my new DB, I pull a query that is filtered to a small section in order to have display names. As shown in the first image, there are boxes for each day to display the current status of the member. There are multiple tables to pull from for the status of the member as seen in the first set of code. In addition to displaying their status, days with holidays will block off the day. The holidays come from the same table as the personnel status table but don't have a USERID. I just created a Query to pull just holiday dates and filtered it to USERID=Null and between the dates displayed on my form. Can I pull the query as it is or do I have to set up this weird code to add in the criteria as well? Can't I say, create a function that pulls the date from my query that is already filtered?

    UPDATE: I decided to try and pull directly from the table adding in appropriate criteria and it finally passed the function. Why does it give errors when using a filtered query? Is it not possible to create a query based off a query?

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can I pull the query as it is or do I have to set up this weird code to add in the criteria as well? Don't know what that means.
    Can't I say, create a function that pulls the date from my query that is already filtered? Yes

    UPDATE: Why does it give errors when using a filtered query? What errors? 3061?
    Is it not possible to create a query based off a query? Yes it is

    You have not commented on any of the suggestions made in post 5 or any outcomes of implementing them.
    If your current problem is still the error from post 4 why don't you post a zipped db copy and tell me/us what to do to replicate the issue? If you have implemented changes, then the current code doesn't resemble what you posted earlier so we're kinda blind.

    If you're saying you solved it to your satisfaction, then I guess that's good?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Format Function Not Working
    By gameemaster in forum Queries
    Replies: 3
    Last Post: 05-13-2017, 02:28 PM
  2. Replies: 1
    Last Post: 08-19-2016, 05:10 PM
  3. Can Shrink on Detail Section Not Working
    By khumbo in forum Reports
    Replies: 4
    Last Post: 06-26-2014, 09:30 AM
  4. Replies: 3
    Last Post: 01-29-2013, 04:34 AM
  5. Replies: 3
    Last Post: 03-12-2012, 01:23 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums