Compile error with code for a List

[Amit]

Hi there,

I am getting a compile error that says "user-defined type
not defined" with the following code:

==================================
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim varItem As Variant
Dim strSql As String
Dim db As Database <-----error on this line

Set db = CurrentDb()

For Each varItem In Me.List2.ItemsSelected
strSql = "Insert Into tblPersonColor (PersonID,
ColorID) Values (" & _
Me.ID & ",'" & Me.List2.ItemData(varItem)
& "')"
db.Execute strSql
Next varItem
Set db = Nothing

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
======================================

This code is in the "On Click" event for a button to
select items from a list. If someone can help me with
this, I'll appreciate that.

Tx.

-Amit

 

[Gary Miller]

Amit,

You didn't state what version of Access you have, but if it
is A2K or greater try a specific reference to DAO as it will
default to ADO...

Dim db as DAO.Database

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Amit]

Gary,

Thanks for the response. Access 2K.

Made the change you suggested, and I'm still getting the
same error.

One thing that I tried was to comment out the line (Dim
db..) and it worked fine. I checked the relevant table,
and the records were being inserted into it. Do you think
this may cause a problem?

-Amit

 

[Gary Miller]

Well, you should do a little more testing, but if it works,
it shouldn't be a problem. Another method is to use the
following where you don't need to declare the database....

DoCmd.RunSQL strSQL

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[Graham Mandeno]

Hi Amit

No, you should NOT remove the declaration. In fact, your module should have
Option Explicit at the top of it, so that EVERY variable must be explicitly
declared.

The problem is that the compiler doesn't know what a Database (or even a
DAO.Database) is. What you need to do is click on Tools>References, and
click the checkbox next to:
Microsoft DAO 3.? Object Library

If you are not using any ADO in your code, you can also uncheck the box next
to:
Microsoft ActiveX Data Objects2.? Library

Then click Debug>Compile <project> to check for errors in all of your code.

 

[Amit]

Graham,

I followed your suggestions. I'm not getting the compile
error any more. But now, the selected values from the list
are not being inserted into the table.....

Didn't change anything else, so I'm at a loss as to why
this has stopped working. Thanks for any help!

-Amit

-----Original Message-----
Hi Amit

No, you should NOT remove the declaration. In fact, your module should have
Option Explicit at the top of it, so that EVERY variable must be explicitly
declared.

The problem is that the compiler doesn't know what a Database (or even a
DAO.Database) is. What you need to do is click on Tools>References, and
click the checkbox next to:
Microsoft DAO 3.? Object Library

If you are not using any ADO in your code, you can also uncheck the box next
to:
Microsoft ActiveX Data Objects2.? Library

Then click Debug>Compile <project> to check for errors in all of your code.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Gary,

Thanks for the response. Access 2K.

Made the change you suggested, and I'm still getting the
same error.

One thing that I tried was to comment out the line (Dim
db..) and it worked fine. I checked the relevant table,
and the records were being inserted into it. Do you think
this may cause a problem?

-Amit
if
it

.

 

[Graham Mandeno]

Hi Amit

Forming a correct declaration should not affect anything else. I suggest
you modify your Execute line to:
db.Execute strSql, dbFailOnError
and see what happens.

If it doesn't throw up an error on that line, try setting a breakpoint on
that line to confirm that the code is being executed. If not, then step
through it and find out why it's not getting there.

If the Execute does throw up an error, then you could examine strSql in the
debug window and if you can't see the problem, copy it and paste it into the
SQL window of a new query. Try to run the query and it might give you some
more information about the error.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I followed your suggestions. I'm not getting the compile
error any more. But now, the selected values from the list
are not being inserted into the table.....

Didn't change anything else, so I'm at a loss as to why
this has stopped working. Thanks for any help!

-Amit

-----Original Message-----
Hi Amit

No, you should NOT remove the declaration. In fact, your module should have
Option Explicit at the top of it, so that EVERY variable must be explicitly
declared.

The problem is that the compiler doesn't know what a Database (or even a
DAO.Database) is. What you need to do is click on Tools>References, and
click the checkbox next to:
Microsoft DAO 3.? Object Library

If you are not using any ADO in your code, you can also uncheck the box next
to:
Microsoft ActiveX Data Objects2.? Library

Then click Debug>Compile <project> to check for errors in all of your code.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Gary,

Thanks for the response. Access 2K.

Made the change you suggested, and I'm still getting the
same error.

One thing that I tried was to comment out the line (Dim
db..) and it worked fine. I checked the relevant table,
and the records were being inserted into it. Do you think
this may cause a problem?

-Amit

-----Original Message-----
Amit,

You didn't state what version of Access you have, but if
it
is A2K or greater try a specific reference to DAO as it
will
default to ADO...

Dim db as DAO.Database

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message Hi there,

I am getting a compile error that says "user-defined
type
not defined" with the following code:

==================================
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim varItem As Variant
Dim strSql As String
Dim db As Database <-----error on this line

Set db = CurrentDb()

For Each varItem In Me.List2.ItemsSelected
strSql = "Insert Into tblPersonColor (PersonID,
ColorID) Values (" & _
Me.ID & ",'" & Me.List2.ItemData (varItem)
& "')"
db.Execute strSql
Next varItem
Set db = Nothing

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
======================================

This code is in the "On Click" event for a button to
select items from a list. If someone can help me with
this, I'll appreciate that.

Tx.

-Amit


.

.

 

[John S]

Could be me, but I had problems with the .itemselected property, and ended
up using .selected property instead

The code fragment looks like this:

For x = 0 To lst.ListCount - 1
If lst.Selected(x) = True Then
....

This seemed to work fine for me. Again, it could have been me with the
..itemselected

John S
Aylmer, PQ

Hi Amit

Forming a correct declaration should not affect anything else. I suggest
you modify your Execute line to:
db.Execute strSql, dbFailOnError
and see what happens.

If it doesn't throw up an error on that line, try setting a breakpoint on
that line to confirm that the code is being executed. If not, then step
through it and find out why it's not getting there.

If the Execute does throw up an error, then you could examine strSql in the
debug window and if you can't see the problem, copy it and paste it into the
SQL window of a new query. Try to run the query and it might give you some
more information about the error.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I followed your suggestions. I'm not getting the compile
error any more. But now, the selected values from the list
are not being inserted into the table.....

Didn't change anything else, so I'm at a loss as to why
this has stopped working. Thanks for any help!

-Amit

-----Original Message-----
Hi Amit

No, you should NOT remove the declaration. In fact, your module should have
Option Explicit at the top of it, so that EVERY variable must be explicitly
declared.

The problem is that the compiler doesn't know what a Database (or even a
DAO.Database) is. What you need to do is click on Tools>References, and
click the checkbox next to:
Microsoft DAO 3.? Object Library

If you are not using any ADO in your code, you can also uncheck the box next
to:
Microsoft ActiveX Data Objects2.? Library

Then click Debug>Compile <project> to check for errors in all of your code.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Gary,

Thanks for the response. Access 2K.

Made the change you suggested, and I'm still getting the
same error.

One thing that I tried was to comment out the line (Dim
db..) and it worked fine. I checked the relevant table,
and the records were being inserted into it. Do you think
this may cause a problem?

-Amit

-----Original Message-----
Amit,

You didn't state what version of Access you have, but if
it
is A2K or greater try a specific reference to DAO as it
will
default to ADO...

Dim db as DAO.Database

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message Hi there,

I am getting a compile error that says "user-defined
type
not defined" with the following code:

==================================
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim varItem As Variant
Dim strSql As String
Dim db As Database <-----error on this line

Set db = CurrentDb()

For Each varItem In Me.List2.ItemsSelected
strSql = "Insert Into tblPersonColor (PersonID,
ColorID) Values (" & _
Me.ID & ",'" & Me.List2.ItemData (varItem)
& "')"
db.Execute strSql
Next varItem
Set db = Nothing

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
======================================

This code is in the "On Click" event for a button to
select items from a list. If someone can help me with
this, I'll appreciate that.

Tx.

-Amit


.



.

 

[Amit]

-----Original Message-----
Hi Amit

Forming a correct declaration should not affect anything else. I suggest
you modify your Execute line to:
db.Execute strSql, dbFailOnError
and see what happens.

That made it work!! Thanks for all your help.

-Amit

If it doesn't throw up an error on that line, try setting a breakpoint on
that line to confirm that the code is being executed. If not, then step
through it and find out why it's not getting there.

If the Execute does throw up an error, then you could examine strSql in the
debug window and if you can't see the problem, copy it and paste it into the
SQL window of a new query. Try to run the query and it might give you some
more information about the error.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

I followed your suggestions. I'm not getting the compile
error any more. But now, the selected values from the list
are not being inserted into the table.....

Didn't change anything else, so I'm at a loss as to why
this has stopped working. Thanks for any help!

-Amit

-----Original Message-----
Hi Amit

No, you should NOT remove the declaration. In fact,

your
module should have

Option Explicit at the top of it, so that EVERY

variable
must be explicitly

declared.

The problem is that the compiler doesn't know what a Database (or even a
DAO.Database) is. What you need to do is click on Tools>References, and
click the checkbox next to:
Microsoft DAO 3.? Object Library

If you are not using any ADO in your code, you can also uncheck the box next
to:
Microsoft ActiveX Data Objects2.? Library

Then click Debug>Compile <project> to check for errors

in
all of your code.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Gary,

Thanks for the response. Access 2K.

Made the change you suggested, and I'm still getting the
same error.

One thing that I tried was to comment out the line (Dim
db..) and it worked fine. I checked the relevant table,
and the records were being inserted into it. Do you think
this may cause a problem?

-Amit

-----Original Message-----
Amit,

You didn't state what version of Access you have,

but
if

it
is A2K or greater try a specific reference to DAO as it
will
default to ADO...

Dim db as DAO.Database

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
message [email protected]...
Hi there,

I am getting a compile error that says "user- defined
type
not defined" with the following code:

==================================
Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

Dim varItem As Variant
Dim strSql As String
Dim db As Database <-----error on this line

Set db = CurrentDb()

For Each varItem In Me.List2.ItemsSelected
strSql = "Insert Into tblPersonColor (PersonID,
ColorID) Values (" & _
Me.ID & ",'" & Me.List2.ItemData (varItem)
& "')"
db.Execute strSql
Next varItem
Set db = Nothing

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click
End Sub
======================================

This code is in the "On Click" event for a button to
select items from a list. If someone can help me with
this, I'll appreciate that.

Tx.

-Amit


.



.

.