Compile Error with Form Code

[paulwoods23]

I have a form which, after selecting a "Staff" record and a year from two seperate comb boxes should list all sickness records for the selections in a list box.

I am using the following code to do this -

Private Sub cmbYearSelect_AfterUpdate()
    Dim StartDate As Date
    Dim EndDate As Date
    If cmbYearSelect = "2002" Then
    StartDate = "01/01/2002" And EndDate = "31/12/2002"
    End If
    If IsNull(cmbYearSelect) And IsNull(cmbNameSearch) Then
        lstSicknessCount.RowSource = ""
        lstSicknessCount.Requery
    Else
        Dim strSQL As String
        strSQL = "SELECT tblSickness.StartDate, tblSickness.ReturnDate, tblSicknessTypes.SicknessType, " _
         & "tblSickness.TotalDays FROM (tblStaffDetails INNER JOIN tblSickness ON tblStaffDetails.StaffNumber " _
         & "= tblSickness.StaffNumber) INNER JOIN tblSicknessTypes ON tblSickness.ID = tblSicknessTypes.ID " _
         & &quot;WHERE (((tblSickness.StartDate)>=&quot; & StartDate & &quot;) AND ((tblSickness.ReturnDate)<=&quot; & EndDate & &quot;));&quot;
        lstSicknessCount.RowSourceType = &quot;Table/Query&quot;
        lstSicknessCount.RowSource = strSQL
        lstSicknessCount.Requery
    End If
    Dim Recset As DAO.Recordset
    Dim SicknessQdf As QueryDef
    Dim Dbs As Database
    Dim NoOfDays As Long
    Set Dbs = CurrentDb()
    Set SicknessQdf = Dbs.CreateQueryDef(&quot;&quot;, strSQL)
    Set Recset = SicknessQdf.OpenRecordset
    With Recset

    If Not Recset.BOF And Not Recset.EOF Then
    Recset.MoveFirst
    Recset.MoveLast
    NoOfDays = .RecordCount
    Else
    NoOfDays = 0
    End If

    Recset.Close
    Set Recset = Nothing
    Set SicknessQdf = Nothing
    End With
    Me.NoDays = NoOfDays
End Sub

(the bit at the start where I convert a year to a start and end date is not complete yet!)

When I run this I get the following error -
Compile Error:
User-defined type not defined

The error I get highlights the following -
Recset As DAO.Recordset

I have used this code in a similar form in a seperate database and it works fine. It's not my code and I'm not in contact with the person who wrote it, does anyone have any ideas why I get this error?

 

[Junior1544]

Make sure you have a reference to DAO in your vba references...

(Under the tools menu, then references...)

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[scriverb]

There is a missing .ddl reference made in this database. open a form in design view and click the code button. Then select from the menu Tools then references. You will see a list of library references. There will be some checked and many that are not. One that is checked will say MISSING: in front of it. This reference is selected but missing. Most likely it will be the Microsoft x.xxDAO Object Library. You will have to provide this library for the code to work.
Bob Scriver

 

[paulwoods23]

Solved. Thanks to both of you.

 

[stvnkrs]

I'm having this same kind of problem in Access 97, but I can't seem to find the Reference that I need. I get the User-Defined Type Not Defined error on:

Dim oSession As MAPI.Session

Under Tools, References, I do not show any of the refrences as MISSING. Does anyone know what reference is needed for using MAPI? Thanks

 

[scriverb]

With ACCESS 97 I think you need to download and register the CDO library from MS. The following link should provide you with the library reference.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q171440

Good luck.

Bob Scriver