Compile Error - can't figure out why

[bigalpha]

So I have a db that was working great (it's split on a server into a fe and be).

After a weekend, I found out that all the code in the database had been erased. Like, every last bit. So, I restored the code from a back up file and all was well (or so I thought).

I have code that updates a table through a combo box. This code and form work perfectly in a back up database I have, but is not working in the restored database. I even copied the forms over. I keep receiving a 'Compile Error: User-definted type not defined'.

I can't figure out why the code isn't functioning in the restored db but works great in the back up. The first line gets highlighted in yellow and "Dim rst As DAO.Recordset" gets highlighted in blue.

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If MsgBox(strMsg, vbYesNo + vbQuestion, "Building_Number_PK") = vbNo Then
    Response = acDataErrContinue
Else
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblBuildingNumbers")
    With rst
        .AddNew
        ![Building_Numbers] = NewData
        .Update
        .Close
    End With
    Response = acDataErrAdded
End If
End Sub

 

[boblarson]

Make sure you have DAO Checked

If using Access 2003 or earlier - DAO 3.x Object Library

If using 2007 -
Microsoft Office 12.0 Access Database Engine Object Library

If using 2010 -

Microsoft Office 12.0 Access Database Engine Object Library


Or simply go with late binding and change the code to

Dim rst As Object
Dim db As Object

 

[bigalpha]

Well, since the backup db and the regular db sit side by side on my machine, doesnt that mean that the DAO shouldn't be the problem?

If I change to late binding, do I simply replace
Dim rst As DAO.Recordset
Dim db As DAO.Database

with

Dim rst As Object
Dim db As Object

 

[boblarson]

Well, since the backup db and the regular db sit side by side on my machine, doesnt that mean that the DAO shouldn't be the problem?

Has nothing to do with your machine and everything to do with what references are set in the individual database file.

If I change to late binding, do I simply replace
Dim rst As DAO.Recordset
Dim db As DAO.Database

with

Dim rst As Object
Dim db As Object

Yes, that is correct, but make sure there isn't an ADO reference set or you will find the "Set rst =" code to generate an error too.

 

[bigalpha]

How do I check the references to fix the weirdness?

 

[bigalpha]

I found how to check the references - a reference is missing in my non-working db.

It's the Microsoft Officd 12.0 Access database engine Object library

I don't know how my db lost all its code and fubar'd the references. Ugh.

 

[JANR]

Another way that do not need refrences set to DAO is to use a With block that points to a hidden refrence to DAO

ex:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
Dim strMsg As String

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If MsgBox(strMsg, vbYesNo + vbQuestion, "Building_Number_PK") = vbNo Then
    Response = acDataErrContinue
Else
    [COLOR="Red"]With CurrentDb.OpenRecordset("tblBuildingNumbers")
        .AddNew
        ![Building_Numbers] = NewData
        .Update
        .Close
    End With[/COLOR]
    Response = acDataErrAdded
End If
End Sub

JR

 

[bigalpha]

Thanks for the help and suggestions. I ended up using JR's suggestion.

I'm still not sure why my db got wiped of all code. I suspect foul play, but have no proof.