Data Type Mismatch Error in VBA code for row source type

[PSUAlumni87]

I am a programmer who has been using Access for a few years. I am a few steps above the casual user, but still very new to all the code. I have written the follwong code as a row souce type to fill a list box on a form I created.
I am getting a data mismatch error. Any clues? Any help at all would be appreciated. I work from homw and have no other programmers to bounce Ideas off of. when I hit a wall like this I am stalled until I can figure it out.

Thanks for any help anyone can give me.

Function namestodate(C As Control, ID, row, col, action)
Static NameofSite As Recordset
Dim SQLText2
Select Case action
Case acLBInitialize
SQLText2 = "SELECT DISTINCT [Site Code and Name Table].SiteName, [Tower Data Table].InspDate, [Tower Data Table].SiteID " & _
"FROM [Tower Data Table] LEFT JOIN [Site Code and Name Table] ON [Tower Data Table].SiteID = [Site Code and Name Table].SiteID " & _
"ORDER BY [Site Code and Name Table].SiteName, [Tower Data Table].InspDate;"

'THE FOLLOWING LINE IS WHERE I AM GETTING THE ERROR
Set NameofSite = CurrentDb.OpenRecordset(SQLText2)
namestodate = True
Case acLBOpen
namestodate = Timer
Case acLBGetRowCount
NameofSite.MoveLast
namestodate = NameofSite.RecordCount
NameofSite.MoveFirst
Case acLBGetColumnCount
namestodate = 1
Case acLBGetColumnWidth
namestodate = -1
Case acLBGetValue
'If row = 0 Then
' CompanyNames = &quot;<All>&quot;
'Else
NameofSite.MoveFirst
NameofSite.Move row
namestodate = NameofSite![SiteName]

'End If
Case acLBGetFormat
Case acLBEnd
Case acLBClose
End Select
End Function

 

[CPUburn]

hi,

dunno for sure, works with me, but just try

Static NameofSite As DAO.Recordset

hth,

the kid

 

[Guest_imported]

I tried the DAO.recordset command suggested by KarateKid - Function would not compile and it gave me a user-defined type not defined error.


Does this make any sense?

Thanks for the help!

 

[PSUAlumni87]

I found an additonal problem that I am sure is related.

I tried to rewrite the code to dim a variable called DBS as database then set DBS=currentdb().

I got the same user-type not defined error.

It iwll not allow me to dim anything as database.

What in the heck have a done wrong?@!

 

[CPUburn]

hi,

have you checked your references?

you should have ms DAO 3.6 object library

the kid