Compile Error

[Double A]

I have created a form with an embedded subform that will be used to add/edit
records into 2 tables. One is a table of categories that is used as a look
up table. The other table is a set of steps that are used in a drop down
menu. In my form, I am able to add new records to these tables. However
when I do and click enter or click off the field I am directed to the
following code with an error telling me there is a compile error.

Private Sub Info_After Update
Dim strSQL As String
Dim db As DAO.Database

StrSQL = "tblWorkLineItem"
Set tblWorkLineItem.Info = Me.Category

If Not IsNull (Me.Category) Then
db.Execute strSQL, db FailOnError
End If
Set db = Nothing

End Sub

The error highlights the 3rd line of code "db As DAO.Database"

I did not recall entering this code specifically. Please help me understand
why I am getting this error.

Thanks.

 

[Richard]

Hi Double A

You could try and comment that line out like this.

'Dim db As DAO.Database

and replace it with this.

Dim dbs As Database

Good luck

 

[Dirk Goldgar]

I have created a form with an embedded subform that will be used to
add/edit
records into 2 tables. One is a table of categories that is used as a
look
up table. The other table is a set of steps that are used in a drop down
menu. In my form, I am able to add new records to these tables. However
when I do and click enter or click off the field I am directed to the
following code with an error telling me there is a compile error.

Private Sub Info_After Update
Dim strSQL As String
Dim db As DAO.Database

StrSQL = "tblWorkLineItem"
Set tblWorkLineItem.Info = Me.Category

If Not IsNull (Me.Category) Then
db.Execute strSQL, db FailOnError
End If
Set db = Nothing

End Sub

The error highlights the 3rd line of code "db As DAO.Database"

I did not recall entering this code specifically. Please help me
understand
why I am getting this error.

Is that a complete copy and paste of the subrouting code? There ought to be
a line that sets the db object, along the lines of ...

Set db = CurrentDb

.... but I don't see one. But that absence would cause a run-time error, not
a compile error. The compile error you're getting seems likely to have been
caused by a missing reference to the DAO Object Library. In the VB Editor
window, click Tools -> References..., locate Microsoft DAO 3.6 Object
Library in the list of references, and put a check mark in the box next to
it. (That's assuming you're using Access 2000, 2002, or 2003.)

If you already have the DAO reference checked, then there's some other
problem. Make sure you don't have any reference marked MISSING. Also,
please post the exact error message you're getting.

 

[BruceM]

In the VBA editor, click Tools >> References and be sure Microsoft DAO 3.6
Object Library is selected. It will be a different number if you have
Access 2007 or pre-2000. For more information about references see:
http://allenbrowne.com/ser-38.html

 

[Douglas J. Steele]

That's unlikely to do any good, Richard.

Dirk's advice (ensure that a reference to the Microsoft DAO 3.6 Object
Library) is almost certainly the correct answer.

 

[Double A]

The code below is the entire code that this error refers to. the exact error
message is "Compile error: User-defined type not defined."

I checked the DAO 3.6 Object Library as you suggested. However, when I add
anything to this form and try to save it or click away from that record I now
get the following error: "Run-time error '424': Object required."

Trying to think ahead given what you mentioned in your reply, I changed
Set db = Nothing
to
Set db = CurrentDb

I received the same run-time error.

thoughts?

 

[Douglas J. Steele]

Dirk wasn't suggesting that you change

Set db = Nothing

to

Set db = CurrentDb()

He was telling you that you need to add the Set db = CurrentDb() statement
to your code before you used the db object:

Private Sub Info_After Update()
Dim strSQL As String
Dim db As DAO.Database

strSQL = "tblWorkLineItem"
Set tblWorkLineItem.Info = Me.Category

Set db = CurrentDb()

If Not IsNull (Me.Category) Then
db.Execute strSQL, db FailOnError
End If
Set db = Nothing

End Sub

That having been said, that's still not going to work. For one thing, this
line would appear to be invalid:

Set tblWorkLineItem.Info = Me.Category

And trying to execute a SQL statement that simply consists of
tblWorkLineItem won't work. The Execute method requires the SQL of an Action
query (INSERT INTO, UPDATE, DELETE, SELECT ... INTO)

If what you're trying to do is set the Info field in table tblWorkLineItem
to the value currently in Me.Category, strSQL needs to be either an UPDATE
statement (if you're trying to change the field value of an existing row) or
an INSERT INTO statement if you're trying to add a new row.