Complie error:  Variable not defined

Posted on 2008-11-11
Last Modified: 2013-11-27
Hello Experts:  

When I try opening my report I get this error:  Variable not defined on the 6th line at dbOpenSnapshot.  Also the first line is highlighted in yellow.  Here is the code example.

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordset("SELECT Min([Start Date]) AS MinOfStartDate " _
                & " FROM Projects", dbOpenSnapshot)
  If rs.RecordCount > 0 Then
    mdatEarliest = rs!MinOfStartDate
  End If
  Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([End Date]),CDate([End Date]),Null)) " _
                & "AS MaxOfEndDate FROM Projects", dbOpenSnapshot)
  If rs.RecordCount > 0 Then
    mdatLatest = rs!MaxOfEndDate
  End If
  mintDayDiff = DateDiff("d", mdatEarliest, mdatLatest)
  Me.txtMinStartDate.Caption = Format(mdatEarliest, "mm/dd/yyyy")
  Me.txtMaxEndDate.Caption = Format(mdatLatest, "mm/dd/yyyy")
  Set rs = Nothing
  Set db = Nothing
End Sub

Question by:SOUTHAMERICA70
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Have you checked your references to see if any are listed as missing?  Do you have a reference to DAO?
    LVL 6

    Expert Comment

    You may have trouble with mdatEarliest, mdatLatest, and mintDayDiff if you are using Option Explicit at the top of the code page. Also, if txtMinStartDate and txtMaxEndDate are text boxes as the naming implies you'll need to change the value instead of the caption.

    Author Comment


    I added the DAO.  But now I get a runtime error:  Type mismatch on the 4th line Set db = CurrentDb
    LVL 92

    Accepted Solution

    Change your declarations to:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Author Comment


    Thank you very much for your help.  That fixed my problem.  Many Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now