Error: User-Defined Type Not Defined

[Earl Anderson]

In A97 on WinXP, I'm trying to use a KB function to provide the next
sequential number in a custom counter (a table with two fields--
[NextAvailable] and [FromYear] ). When I run it, I get the "User-defined
type not defined" error on the third line of code (Dim rs As
ADODB.Recordset) I'm using below:

" Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long
Set rs = New ADODB.Recordset
rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

NextCounter = rs!NextAvailableCounter
If rs!FromYear < Year(Date) Then
rs!FromYear = Year(Date)
NextCounter = 0
End If

rs!NextAvailableCounter = NextCounter + 1
NextCounter = rs!NextAvailableCounter
rs.Update

MsgBox "Next available counter value is " & Str(NextCounter)

rs.Close

Set rs = Nothing

Next_Custom_Counter = NextCounter

Exit Function
Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function "

I know this has been asked and answered "1000 times", but I did check my
references which are:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft ADO Ext. 2.7 for DLL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Object Recordset 2.7 Library

I read the 'Help' file and the 'Reference' issue seemed to be the only one
of the three that was applicable to this code. Is there another reason I'm
getting this error or am I missing something regarding my References?

Thx for any help.
Earl

[Allen Browne]

Try MDAC 2.5:
http://support.microsoft.com/default.aspx?kbid=293312
2.7 does not include the JET drivers IIRC.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Earl Anderson" <is*****@rcn.com> wrote in message
news:40**********************@news.rcn.com...

In A97 on WinXP, I'm trying to use a KB function to provide the next
sequential number in a custom counter (a table with two fields--
[NextAvailable] and [FromYear] ). When I run it, I get the "User-defined
type not defined" error on the third line of code (Dim rs As
ADODB.Recordset) I'm using below:

" Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long
Set rs = New ADODB.Recordset
rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

NextCounter = rs!NextAvailableCounter
If rs!FromYear < Year(Date) Then
rs!FromYear = Year(Date)
NextCounter = 0
End If

rs!NextAvailableCounter = NextCounter + 1
NextCounter = rs!NextAvailableCounter
rs.Update

MsgBox "Next available counter value is " & Str(NextCounter)

rs.Close

Set rs = Nothing

Next_Custom_Counter = NextCounter

Exit Function
Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function "

I know this has been asked and answered "1000 times", but I did check my
references which are:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft ADO Ext. 2.7 for DLL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Object Recordset 2.7 Library

I read the 'Help' file and the 'Reference' issue seemed to be the only one
of the three that was applicable to this code. Is there another reason I'm getting this error or am I missing something regarding my References?

Thx for any help.
Earl

[Earl Anderson]

Went to 'References', checked "Microsoft Data Access Components Installed
Version", to include it with the other 5 checked references -- no luck; same
compile error. I went to the Microsoft site and tried to download it, but
installation was halted because it was "already installed". Could there be
a conflict between any of the 6 References? Short of starting over, is there
anything else I should be looking at/for in the References or code?

Thx...
Earl

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40**********************@freenews.iinet.net.a u...

Try MDAC 2.5:
http://support.microsoft.com/default.aspx?kbid=293312
2.7 does not include the JET drivers IIRC.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Earl Anderson" <is*****@rcn.com> wrote in message
news:40**********************@news.rcn.com...

In A97 on WinXP, I'm trying to use a KB function to provide the next
sequential number in a custom counter (a table with two fields--
[NextAvailable] and [FromYear] ). When I run it, I get the "User-defined type not defined" error on the third line of code (Dim rs As
ADODB.Recordset) I'm using below:

" Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim rs As ADODB.Recordset
Dim NextCounter As Long
Set rs = New ADODB.Recordset
rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

NextCounter = rs!NextAvailableCounter
If rs!FromYear < Year(Date) Then
rs!FromYear = Year(Date)
NextCounter = 0
End If

rs!NextAvailableCounter = NextCounter + 1
NextCounter = rs!NextAvailableCounter
rs.Update

MsgBox "Next available counter value is " & Str(NextCounter)

rs.Close

Set rs = Nothing

Next_Custom_Counter = NextCounter

Exit Function
Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End
End Function "

I know this has been asked and answered "1000 times", but I did check my
references which are:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft ADO Ext. 2.7 for DLL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Object Recordset 2.7 Library

I read the 'Help' file and the 'Reference' issue seemed to be the only one of the three that was applicable to this code. Is there another reason

I'm

getting this error or am I missing something regarding my References?

Thx for any help.
Earl

[rkc]

"Earl Anderson" <is*****@rcn.com> wrote in message
news:40**********************@news.rcn.com...

Went to 'References', checked "Microsoft Data Access Components Installed
Version", to include it with the other 5 checked references -- no luck; same compile error. I went to the Microsoft site and tried to download it, but
installation was halted because it was "already installed". Could there be a conflict between any of the 6 References? Short of starting over, is there anything else I should be looking at/for in the References or code?

Try unchecking
"Microsoft ActiveX Data Object Recordset 2.7 Library"
and checking
"Microsoft ActiveX Data Object 2.7 Library"

Aren't you going to have problems when you get to the line

rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset...

in an Access 97 application? CurrentProject?

[Earl Anderson]

That took care of the "User-defined type not defined" error and yes, I did
get the error on 'CurrentProject' and declared that also.

Thanks to both you and Allen.
Earl

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:ZV*******************@twister.nyroc.rr.com...

"Earl Anderson" <is*****@rcn.com> wrote in message
news:40**********************@news.rcn.com...

Went to 'References', checked "Microsoft Data Access Components Installed Version", to include it with the other 5 checked references -- no luck;

same

compile error. I went to the Microsoft site and tried to download it, but installation was halted because it was "already installed". Could there

be

a conflict between any of the 6 References? Short of starting over, is

there

anything else I should be looking at/for in the References or code?

Try unchecking
"Microsoft ActiveX Data Object Recordset 2.7 Library"
and checking
"Microsoft ActiveX Data Object 2.7 Library"

Aren't you going to have problems when you get to the line

rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset...

in an Access 97 application? CurrentProject?

[David W. Fenton]

"Earl Anderson" <is*****@rcn.com> wrote in
news:40**********************@news.rcn.com:

That took care of the "User-defined type not defined" error and
yes, I did get the error on 'CurrentProject' and declared that
also.

May I ask why you're using ADO in A97?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

"Earl Anderson" <is*****@rcn.com> wrote in message
news:40**********************@news.rcn.com...

That took care of the "User-defined type not defined" error and yes, I did
get the error on 'CurrentProject' and declared that also.

So the function is working now?
The problem is that there is no CurrentProject in an A97 application.
Your solution is probably to re-write the function you found so that
it uses DAO in place of ADO.