469,271 Members | 1,778 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

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...

Expand|Select|Wrap|Line Numbers
  1. 'draw: bookings
  2.     '**************
  3.  
  4.     Do Until RecSetBookings.EOF
  5.  
  6.         'finding variables
  7.         '*****************
  8.         q = RecSetBookings("RoomID").Value - 1
  9.         vOffset = 420 * q
  10.         arTop = vOffset + 40
  11.  
  12.  
  13.         slBeg = RecSetBookings.Fields("SlotBegin").Value
  14.         slEnd = RecSetBookings.Fields("SlotEnd").Value
  15.  
  16.             bolDrawTail = True
  17.             bolDrawHead = True
  18.             bolDrawBody = True
  19.             bolDrawLabel = True
  20.             bolDrawSmall = False
  21.  
  22.         leftpos = (slBeg - dstart) * 1217 + 730
  23.         'MsgBox leftpos
  24.             If leftpos < 720 Then
  25.                 bolDrawTail = False
  26.                 leftpos = 720
  27.                 leftposbox = 730
  28.                 wdthBox = (((slEnd - dstart) * 1217) - 370)
  29.                 If wdthBox < 0 Then wdthBox = 0
  30.                 If wdthBox > 0 Then wdthBox = ((slEnd - dstart) * 1217) - 180
  31.                 leftposhead = leftpos + wdthBox
  32.                 MsgBox leftposhead & ", " & wdthBox
  33.                 If leftposhead < 720 Then leftposhead = 730 + wdthBox
  34.  
  35.             Else
  36.                 bolDrawTail = True
  37.                 leftposbox = leftpos + 180
  38.                 wdthBox = (((slEnd - slBeg) * 1217) - 370)
  39.                 If wdthBox < 0 Then wdthBox = 0
  40.                 leftposhead = leftpos + ((slEnd - slBeg) * 1217) - 190
  41.             End If
  42.  
  43.         rcBookID = RecSetBookings.Fields("Bookings.ID").Value
  44.         bConf = RecSetBookings.Fields("Confirmed").Value
  45.         chinDate = RecSetBookings.Fields("CheckInDate").Value
  46.         choutDate = RecSetBookings.Fields("CheckOutDate").Value
  47.         bChkIn = RecSetBookings.Fields("CheckedIn").Value
  48.         bChkOut = RecSetBookings.Fields("CheckedOut").Value
  49.  
  50.        ' MsgBox slBeg & ", " & dstart
  51.  
  52.         If bChkIn = True And bChkOut = False Then colorArrow = 1                                'green
  53.         If chinDate = Date And bChkIn = False Then colorArrow = 2                               'green shade
  54.         If chinDate > Date And bConf = True Then colorArrow = 3                                 'yellow
  55.         If chinDate > Date And bConf = False Then colorArrow = 4                                'yellow shade
  56.         If choutDate = Date And bChkOut = False Or choutDate < Date Then colorArrow = 5         'red
  57.         If bChkOut = True Then colorArrow = 6                                                   'grey
  58.         '+ black for out of order
  59.  
  60.  
  61.         Select Case colorArrow
  62.             Case 1 'green
  63.                 tailImage = imgGreenArrowTail
  64.                 headImage = imgGreenArrowHead
  65.                 bodyImage = imgGreenBox
  66.                 lblForeColor = RGB(255, 255, 255)
  67.                 smallImage = imgGreenArrowSmall
  68.             Case 2 'green shaded
  69.                 tailImage = imgGreenArrowTailShade
  70.                 headImage = imgGreenArrowHeadShade
  71.                 bodyImage = imgGreenBoxShade
  72.                 lblForeColor = RGB(255, 255, 255)
  73.                 smallImage = imgGreenArrowSmallShaded
  74.             Case 3 'yellow
  75.                 tailImage = imgYellowArrowTail
  76.                 headImage = imgYellowArrowHead
  77.                 bodyImage = imgYellowBox
  78.                 lblForeColor = RGB(255, 255, 255)
  79.                 smallImage = imgYellowArrowSmall
  80.             Case 4 'yellow shaded
  81.                 tailImage = imgYellowArrowTailShade
  82.                 headImage = imgYellowArrowHeadShade
  83.                 bodyImage = imgYellowBoxShade
  84.                 lblForeColor = RGB(0, 0, 0)
  85.                 smallImage = imgYellowArrowSmallShaded
  86.             Case 5 'red
  87.                 tailImage = imgRedArrowTail
  88.                 headImage = imgRedArrowHead
  89.                 bodyImage = imgRedBox
  90.                 lblForeColor = RGB(255, 255, 255)
  91.                 smallImage = imgRedArrowSmall
  92.             Case 6 'grey
  93.                 tailImage = imgGreyArrowTail
  94.                 headImage = imgGreyArrowHead
  95.                 bodyImage = imgGreyBox
  96.                 lblForeColor = RGB(255, 255, 255)
  97.                 smallImage = imgGreyArrowSmall
  98.             Case 7 'black
  99.  
  100.         End Select
  101.  
  102.         If IsNull(slEnd) Then MsgBox "slEnd is null!?"
  103.         If IsNull(slBeg) Then MsgBox "slBeg is null!?"
  104.  
  105.         If slEnd - slBeg < 0.33 Then
  106.             MsgBox "< 8 hours"
  107.             tailImage = shortImage
  108.             bolDrawTail = False
  109.             bolDrawHead = False
  110.             bolDrawBody = False
  111.             bolDrawLabel = False
  112.             bolDrawSmall = True
  113.         End If
  114.  
  115.  
  116.         'draw: small arrow
  117.         '**********
  118.         If bolDrawSmall = True Then
  119.         Set cImg1 = CreateControl(strForms, acImage)
  120.          With cImg1
  121.              .BackStyle = 0
  122.              'msgWidth = (slEnd - slBeg) * 1217
  123.              'MsgBox "msgW=" & msgWidth & ", slEnd=" & slEnd & ", slBeg=" & slBeg & ", ID=" & rcBookID
  124.              .Width = (slEnd - slBeg) * 1217
  125.              .Height = 360
  126.              .Left = leftpos
  127.              .Top = arTop
  128.              .ControlTipText = rcBookID
  129.              .OnClick = ""
  130.              .Picture = smallImage
  131.              .SizeMode = acOLESizeStretch
  132.             '.PictureAlignment = 0
  133.          End With
  134.         End If
  135.  
  136.         'draw: tail
  137.         '**********
  138.         If bolDrawTail = True Then
  139.         Set cImg1 = CreateControl(strForms, acImage)
  140.          With cImg1
  141.              .BackStyle = 0
  142.              .Width = 432
  143.              .SizeMode = 0
  144.              .Height = 360
  145.              .Left = leftpos
  146.              .Top = arTop
  147.              .ControlTipText = rcBookID
  148.              .OnClick = ""
  149.              .Picture = tailImage
  150.              .SizeMode = acOLESizeZoom
  151.              .PictureAlignment = 0
  152.          End With
  153.         End If
  154.  
  155.         'draw: head
  156.         '**********
  157.         If bolDrawHead = True Then
  158.         Set cImg2 = CreateControl(strForms, acImage)
  159.             With cImg2
  160.                 .BackStyle = 0
  161.                 .Width = 400
  162.                 .Height = 360
  163.                 .SizeMode = 0
  164.                 .ControlTipText = rcBookID
  165.                 .Left = leftposhead
  166.                 '.Left = leftPos + 300
  167.                 .Top = arTop
  168.                 .Picture = headImage
  169.                 .SizeMode = acOLESizeZoom
  170.                 .PictureAlignment = 0
  171.                 .BorderStyle = 0
  172.             End With
  173.         End If
  174.  
  175.         'draw: body
  176.         '***************
  177.         If bolDrawBody = True Then
  178.         Set cImg3 = CreateControl(strForms, acImage)
  179.            With cImg3
  180.                 .BackStyle = 0
  181.                 .Width = wdthBox
  182.                 .Height = 360
  183.                 .SizeMode = 0
  184.                 .ControlTipText = rcBookID
  185.                 .Left = leftposbox
  186.                 '.Left = leftPos + 300
  187.                 .Top = arTop
  188.                 .Picture = bodyImage
  189.             End With
  190.         End If
  191.  
  192.         'draw: label
  193.         '**********
  194.         If bolDrawLabel = True Then
  195.         Set cLbl1 = CreateControl(strForms, acLabel)
  196.            With cLbl1
  197.                .Visible = True
  198.                .BackColor = RGB(255, 194, 14)
  199.                .ForeColor = lblForeColor
  200.                .FontWeight = 900
  201.                .TopMargin = 34
  202.                .TextAlign = 2
  203.                .BackStyle = 0
  204.                .FontWeight = 900
  205.                .Top = arTop + 10
  206.                .Height = 330
  207.                .Left = leftposbox
  208.                .Width = wdthBox
  209.                '.Width = 200
  210.                .Caption = rcBookID
  211.            End With
  212.         End If
  213.  
  214.         RecSetBookings.MoveNext                                         'moving to next booking
  215.  
  216.     Loop
  217.  
I can post the whole module if needed. Thank you very much!
Apr 28 '11 #1
10 10793
Posting full code just in case, (error now being in line 353)

Expand|Select|Wrap|Line Numbers
  1. Public Function makeArrows()
  2.  
  3.     'initializing
  4.     '************
  5.     startTime = Time()
  6.     Dim RecSetBookings As DAO.Recordset
  7.     Dim RecSetDatePoint As DAO.Recordset
  8.     Dim RecSetRooms As DAO.Recordset
  9.     Dim ctl As Control
  10.     Dim frmSub As Form
  11.     Dim strSubForm As Form
  12.  
  13.     Set RecSetBookings = CurrentDb.OpenRecordset("qBookings", dbOpenSnapshot, dbOpenForwardOnly)
  14.     Set RecSetRooms = CurrentDb.OpenRecordset("qRoomsAll", dbOpenSnapshot, dbOpenForwardOnly)
  15.  
  16.     dstart = DLookup("[RStartDate]", "DatePointer")                         'constant
  17.     rCount = DCount("[RoomNumber]", "Rooms")                                'SC variable
  18.  
  19.         imgYellowArrowTail = "C:\Users\Lailita\Documents\arrows\yellowA.wmf"
  20.         imgYellowArrowHead = "C:\Users\Lailita\Documents\arrows\yellowAh.wmf"
  21.         imgYellowArrowTailShade = "C:\Users\Lailita\Documents\arrows\yshadeA.wmf"
  22.         imgYellowArrowHeadShade = "C:\Users\Lailita\Documents\arrows\yshadeAh.wmf"
  23.         imgYellowBox = "C:\Users\Lailita\Documents\arrows\yellowB.wmf"
  24.         imgYellowBoxShade = "C:\Users\Lailita\Documents\arrows\yshadeB.wmf"
  25.         imgYellowArrowSmall = "C:\Users\Lailita\Documents\arrows\yellowArrowSmall.wmf"
  26.         imgYellowArrowSmallShaded = "C:\Users\Lailita\Documents\arrows\yellowArrowSmallShaded.wmf"
  27.  
  28.         imgGreenArrowTail = "C:\Users\Lailita\Documents\arrows\greenA.wmf"
  29.         imgGreenArrowTailShade = "C:\Users\Lailita\Documents\arrows\gshadeA.wmf"
  30.         imgGreenArrowHead = "C:\Users\Lailita\Documents\arrows\greenAh.wmf"
  31.         imgGreenArrowHeadShade = "C:\Users\Lailita\Documents\arrows\gshadeAh.wmf"
  32.         imgGreenBox = "C:\Users\Lailita\Documents\arrows\greenB.wmf"
  33.         imgGreenBoxShade = "C:\Users\Lailita\Documents\arrows\gshadeB.wmf"
  34.         imgGreenArrowSmall = "C:\Users\Lailita\Documents\arrows\greenArrowSmall.wmf"
  35.         imgGreenArrowSmallShaded = "C:\Users\Lailita\Documents\arrows\greenArrowSmallShaded.wmf"
  36.  
  37.         imgRedArrowTail = "C:\Users\Lailita\Documents\arrows\redA.wmf"
  38.         imgRedArrowHead = "C:\Users\Lailita\Documents\arrows\redAh.wmf"
  39.         imgRedBox = "C:\Users\Lailita\Documents\arrows\redB.wmf"
  40.         imgRedArrowSmall = "C:\Users\Lailita\Documents\arrows\redArrowSmall.wmf"
  41.  
  42.         imgGreyArrowTail = "C:\Users\Lailita\Documents\arrows\greyA.wmf"
  43.         imgGreyArrowHead = "C:\Users\Lailita\Documents\arrows\greyAh.wmf"
  44.         imgGreyBox = "C:\Users\Lailita\Documents\arrows\greyB.wmf"
  45.         imgGreyArrowSmall = "C:\Users\Lailita\Documents\arrows\greyArrowSmall.wmf"
  46.  
  47.  
  48.  
  49.     'disconnect subform from mainform control                               'OK!
  50.     '****************************************
  51.     ctr = "ctrCanvas"                                                       'constant
  52.     Forms!Calendar!(ctr).SourceObject = ""                                  'OK!
  53.  
  54.  
  55.     'open form
  56.     '*********
  57.     strForms = "frmCanvas"                                                  'constant
  58.     DoCmd.OpenForm strForms, acDesign, , , acFormEdit, acHidden
  59.  
  60.  
  61.     'delete all controls in all subforms                                    'OK!
  62.     '***********************************
  63.     Do While Forms(strForms).Controls.Count > 0
  64.         DeleteControl strForms, Forms(strForms).Controls(0).Name
  65.     Loop
  66.  
  67.  
  68.     'draw: background vertical lines
  69.     '*******************************
  70.     If q = 0 Then
  71.         p = 1
  72.         Do Until p = 14
  73.             Set cBackLinev = CreateControl(strForms, acLine)
  74.             With cBackLinev
  75.                 .Width = 0
  76.                 .Height = 420 * rCount
  77.                 .Left = p * 1217 + 710
  78.                 .Top = 0
  79.                 .BorderColor = RGB(240, 240, 240)
  80.             End With
  81.             p = p + 1
  82.         Loop
  83.     End If
  84.  
  85.  
  86.  
  87.     '***********************
  88.     '* draw: for each room *
  89.     '***********************
  90.     q = -1                                              'line relativity
  91.     Do Until RecSetRooms.EOF                            '1st DO LOOP / Do until all rooms have been processed
  92.         q = q + 1
  93.         roomNow = RecSetRooms.Fields("RoomNumber").Value
  94.         roomClean = RecSetRooms.Fields("Clean").Value
  95.         vOffset = 420 * q
  96.         rmTop = vOffset + 30
  97.  
  98.  
  99.         'draw: background horisontal lines for each room
  100.         '***********************************************
  101.         If q Mod 2 = 0 Then bckStl = 1 Else bckStl = 0
  102.         Set cBackLineh = CreateControl(strForms, acLabel)
  103.             With cBackLineh
  104.                 .BackStyle = bckStl
  105.                 .Width = 17100
  106.                 .Height = 400
  107.                 .Left = 720
  108.                 .Top = rmTop - 15
  109.                 .BorderStyle = 0
  110.                 .BorderWidth = 0
  111.                 .BackColor = RGB(240, 240, 240)
  112.             End With
  113.  
  114.  
  115.         'draw: room-number box
  116.         '*********************
  117.         Set cRoomBox = CreateControl(strForms, acLabel)
  118.             With cRoomBox
  119.                 .BackStyle = 1
  120.                 .Width = 580
  121.                 .Height = 380
  122.                 .Left = 50
  123.                 .Top = rmTop
  124.                 .Caption = roomNow
  125.                 .BorderStyle = 1
  126.                 .FontSize = 14
  127.                 .TextAlign = 2
  128.                 .BorderWidth = 1
  129.                 .FontWeight = 700
  130.                 '.BackColor = RGB(0, 255, 0)
  131.             End With
  132.         RecSetRooms.MoveNext
  133.  
  134.     Loop
  135.  
  136.  
  137. '------------------------------------------------------
  138.  
  139.  
  140.     'draw: bookings
  141.     '**************
  142.  
  143.     Do Until RecSetBookings.EOF
  144.  
  145.         'finding variables
  146.         '*****************
  147.         q = RecSetBookings("RoomID").Value - 1
  148.         vOffset = 420 * q
  149.         arTop = vOffset + 40
  150.  
  151.  
  152.         slBeg = RecSetBookings.Fields("SlotBegin").Value
  153.         slEnd = RecSetBookings.Fields("SlotEnd").Value
  154.  
  155.             bolDrawTail = True
  156.             bolDrawHead = True
  157.             bolDrawBody = True
  158.             bolDrawLabel = True
  159.             bolDrawSmall = False
  160.  
  161.         leftpos = (slBeg - dstart) * 1217 + 730
  162.         'MsgBox leftpos
  163.             If leftpos < 720 Then
  164.                 bolDrawTail = False
  165.                 leftpos = 720
  166.                 leftposbox = 730
  167.                 wdthBox = (((slEnd - dstart) * 1217) - 370)
  168.                 If wdthBox < 0 Then wdthBox = 0
  169.                 If wdthBox > 0 Then wdthBox = ((slEnd - dstart) * 1217) - 180
  170.                 leftposhead = leftpos + wdthBox
  171.                 MsgBox leftposhead & ", " & wdthBox
  172.                 If leftposhead < 720 Then leftposhead = 730 + wdthBox
  173.  
  174.             Else
  175.                 bolDrawTail = True
  176.                 leftposbox = leftpos + 180
  177.                 wdthBox = (((slEnd - slBeg) * 1217) - 370)
  178.                 If wdthBox < 0 Then wdthBox = 0
  179.                 leftposhead = leftpos + ((slEnd - slBeg) * 1217) - 190
  180.             End If
  181.  
  182.         rcBookID = RecSetBookings.Fields("Bookings.ID").Value
  183.         bConf = RecSetBookings.Fields("Confirmed").Value
  184.         chinDate = RecSetBookings.Fields("CheckInDate").Value
  185.         choutDate = RecSetBookings.Fields("CheckOutDate").Value
  186.         bChkIn = RecSetBookings.Fields("CheckedIn").Value
  187.         bChkOut = RecSetBookings.Fields("CheckedOut").Value
  188.  
  189.        ' MsgBox slBeg & ", " & dstart
  190.  
  191.         If bChkIn = True And bChkOut = False Then colorArrow = 1                                'green
  192.         If chinDate = Date And bChkIn = False Then colorArrow = 2                               'green shade
  193.         If chinDate > Date And bConf = True Then colorArrow = 3                                 'yellow
  194.         If chinDate > Date And bConf = False Then colorArrow = 4                                'yellow shade
  195.         If choutDate = Date And bChkOut = False Or choutDate < Date Then colorArrow = 5         'red
  196.         If bChkOut = True Then colorArrow = 6                                                   'grey
  197.         '+ black for out of order
  198.  
  199.  
  200.         Select Case colorArrow
  201.             Case 1 'green
  202.                 tailImage = imgGreenArrowTail
  203.                 headImage = imgGreenArrowHead
  204.                 bodyImage = imgGreenBox
  205.                 lblForeColor = RGB(255, 255, 255)
  206.                 smallImage = imgGreenArrowSmall
  207.             Case 2 'green shaded
  208.                 tailImage = imgGreenArrowTailShade
  209.                 headImage = imgGreenArrowHeadShade
  210.                 bodyImage = imgGreenBoxShade
  211.                 lblForeColor = RGB(255, 255, 255)
  212.                 smallImage = imgGreenArrowSmallShaded
  213.             Case 3 'yellow
  214.                 tailImage = imgYellowArrowTail
  215.                 headImage = imgYellowArrowHead
  216.                 bodyImage = imgYellowBox
  217.                 lblForeColor = RGB(255, 255, 255)
  218.                 smallImage = imgYellowArrowSmall
  219.             Case 4 'yellow shaded
  220.                 tailImage = imgYellowArrowTailShade
  221.                 headImage = imgYellowArrowHeadShade
  222.                 bodyImage = imgYellowBoxShade
  223.                 lblForeColor = RGB(0, 0, 0)
  224.                 smallImage = imgYellowArrowSmallShaded
  225.             Case 5 'red
  226.                 tailImage = imgRedArrowTail
  227.                 headImage = imgRedArrowHead
  228.                 bodyImage = imgRedBox
  229.                 lblForeColor = RGB(255, 255, 255)
  230.                 smallImage = imgRedArrowSmall
  231.             Case 6 'grey
  232.                 tailImage = imgGreyArrowTail
  233.                 headImage = imgGreyArrowHead
  234.                 bodyImage = imgGreyBox
  235.                 lblForeColor = RGB(255, 255, 255)
  236.                 smallImage = imgGreyArrowSmall
  237.             Case 7 'black
  238.  
  239.         End Select
  240.  
  241.         If IsNull(slEnd) Then MsgBox "slEnd is null!?"
  242.         If IsNull(slBeg) Then MsgBox "slBeg is null!?"
  243.  
  244.         If slEnd - slBeg < 0.33 Then
  245.             MsgBox "< 8 hours"
  246.             tailImage = shortImage
  247.             bolDrawTail = False
  248.             bolDrawHead = False
  249.             bolDrawBody = False
  250.             bolDrawLabel = False
  251.             bolDrawSmall = True
  252.         End If
  253.  
  254.  
  255.         'draw: small arrow
  256.         '**********
  257.         If bolDrawSmall = True Then
  258.         Set cImg1 = CreateControl(strForms, acImage)
  259.          With cImg1
  260.              .BackStyle = 0
  261.              'msgWidth = (slEnd - slBeg) * 1217
  262.              'MsgBox "msgW=" & msgWidth & ", slEnd=" & slEnd & ", slBeg=" & slBeg & ", ID=" & rcBookID
  263.              .Width = (slEnd - slBeg) * 1217
  264.              .Height = 360
  265.              .Left = leftpos
  266.              .Top = arTop
  267.              .ControlTipText = rcBookID
  268.              .OnClick = ""
  269.              .Picture = smallImage
  270.              .SizeMode = acOLESizeStretch
  271.             '.PictureAlignment = 0
  272.          End With
  273.         End If
  274.  
  275.         'draw: tail
  276.         '**********
  277.         If bolDrawTail = True Then
  278.         Set cImg1 = CreateControl(strForms, acImage)
  279.          With cImg1
  280.              .BackStyle = 0
  281.              .Width = 432
  282.              .SizeMode = 0
  283.              .Height = 360
  284.              .Left = leftpos
  285.              .Top = arTop
  286.              .ControlTipText = rcBookID
  287.              .OnClick = ""
  288.              .Picture = tailImage
  289.              .SizeMode = acOLESizeZoom
  290.              .PictureAlignment = 0
  291.          End With
  292.         End If
  293.  
  294.         'draw: head
  295.         '**********
  296.         If bolDrawHead = True Then
  297.         Set cImg2 = CreateControl(strForms, acImage)
  298.             With cImg2
  299.                 .BackStyle = 0
  300.                 .Width = 400
  301.                 .Height = 360
  302.                 .SizeMode = 0
  303.                 .ControlTipText = rcBookID
  304.                 .Left = leftposhead
  305.                 '.Left = leftPos + 300
  306.                 .Top = arTop
  307.                 .Picture = headImage
  308.                 .SizeMode = acOLESizeZoom
  309.                 .PictureAlignment = 0
  310.                 .BorderStyle = 0
  311.             End With
  312.         End If
  313.  
  314.         'draw: body
  315.         '***************
  316.         If bolDrawBody = True Then
  317.         Set cImg3 = CreateControl(strForms, acImage)
  318.            With cImg3
  319.                 .BackStyle = 0
  320.                 .Width = wdthBox
  321.                 .Height = 360
  322.                 .SizeMode = 0
  323.                 .ControlTipText = rcBookID
  324.                 .Left = leftposbox
  325.                 '.Left = leftPos + 300
  326.                 .Top = arTop
  327.                 .Picture = bodyImage
  328.             End With
  329.         End If
  330.  
  331.         'draw: label
  332.         '**********
  333.         If bolDrawLabel = True Then
  334.         Set cLbl1 = CreateControl(strForms, acLabel)
  335.            With cLbl1
  336.                .Visible = True
  337.                .BackColor = RGB(255, 194, 14)
  338.                .ForeColor = lblForeColor
  339.                .FontWeight = 900
  340.                .TopMargin = 34
  341.                .TextAlign = 2
  342.                .BackStyle = 0
  343.                .FontWeight = 900
  344.                .Top = arTop + 10
  345.                .Height = 330
  346.                .Left = leftposbox
  347.                .Width = wdthBox
  348.                '.Width = 200
  349.                .Caption = rcBookID
  350.            End With
  351.         End If
  352.  
  353.         RecSetBookings.MoveNext                                         'moving to next booking
  354.  
  355.     Loop
  356.  
  357.  
  358.  
  359.  
  360.  
  361.     'close & reconnect
  362.     '*****************
  363.     strForms = "frmCanvas"
  364.     DoCmd.Close acForm, strForms, acSaveYes
  365.     Forms!Calendar!(ctr).SourceObject = "frmCanvas"
  366.  
  367.  
  368.     'refresh
  369.     '*******
  370.    ' DoCmd.Requery
  371.     Forms!Calendar.Refresh
  372.     Forms!Calendar.Repaint
  373.  
  374.  
  375.     'clean up
  376.     '********
  377.     DoCmd.Echo (True)
  378.     Set RecSetBookings = Nothing
  379.     Set RecSetRooms = Nothing
  380.  
  381. End Function
  382.  
Apr 28 '11 #2
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.
Apr 28 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.OpenRecordset("qBookings", dbOpenSnapshot, dbOpenForwardOnly)
The Syntax for the Openrecordset function is:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 28 '11 #4
thanks smiley!

so what do I do with the two types, add & or + in between?
Apr 29 '11 #5
TheSmileyCoder
2,321 Expert Mod 2GB
They are two different types. Its a case of "one or the other", you cannot select both (as far as I know/understand).
Apr 29 '11 #6
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!
Apr 29 '11 #7
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!
Apr 29 '11 #8
TheSmileyCoder
2,321 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 29 '11 #9
Thanks again smiley!

SQL of query (qBookings) is
Expand|Select|Wrap|Line Numbers
  1. SELECT qBase.*, DatePointer.RStartDate, CDbl([CheckInDate]+[CheckInTime]) AS SlotBegin, CDbl([CheckOutDate]+[CheckOutTime]) AS SlotEnd
  2. FROM DatePointer, qBase
  3. 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])));
  4.  
and for qBase
Expand|Select|Wrap|Line Numbers
  1. SELECT Bookings.*, Contacts.*
  2. 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!
Apr 29 '11 #10
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Apr 29 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.