Run-time error by a blank record made by code


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 1 of 1

Thread: Run-time error by a blank record made by code

Hybrid View

  1. #1
    Join Date
    Jul 2005
    Posts
    1

    Run-time error by a blank record made by code

    I have a database with a problem in the code, but I can't see the problem. Any help would be much appreciated.

    Problem is when it runs through the MonthsYears section. I'm using this to print tasks, like the ones used in MS Outlook, for work. When running through the code it add's a new blank record at the end, this then messes up the DB.
    This is the code.


    Run-time error '2763':

    Code:
    
    Option Compare Database
    Option Explicit
    
    Dim o                   ' For testing purposes only
    Public i                ' Loop count
    Public ThisCount        ' Used for counting days
    Public MonYeaCount      ' Used for counting Months Years
    Public verCurrentPD     ' The day the report is printed
    Public verTmpPD         ' The tempory Print Date
    Public tmpDays          ' Tempory Number of days in a month
    Public verComb1         ' Tempory Month
    Public verComb2         ' Tempory 1st, 2nd, 3rd, ... ect
    Public verComb3         ' Tempory Day
    
    Private Sub Form_Timer()
      'DoCmd.SetWarnings True
      Form.TimerInterval = 0
      Dim dbs As Database, rst As Recordset
      Set dbs = CurrentDb
      Set rst = dbs.OpenRecordset("Tasks")
      prgBar.Max = rst.recordCount
      Me!labProg.Format = "00"
      DoCmd.RunSQL "UPDATE Tasks SET Tasks.PrintDate = [tasks].[start]"
      For i = 1 To rst.recordCount
        If JobID = 189 Then
          Debug.Print JobID
          o = 1
        End If
        SetupVer
        DoCmd.GoToRecord , , acNext
        prgBar.Value = prgBar.Value + 1
        labProg = Format((prgBar.Value / prgBar.Max) * 100, "0") & " %"
      Next i
      DoCmd.GoToRecord , , acLast
      SetupVer
      rst.Close
      Set dbs = Nothing
      'Forms!Print!prgPrinOk = 1
      'DoCmd.Close
    End Sub
    
    Private Sub SetupVer()
      verCurrentPD = (Forms!Print!txtPrintDate)
      If TaskActive = 0 Then Exit Sub
      If IsNull(PrintDate.Value) Then PrintDate = Start
      If PrintDate >= verCurrentPD Then Exit Sub
      ThisCount = 0
      Select Case Recurrence
        Case 1
          Days
        Case 2
          Weeks
        Case 3, 4
          Select Case Combo2
            Case "first"
              verComb2 = 1
            Case "second"
              verComb2 = 2
            Case "third"
              verComb2 = 3
            Case "fourth"
              verComb2 = 4
            Case "last"
              verComb2 = 5
          End Select
          Select Case Combo3
            Case "day"
              verComb3 = 8
            Case "weekday"
              verComb3 = 9
            Case "weekend day"
              verComb3 = 10
            Case "Monday"
              verComb3 = 1
            Case "Tuesday"
              verComb3 = 2
            Case "Wednesday"
              verComb3 = 3
            Case "Thursday"
              verComb3 = 4
            Case "Friday"
              verComb3 = 5
            Case "Saturday"
              verComb3 = 6
            Case "Sunday"
              verComb3 = 7
          End Select
          If Recurrence = 3 Then Months
          If Recurrence = 4 Then Years
      End Select
    End Sub
    
    Private Sub Months()
      MonYeaCount = 0
      Select Case Option1
        Case 1
          PrintDate = DateValue(Text1 & " " & (Month(PrintDate)) & " " & (Year(PrintDate)))
          Do While PrintDate < verCurrentPD
            PrintDate = DateAdd("m", Text2, PrintDate)
            If PrintDate >= verCurrentPD Then Exit Do
          Loop
        Case 2
          verTmpPD = DateValue("1" & " " & (Month(PrintDate)) & " " & (Year(PrintDate)))
          Do While verTmpPD < verCurrentPD
            verTmpPD = DateAdd("m", Text2, verTmpPD)
          Loop
          MonthsYears
      End Select
    End Sub
    
    Private Sub Years()
      MonYeaCount = 0
      Select Case Option1
        Case 1
          PrintDate = DateValue(Text1 & " " & Combo1 & " " & (Year(PrintDate)))
          Do While PrintDate < verCurrentPD
            PrintDate = DateAdd("yyyy", 1, PrintDate)
            If PrintDate >= verCurrentPD Then Exit Do
          Loop
        Case 2
          verTmpPD = DateValue("1" & " " & Combo1 & " " & (Year(PrintDate)))
          Do While verTmpPD < verCurrentPD
            verTmpPD = DateAdd("yyyy", 1, verTmpPD)
          Loop
          MonthsYears
      End Select
    End Sub
    
    Private Sub MonthsYears()
      ThisCount = 0
      Select Case Month(verTmpPD)
        Case 1, 3, 5, 7, 8, 10, 12          ' 31 days - Jan, Mar, May, Jul, Aug, Oct, Dec
          tmpDays = 31
        Case 4, 6, 9, 11                    ' 30 days - Apr, Jun, Sep, Nov
          tmpDays = 30
        Case 2                              ' Feb
          If IsLeapYear("01/01" & Year(verTmpPD)) Then
            tmpDays = 29
          Else
            tmpDays = 28
          End If
      End Select
      Do While PrintDate < verCurrentPD
        PrintDate = verTmpPD
        MonYeaCount = MonYeaCount + 1
        If MonYeaCount > 1 Then
          PrintDate = verTmpPD
          If Recurrence = 3 Then Months
          If Recurrence = 4 Then Years
        End If
        Select Case verComb3
          Case 1 To 7                       ' Monday to Sunday
            Select Case verComb2
              Case 1, 2, 3, 4
                For i = 1 To tmpDays
                  If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) = verComb3 Then
                    verTmpPD = i & "/" & Month(PrintDate) & "/" & Year(PrintDate)
                    ThisCount = ThisCount + 1
                    If ThisCount = verComb2 Then
                      PrintDate = verTmpPD
                      Exit For
                    End If
                  End If
                Next i
              Case 5
                For i = 1 To tmpDays
                  If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) = verComb3 Then
                    PrintDate = i & "/" & Month(PrintDate) & "/" & Year(PrintDate)
                  End If
                Next i
            End Select
          Case 8                            ' Day
            Select Case verComb2
              Case 1, 2, 3, 4
                PrintDate = verComb2 & "/" & Month(PrintDate) & "/" & Year(PrintDate)
              Case 5
                PrintDate = tmpDays & "/" & Month(PrintDate) & "/" & Year(PrintDate)
            End Select
          Case 9                            ' Weekday
            Select Case verComb2
              Case 1, 2, 3, 4
                ThisCount = 0
                For i = 1 To tmpDays
                  If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) < 6 Then
                  'If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) <> 6 And _
                  '   WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) <> 7 Then
                    PrintDate = i & "/" & Month(PrintDate) & "/" & Year(PrintDate)
                    ThisCount = ThisCount + 1
                    If ThisCount = verComb2 Then Exit For
                  End If
                Next i
              Case 5
                For i = 1 To tmpDays
                  If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) < 6 Then
                  'If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) <> 6 And _
                  '   WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) <> 7 Then
                    PrintDate = i & "/" & Month(PrintDate) & "/" & Year(PrintDate)
                  End If
                Next i
            End Select
          Case 10                           ' Weekend
            Select Case verComb2
              Case 1, 2, 3, 4
                For i = 1 To tmpDays
                  If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) > 5 Then
                    PrintDate = i & "/" & Month(PrintDate) & "/" & Year(PrintDate)
                    ThisCount = ThisCount + 1
                    If ThisCount = verComb2 Then Exit For
                  End If
                Next i
              Case 5
                For i = 1 To tmpDays
                  If WeekDay(i & "/" & Month(PrintDate) & "/" & Year(PrintDate), vbMonday) > 5 Then
                    PrintDate = i & "/" & Month(PrintDate) & "/" & Year(PrintDate)
                  End If
                Next i
            End Select
        End Select
      Loop
    End Sub
    
    Function IsLeapYear(ByVal SomeValue As Variant) As Boolean
      On Error GoTo LocalError
      Dim intYear As Integer
      If IsDate(SomeValue) Then
        intYear = CInt(Year(SomeValue))
      Else
        intYear = CInt(SomeValue)
      End If
      
      If TypeName(intYear) = "Integer" Then
        IsLeapYear = ((intYear Mod 4 = 0) And _
        (intYear Mod 100 <> 0) Or (intYear Mod 400 = 0))
      End If
      
      Exit Function
    LocalError:
      IsLeapYear = False
      
    End Function
    
    Ian
    Last edited by VanDam; 07-09-2005 at 05:37 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center