Results 1 to 7 of 7
  1. #1
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102

    Run-Time error 3021 trouble resolving

    This is the code I use to make a leave table, but if there are no records to create I get the run time error 3021.

    Sub MakeLeaveTable()
    '* This VBA code is Copyright by Christopher Mead, [www.MeadInKent.co.uk 2008]
    '* Update the LeaveTable with new values of leave bookings
    Dim MyDB As Database, LData As Recordset, LTable As Recordset, Temp As String, LUsers As Recordset
    Dim MyMon As Integer, MyYear As Integer, NumMonths As Integer, MyDate As Date, LastDayOfMonth As Integer
    Dim Specials As Recordset
    Set MyDB = CurrentDb()
    Set LData = MyDB.OpenRecordset("leavedata", dbOpenDynaset)
    Set LUsers = MyDB.OpenRecordset("users", dbOpenDynaset)
    Set LTable = MyDB.OpenRecordset("leavetable", dbOpenDynaset)
    Set Specials = MyDB.OpenRecordset("specialdates", dbOpenDynaset)
    'Temp = Format(Date, "MMYY")
    'Temp = InputBox("Start period (MMYY)", "MakeLeaveTable()", Temp)
    Debug.Print Val(Forms("compiledata").Controls("f_periodfrommm" ).Value)
    Debug.Print Val(Forms("compiledata").Controls("f_periodfromyyy y").Value) - 2000
    Debug.Print Val(Forms("compiledata").Controls("f_nummonths").V alue)
    MyMon = Val(Forms("compiledata").Controls("f_periodfrommm" ).Value)
    MyYear = Val(Forms("compiledata").Controls("f_periodfromyyy y").Value) - 2000
    NumMonths = Val(Forms("compiledata").Controls("f_nummonths").V alue)
    If NumMonths <> 0 Then
    Debug.Print "ok"
    Forms("compiledata").Controls("f_progress").Value = "2. Compiling table ..."
    DoCmd.Hourglass True
    For m = MyMon To MyMon + NumMonths - 1
    'Debug.Print
    'Debug.Print "Mon " & m & " > "
    LastDayOfMonth = Day(DateSerial(2000 + MyYear, m + 1, 1) - 1)
    LUsers.MoveFirst
    Do While Not LUsers.EOF
    If LUsers!UActive = "Y" Then
    Debug.Print
    Debug.Print LUsers!UName;
    LTable.AddNew
    LTable!tmonth = Month(DateSerial(2000 + MyYear, m, 1))
    LTable!tyear = Year(DateSerial(2000 + MyYear, m, 1))
    LTable!tsortby = LUsers!USortBy & LUsers!UName
    LTable!tname = LUsers!UName
    LTable!tshading = LUsers!UDeptShading


    For d = 1 To 31
    If d > LastDayOfMonth Then
    Temp = "-"
    Else
    MyDate = DateSerial(2000 + MyYear, m, d)
    If Weekday(MyDate, vbMonday) < 6 Then
    LData.MoveFirst
    Temp = "."
    Do While Not LData.EOF
    If LUsers!UInits = LData!LInits Then
    If MyDate >= LData!LDateFrom And MyDate <= LData!LDateTo Then
    Temp = LData!LType
    Exit Do
    End If
    End If
    LData.MoveNext
    Loop
    Else
    Temp = "W"
    End If
    End If
    Specials.MoveFirst ' check if current date is in specials table
    Do While Not Specials.EOF
    If MyDate = Specials!xdate Then
    Temp = "X"
    Exit Do
    End If
    Specials.MoveNext
    Loop
    'Debug.Print Temp;
    LTable.Fields("t" & Format(d, "00")) = Temp ' set field for date to any reason code
    Next d ' day
    LTable.Update
    End If ' if user is active
    LUsers.MoveNext
    Loop
    Next m ' month
    DoCmd.Hourglass False
    End If ' OK button selected
    LData.Close
    LTable.Close
    LUsers.Close
    Specials.Close
    Forms("compiledata").Controls("f_progress").Value = "3. Done ..."

    End Sub

    How can I trap there and print a message to the user to let them know there are no records?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Get a DCOUNT of the query,if zero, give message.

    if Dcount("*","qsQuery")=0 then
    MsgBox "no records"
    else

  3. #3
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Thanks for that Ranman256 but where would I place that in that function?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The first thing is to determine where the error is occurring. You are dealing with many recordsets and it could be happening on any one of them. Use the debugging and step thru the code one line at a time to determine where the error occurs. At that point you will know how/where to handle the error.

  5. #5
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    LData.MoveFirst

    Thisis the line that is highlighted when I hit the debug button on the error message

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Where you put the DCount ("leavedata") is determined by your requirements, what do you want to happen when the record count is zero? For instance, if you want to exit the routine then put the DCount right at the very beginning. Or else handle it right where it happens.

    You don't want to do the DCount more often than you need to, it is a resource hog and doesn't need to be done more than once. A good move would be to do it right at the beginning and then store the value. Do it for all your recordsets that you are using, then you can use the variables throughout the routine as and when you need to.

  7. #7
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    HI and thanks aytee111, I did as you suggested and placed it near the beginning of the routine and it works a treat, thanks again.

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

Similar Threads

  1. Run-time Error 3021
    By Accu-Grind in forum Forms
    Replies: 5
    Last Post: 11-10-2015, 11:47 AM
  2. 3021 Error
    By RonL in forum Programming
    Replies: 6
    Last Post: 05-10-2013, 03:45 PM
  3. Error 3021
    By Marianna_Air in forum Forms
    Replies: 27
    Last Post: 08-20-2012, 01:13 PM
  4. Run Time Error 3021 - Access 2002 SP3
    By alpinegroove in forum Programming
    Replies: 9
    Last Post: 01-24-2012, 04:38 PM
  5. Replies: 8
    Last Post: 05-16-2011, 06:01 PM

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