Error 3071: Expression too complex

[Knut Ole]

Hi,

I had a beautifully working code, but it's now 3071, and I don't know what have changed. I get 3071 on line 214, but I suspect that's not where the problem lies?

I'm looking and looking, and can't seem to find what could create this error...

'draw: bookings
    '**************
 
    Do Until RecSetBookings.EOF
 
        'finding variables
        '*****************
        q = RecSetBookings("RoomID").Value - 1
        vOffset = 420 * q
        arTop = vOffset + 40
 
 
        slBeg = RecSetBookings.Fields("SlotBegin").Value
        slEnd = RecSetBookings.Fields("SlotEnd").Value
 
            bolDrawTail = True
            bolDrawHead = True
            bolDrawBody = True
            bolDrawLabel = True
            bolDrawSmall = False
 
        leftpos = (slBeg - dstart) * 1217 + 730
        'MsgBox leftpos
            If leftpos < 720 Then
                bolDrawTail = False
                leftpos = 720
                leftposbox = 730
                wdthBox = (((slEnd - dstart) * 1217) - 370)
                If wdthBox < 0 Then wdthBox = 0
                If wdthBox > 0 Then wdthBox = ((slEnd - dstart) * 1217) - 180
                leftposhead = leftpos + wdthBox
                MsgBox leftposhead & ", " & wdthBox
                If leftposhead < 720 Then leftposhead = 730 + wdthBox
 
            Else
                bolDrawTail = True
                leftposbox = leftpos + 180
                wdthBox = (((slEnd - slBeg) * 1217) - 370)
                If wdthBox < 0 Then wdthBox = 0
                leftposhead = leftpos + ((slEnd - slBeg) * 1217) - 190
            End If
 
        rcBookID = RecSetBookings.Fields("Bookings.ID").Value
        bConf = RecSetBookings.Fields("Confirmed").Value
        chinDate = RecSetBookings.Fields("CheckInDate").Value
        choutDate = RecSetBookings.Fields("CheckOutDate").Value
        bChkIn = RecSetBookings.Fields("CheckedIn").Value
        bChkOut = RecSetBookings.Fields("CheckedOut").Value
 
       ' MsgBox slBeg & ", " & dstart
 
        If bChkIn = True And bChkOut = False Then colorArrow = 1                                'green
        If chinDate = Date And bChkIn = False Then colorArrow = 2                               'green shade
        If chinDate > Date And bConf = True Then colorArrow = 3                                 'yellow
        If chinDate > Date And bConf = False Then colorArrow = 4                                'yellow shade
        If choutDate = Date And bChkOut = False Or choutDate < Date Then colorArrow = 5         'red
        If bChkOut = True Then colorArrow = 6                                                   'grey
        '+ black for out of order
 
 
        Select Case colorArrow
            Case 1 'green
                tailImage = imgGreenArrowTail
                headImage = imgGreenArrowHead
                bodyImage = imgGreenBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgGreenArrowSmall
            Case 2 'green shaded
                tailImage = imgGreenArrowTailShade
                headImage = imgGreenArrowHeadShade
                bodyImage = imgGreenBoxShade
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgGreenArrowSmallShaded
            Case 3 'yellow
                tailImage = imgYellowArrowTail
                headImage = imgYellowArrowHead
                bodyImage = imgYellowBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgYellowArrowSmall
            Case 4 'yellow shaded
                tailImage = imgYellowArrowTailShade
                headImage = imgYellowArrowHeadShade
                bodyImage = imgYellowBoxShade
                lblForeColor = RGB(0, 0, 0)
                smallImage = imgYellowArrowSmallShaded
            Case 5 'red
                tailImage = imgRedArrowTail
                headImage = imgRedArrowHead
                bodyImage = imgRedBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgRedArrowSmall
            Case 6 'grey
                tailImage = imgGreyArrowTail
                headImage = imgGreyArrowHead
                bodyImage = imgGreyBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgGreyArrowSmall
            Case 7 'black
 
        End Select
 
        If IsNull(slEnd) Then MsgBox "slEnd is null!?"
        If IsNull(slBeg) Then MsgBox "slBeg is null!?"
 
        If slEnd - slBeg < 0.33 Then
            MsgBox "< 8 hours"
            tailImage = shortImage
            bolDrawTail = False
            bolDrawHead = False
            bolDrawBody = False
            bolDrawLabel = False
            bolDrawSmall = True
        End If
 
 
        'draw: small arrow
        '**********
        If bolDrawSmall = True Then
        Set cImg1 = CreateControl(strForms, acImage)
         With cImg1
             .BackStyle = 0
             'msgWidth = (slEnd - slBeg) * 1217
             'MsgBox "msgW=" & msgWidth & ", slEnd=" & slEnd & ", slBeg=" & slBeg & ", ID=" & rcBookID
             .Width = (slEnd - slBeg) * 1217
             .Height = 360
             .Left = leftpos
             .Top = arTop
             .ControlTipText = rcBookID
             .OnClick = ""
             .Picture = smallImage
             .SizeMode = acOLESizeStretch
            '.PictureAlignment = 0
         End With
        End If
 
        'draw: tail
        '**********
        If bolDrawTail = True Then
        Set cImg1 = CreateControl(strForms, acImage)
         With cImg1
             .BackStyle = 0
             .Width = 432
             .SizeMode = 0
             .Height = 360
             .Left = leftpos
             .Top = arTop
             .ControlTipText = rcBookID
             .OnClick = ""
             .Picture = tailImage
             .SizeMode = acOLESizeZoom
             .PictureAlignment = 0
         End With
        End If
 
        'draw: head
        '**********
        If bolDrawHead = True Then
        Set cImg2 = CreateControl(strForms, acImage)
            With cImg2
                .BackStyle = 0
                .Width = 400
                .Height = 360
                .SizeMode = 0
                .ControlTipText = rcBookID
                .Left = leftposhead
                '.Left = leftPos + 300
                .Top = arTop
                .Picture = headImage
                .SizeMode = acOLESizeZoom
                .PictureAlignment = 0
                .BorderStyle = 0
            End With
        End If
 
        'draw: body
        '***************
        If bolDrawBody = True Then
        Set cImg3 = CreateControl(strForms, acImage)
           With cImg3
                .BackStyle = 0
                .Width = wdthBox
                .Height = 360
                .SizeMode = 0
                .ControlTipText = rcBookID
                .Left = leftposbox
                '.Left = leftPos + 300
                .Top = arTop
                .Picture = bodyImage
            End With
        End If
 
        'draw: label
        '**********
        If bolDrawLabel = True Then
        Set cLbl1 = CreateControl(strForms, acLabel)
           With cLbl1
               .Visible = True
               .BackColor = RGB(255, 194, 14)
               .ForeColor = lblForeColor
               .FontWeight = 900
               .TopMargin = 34
               .TextAlign = 2
               .BackStyle = 0
               .FontWeight = 900
               .Top = arTop + 10
               .Height = 330
               .Left = leftposbox
               .Width = wdthBox
               '.Width = 200
               .Caption = rcBookID
           End With
        End If
 
        RecSetBookings.MoveNext                                         'moving to next booking
 
    Loop
 

I can post the whole module if needed. Thank you very much!

[Knut Ole]

Posting full code just in case, (error now being in line 353)

Public Function makeArrows()
 
    'initializing
    '************
    startTime = Time()
    Dim RecSetBookings As DAO.Recordset
    Dim RecSetDatePoint As DAO.Recordset
    Dim RecSetRooms As DAO.Recordset
    Dim ctl As Control
    Dim frmSub As Form
    Dim strSubForm As Form
 
    Set RecSetBookings = CurrentDb.OpenRecordset("qBookings", dbOpenSnapshot, dbOpenForwardOnly)
    Set RecSetRooms = CurrentDb.OpenRecordset("qRoomsAll", dbOpenSnapshot, dbOpenForwardOnly)
 
    dstart = DLookup("[RStartDate]", "DatePointer")                         'constant
    rCount = DCount("[RoomNumber]", "Rooms")                                'SC variable
 
        imgYellowArrowTail = "C:\Users\Lailita\Documents\arrows\yellowA.wmf"
        imgYellowArrowHead = "C:\Users\Lailita\Documents\arrows\yellowAh.wmf"
        imgYellowArrowTailShade = "C:\Users\Lailita\Documents\arrows\yshadeA.wmf"
        imgYellowArrowHeadShade = "C:\Users\Lailita\Documents\arrows\yshadeAh.wmf"
        imgYellowBox = "C:\Users\Lailita\Documents\arrows\yellowB.wmf"
        imgYellowBoxShade = "C:\Users\Lailita\Documents\arrows\yshadeB.wmf"
        imgYellowArrowSmall = "C:\Users\Lailita\Documents\arrows\yellowArrowSmall.wmf"
        imgYellowArrowSmallShaded = "C:\Users\Lailita\Documents\arrows\yellowArrowSmallShaded.wmf"
 
        imgGreenArrowTail = "C:\Users\Lailita\Documents\arrows\greenA.wmf"
        imgGreenArrowTailShade = "C:\Users\Lailita\Documents\arrows\gshadeA.wmf"
        imgGreenArrowHead = "C:\Users\Lailita\Documents\arrows\greenAh.wmf"
        imgGreenArrowHeadShade = "C:\Users\Lailita\Documents\arrows\gshadeAh.wmf"
        imgGreenBox = "C:\Users\Lailita\Documents\arrows\greenB.wmf"
        imgGreenBoxShade = "C:\Users\Lailita\Documents\arrows\gshadeB.wmf"
        imgGreenArrowSmall = "C:\Users\Lailita\Documents\arrows\greenArrowSmall.wmf"
        imgGreenArrowSmallShaded = "C:\Users\Lailita\Documents\arrows\greenArrowSmallShaded.wmf"
 
        imgRedArrowTail = "C:\Users\Lailita\Documents\arrows\redA.wmf"
        imgRedArrowHead = "C:\Users\Lailita\Documents\arrows\redAh.wmf"
        imgRedBox = "C:\Users\Lailita\Documents\arrows\redB.wmf"
        imgRedArrowSmall = "C:\Users\Lailita\Documents\arrows\redArrowSmall.wmf"
 
        imgGreyArrowTail = "C:\Users\Lailita\Documents\arrows\greyA.wmf"
        imgGreyArrowHead = "C:\Users\Lailita\Documents\arrows\greyAh.wmf"
        imgGreyBox = "C:\Users\Lailita\Documents\arrows\greyB.wmf"
        imgGreyArrowSmall = "C:\Users\Lailita\Documents\arrows\greyArrowSmall.wmf"
 
 
 
    'disconnect subform from mainform control                               'OK!
    '****************************************
    ctr = "ctrCanvas"                                                       'constant
    Forms!Calendar!(ctr).SourceObject = ""                                  'OK!
 
 
    'open form
    '*********
    strForms = "frmCanvas"                                                  'constant
    DoCmd.OpenForm strForms, acDesign, , , acFormEdit, acHidden
 
 
    'delete all controls in all subforms                                    'OK!
    '***********************************
    Do While Forms(strForms).Controls.Count > 0
        DeleteControl strForms, Forms(strForms).Controls(0).Name
    Loop
 
 
    'draw: background vertical lines
    '*******************************
    If q = 0 Then
        p = 1
        Do Until p = 14
            Set cBackLinev = CreateControl(strForms, acLine)
            With cBackLinev
                .Width = 0
                .Height = 420 * rCount
                .Left = p * 1217 + 710
                .Top = 0
                .BorderColor = RGB(240, 240, 240)
            End With
            p = p + 1
        Loop
    End If
 
 
 
    '***********************
    '* draw: for each room *
    '***********************
    q = -1                                              'line relativity
    Do Until RecSetRooms.EOF                            '1st DO LOOP / Do until all rooms have been processed
        q = q + 1
        roomNow = RecSetRooms.Fields("RoomNumber").Value
        roomClean = RecSetRooms.Fields("Clean").Value
        vOffset = 420 * q
        rmTop = vOffset + 30
 
 
        'draw: background horisontal lines for each room
        '***********************************************
        If q Mod 2 = 0 Then bckStl = 1 Else bckStl = 0
        Set cBackLineh = CreateControl(strForms, acLabel)
            With cBackLineh
                .BackStyle = bckStl
                .Width = 17100
                .Height = 400
                .Left = 720
                .Top = rmTop - 15
                .BorderStyle = 0
                .BorderWidth = 0
                .BackColor = RGB(240, 240, 240)
            End With
 
 
        'draw: room-number box
        '*********************
        Set cRoomBox = CreateControl(strForms, acLabel)
            With cRoomBox
                .BackStyle = 1
                .Width = 580
                .Height = 380
                .Left = 50
                .Top = rmTop
                .Caption = roomNow
                .BorderStyle = 1
                .FontSize = 14
                .TextAlign = 2
                .BorderWidth = 1
                .FontWeight = 700
                '.BackColor = RGB(0, 255, 0)
            End With
        RecSetRooms.MoveNext
 
    Loop
 
 
'------------------------------------------------------
 
 
    'draw: bookings
    '**************
 
    Do Until RecSetBookings.EOF
 
        'finding variables
        '*****************
        q = RecSetBookings("RoomID").Value - 1
        vOffset = 420 * q
        arTop = vOffset + 40
 
 
        slBeg = RecSetBookings.Fields("SlotBegin").Value
        slEnd = RecSetBookings.Fields("SlotEnd").Value
 
            bolDrawTail = True
            bolDrawHead = True
            bolDrawBody = True
            bolDrawLabel = True
            bolDrawSmall = False
 
        leftpos = (slBeg - dstart) * 1217 + 730
        'MsgBox leftpos
            If leftpos < 720 Then
                bolDrawTail = False
                leftpos = 720
                leftposbox = 730
                wdthBox = (((slEnd - dstart) * 1217) - 370)
                If wdthBox < 0 Then wdthBox = 0
                If wdthBox > 0 Then wdthBox = ((slEnd - dstart) * 1217) - 180
                leftposhead = leftpos + wdthBox
                MsgBox leftposhead & ", " & wdthBox
                If leftposhead < 720 Then leftposhead = 730 + wdthBox
 
            Else
                bolDrawTail = True
                leftposbox = leftpos + 180
                wdthBox = (((slEnd - slBeg) * 1217) - 370)
                If wdthBox < 0 Then wdthBox = 0
                leftposhead = leftpos + ((slEnd - slBeg) * 1217) - 190
            End If
 
        rcBookID = RecSetBookings.Fields("Bookings.ID").Value
        bConf = RecSetBookings.Fields("Confirmed").Value
        chinDate = RecSetBookings.Fields("CheckInDate").Value
        choutDate = RecSetBookings.Fields("CheckOutDate").Value
        bChkIn = RecSetBookings.Fields("CheckedIn").Value
        bChkOut = RecSetBookings.Fields("CheckedOut").Value
 
       ' MsgBox slBeg & ", " & dstart
 
        If bChkIn = True And bChkOut = False Then colorArrow = 1                                'green
        If chinDate = Date And bChkIn = False Then colorArrow = 2                               'green shade
        If chinDate > Date And bConf = True Then colorArrow = 3                                 'yellow
        If chinDate > Date And bConf = False Then colorArrow = 4                                'yellow shade
        If choutDate = Date And bChkOut = False Or choutDate < Date Then colorArrow = 5         'red
        If bChkOut = True Then colorArrow = 6                                                   'grey
        '+ black for out of order
 
 
        Select Case colorArrow
            Case 1 'green
                tailImage = imgGreenArrowTail
                headImage = imgGreenArrowHead
                bodyImage = imgGreenBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgGreenArrowSmall
            Case 2 'green shaded
                tailImage = imgGreenArrowTailShade
                headImage = imgGreenArrowHeadShade
                bodyImage = imgGreenBoxShade
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgGreenArrowSmallShaded
            Case 3 'yellow
                tailImage = imgYellowArrowTail
                headImage = imgYellowArrowHead
                bodyImage = imgYellowBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgYellowArrowSmall
            Case 4 'yellow shaded
                tailImage = imgYellowArrowTailShade
                headImage = imgYellowArrowHeadShade
                bodyImage = imgYellowBoxShade
                lblForeColor = RGB(0, 0, 0)
                smallImage = imgYellowArrowSmallShaded
            Case 5 'red
                tailImage = imgRedArrowTail
                headImage = imgRedArrowHead
                bodyImage = imgRedBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgRedArrowSmall
            Case 6 'grey
                tailImage = imgGreyArrowTail
                headImage = imgGreyArrowHead
                bodyImage = imgGreyBox
                lblForeColor = RGB(255, 255, 255)
                smallImage = imgGreyArrowSmall
            Case 7 'black
 
        End Select
 
        If IsNull(slEnd) Then MsgBox "slEnd is null!?"
        If IsNull(slBeg) Then MsgBox "slBeg is null!?"
 
        If slEnd - slBeg < 0.33 Then
            MsgBox "< 8 hours"
            tailImage = shortImage
            bolDrawTail = False
            bolDrawHead = False
            bolDrawBody = False
            bolDrawLabel = False
            bolDrawSmall = True
        End If
 
 
        'draw: small arrow
        '**********
        If bolDrawSmall = True Then
        Set cImg1 = CreateControl(strForms, acImage)
         With cImg1
             .BackStyle = 0
             'msgWidth = (slEnd - slBeg) * 1217
             'MsgBox "msgW=" & msgWidth & ", slEnd=" & slEnd & ", slBeg=" & slBeg & ", ID=" & rcBookID
             .Width = (slEnd - slBeg) * 1217
             .Height = 360
             .Left = leftpos
             .Top = arTop
             .ControlTipText = rcBookID
             .OnClick = ""
             .Picture = smallImage
             .SizeMode = acOLESizeStretch
            '.PictureAlignment = 0
         End With
        End If
 
        'draw: tail
        '**********
        If bolDrawTail = True Then
        Set cImg1 = CreateControl(strForms, acImage)
         With cImg1
             .BackStyle = 0
             .Width = 432
             .SizeMode = 0
             .Height = 360
             .Left = leftpos
             .Top = arTop
             .ControlTipText = rcBookID
             .OnClick = ""
             .Picture = tailImage
             .SizeMode = acOLESizeZoom
             .PictureAlignment = 0
         End With
        End If
 
        'draw: head
        '**********
        If bolDrawHead = True Then
        Set cImg2 = CreateControl(strForms, acImage)
            With cImg2
                .BackStyle = 0
                .Width = 400
                .Height = 360
                .SizeMode = 0
                .ControlTipText = rcBookID
                .Left = leftposhead
                '.Left = leftPos + 300
                .Top = arTop
                .Picture = headImage
                .SizeMode = acOLESizeZoom
                .PictureAlignment = 0
                .BorderStyle = 0
            End With
        End If
 
        'draw: body
        '***************
        If bolDrawBody = True Then
        Set cImg3 = CreateControl(strForms, acImage)
           With cImg3
                .BackStyle = 0
                .Width = wdthBox
                .Height = 360
                .SizeMode = 0
                .ControlTipText = rcBookID
                .Left = leftposbox
                '.Left = leftPos + 300
                .Top = arTop
                .Picture = bodyImage
            End With
        End If
 
        'draw: label
        '**********
        If bolDrawLabel = True Then
        Set cLbl1 = CreateControl(strForms, acLabel)
           With cLbl1
               .Visible = True
               .BackColor = RGB(255, 194, 14)
               .ForeColor = lblForeColor
               .FontWeight = 900
               .TopMargin = 34
               .TextAlign = 2
               .BackStyle = 0
               .FontWeight = 900
               .Top = arTop + 10
               .Height = 330
               .Left = leftposbox
               .Width = wdthBox
               '.Width = 200
               .Caption = rcBookID
           End With
        End If
 
        RecSetBookings.MoveNext                                         'moving to next booking
 
    Loop
 
 
 
 
 
    'close & reconnect
    '*****************
    strForms = "frmCanvas"
    DoCmd.Close acForm, strForms, acSaveYes
    Forms!Calendar!(ctr).SourceObject = "frmCanvas"
 
 
    'refresh
    '*******
   ' DoCmd.Requery
    Forms!Calendar.Refresh
    Forms!Calendar.Repaint
 
 
    'clean up
    '********
    DoCmd.Echo (True)
    Set RecSetBookings = Nothing
    Set RecSetRooms = Nothing
 
End Function
 

[Knut Ole]

Seems it's due to info in the Tables/Records themselves. When I completely emptied all test data in all tables and added fresh ones, things are working fine.

FYI. Thank you.

[TheSmileyCoder]

I dont spot anything wrong in the code. Only thing I notice is that you use dbopensnapshot, whereas I standardly use dbopendynaset.
However I will be honest and say I dont have enough experience in that area to say WHY I do so. Guess it worked the first time I used it, and I've kinda stuck to it since :P

That said, I do notice an error in your opening statement:

CurrentDb.OpenRecordset("qBookings", dbOpenSnapshot, dbOpenForwardOnly)

The Syntax for the Openrecordset function is:

CurrentDB.OpenRecordset (source, type, options, lockedits)

Problem is that dbOpenSnapshot, as well as dbOpenForwardOnly are arguments meant for the TYPE variable. You are instead passing the dbOpenForwardOnly constant as a Options variable. dbOpenForwardOnly is an internal constant of value 8. If you examine the possibilities for the Options argument, you will find that dbAppendOnly (one of the possibilites) also has the value 8. Thus you are in fact opening a dbAppendOnly recordset. Now from your code it doesn't seem to matter, but jsut thought I would point out the error since I saw it.

[Knut Ole]

thanks smiley!

so what do I do with the two types, add & or + in between?

[TheSmileyCoder]

They are two different types. Its a case of "one or the other", you cannot select both (as far as I know/understand).

[Knut Ole]

Ok, thanks for that tip smiley, I've changed it.

Since I cleaned out my test tables etc., the error has now returned. It happened after I added a new recordset, and it persists after I deleted the recordset again.

as we agree, there is nothing fundamentally wrong with the code above. hence - where to even begin debugging? my tables are not overly complicated either, just a few normal relations here and there, and one calculated field (combining namestrings).

this has been my fear from the beginning of starting this project, that it will turn big and cracked and some impossible error stopping the whole thing...

any ideas of where to look and begin debugging greatly appreciated!! thank you very much!

[Knut Ole]

excuse me, but I've managed to return to working code.
AGAIN - it seems it was an issue with EMPTY CELLS in the table.

so far, it seems this error is due to missing information or faulty information in the relevant table(s). fyi.

thanks!

[TheSmileyCoder]

Im still not sure how that would affect the .MoveNext command, and cause that to err.

I've been looking at your code to see if anything could affect the recordset. Only thing I seem to see is line 52:

Forms!Calendar!(ctr).SourceObject = ""

Which would cause the forms recordset to save, if it was dirty (and if it even has a recordset tied to it).

What is the SQL syntax for your query?


As far as I know, empty or incorrect fields could ofcourse cause your recordset to contain faulty data, but that should not affect the .MoveNext. It simply tells Access to proceed to the next record. As long as your not at the end of the recordset it shouldn't throw an error.

Try using a dbopendynaset instead of a dbopensnapshot and see if that helps.

[Knut Ole]

Thanks again smiley!

SQL of query (qBookings) is

SELECT qBase.*, DatePointer.RStartDate, CDbl([CheckInDate]+[CheckInTime]) AS SlotBegin, CDbl([CheckOutDate]+[CheckOutTime]) AS SlotEnd
FROM DatePointer, qBase
WHERE (((CDbl([CheckInDate]+[CheckInTime]))>=([RStartDate]) And (CDbl([CheckInDate]+[CheckInTime]))<=([RStartDate]+14))) OR (((CDbl([CheckOutDate]+[CheckOutTime]))>=([RStartDate]) And (CDbl([CheckOutDate]+[CheckOutTime]))<=([RStartDate]+14))) OR (((CDbl([CheckInDate]+[CheckInTime]))<([RStartDate])) AND ((CDbl([CheckOutDate]+[CheckOutTime]))>([RStartDate])));
 

and for qBase

SELECT Bookings.*, Contacts.*
FROM Rooms RIGHT JOIN (Contacts INNER JOIN Bookings ON Contacts.ID = Bookings.ContactID) ON Rooms.ID = Bookings.RoomID;

About the .SourceObject = "", I suspect that doesn't matter because there are actually no records in the subform (to my thinking) - it's only a drawingboard for some arrows. The subform is thus unbound. The mainform, also, seems irrelvant if updated - it's merely bound to a DatePointer, a date in a table with one field/record, which acts as a reference point for the drawings.

I'm still suspecting it's due to empty cells in a recordset - cells which are needed for the drawing. So perhaps Access checks the existence of values to be used in the upcoming loop, or something like that, and finds them lacking. The debug seems to be simply pointing to the wrong place, which I understand is commonplace?

I've realised I have a bug in my Cancel Reservation command, so bookings pop up incomplete in the table. This is creating the error, as far as I can see.

I hope it's sorted now, I guess I have to make some kind of error handler for it and populate the fields or something, sounds feasible?

Thanks again smiley!

[TheSmileyCoder]

As I mentioned earlier, im not too experienced in the different types of recordsets but here goes anyway:

I believe that the dbOpensnapshot might only open a section of the recordset. Thus the problem doesn't occur until you use .MoveNext to move to a "yet unloaded" section of the recordset, and while access loads the next section, it runs into empty fields while performing the query, and thus throws the error at that point in the code (the .MoveNext).

If you try to change the recordset to a dbopendynaset, I suspect that it should throw the error when you first open the recordset.

Thats kinda the best sense I can make of the situation at present, since cdbl(Null) will throw an error 94 (Invalid use of Null). I know thats not the error your seeing, but maybe thats still the underlying problem.


On another note, I dont think you need to use the cdbl function.

While you can use and mix SQL and vba functions you should know that there will be a performance hit when you mix the two. A pure SQL (with no VBA calls) will run significantly faster.