Same Working Module not working in new Database 1

[ImStuk]

I use the following code to create a unique ID on one of my databases. It currently works great. I have started a new database and want to use the same Module to create Unique ID numbers. I exported the Module, Table, Query, and Form that I use in my older database to my new database to create this unique ID. However, I am getting this error on the first line "Dim DB As Database":
Compile Error:
user-defined type not defined

Help Please! What am I not seeing. One thing is the old DB was designed in Access '97 then converted to 2000 (But Still Works) The new DB was created in 2000.

Here's the Module:

Option Compare Database
Option Explicit

Public Function NewOrderNum() As Long
On Error GoTo Err_NewOrderNum
Dim DB As Database
Dim tblNewOrderNumber As Recordset
Dim qryNewOrderNumberMax As Recordset

Const RiErr = 3000
Const LockErr = 3260
Const InUseErr = 3262
Const NumReTries = 20#

Dim NumLocks As Integer
Dim lngX As Long

Dim OrderNumber As Long
Dim lngOldOrderNumber As Long
Dim lngNewOrderNumber As Long
Dim lngBigOrderNumber As Long

Set DB = CurrentDb()

Set qryNewOrderNumberMax = DB.OpenRecordset("qryNewOrderNumberMax", dbDenyRead)
Set tblNewOrderNumber = DB.OpenRecordset("tblNewOrderNumber", dbDenyRead)

lngOldOrderNumber = qryNewOrderNumberMax!OrderNumber
lngNewOrderNumber = tblNewOrderNumber!OrderNumber
lngBigOrderNumber = lngNewOrderNumber

If (lngOldOrderNumber > lngBigOrderNumber) Then
lngBigOrderNumber = lngOldOrderNumber
End If

If (OrderNumber > lngBigOrderNumber) Then
lngBigOrderNumber = OrderNumber
End If

lngBigOrderNumber = lngBigOrderNumber + 1

With tblNewOrderNumber
.Edit
!OrderNumber = lngBigOrderNumber
.Update
End With
NewOrderNum = lngBigOrderNumber

NormExit:
Set DB = Nothing

Exit Function

Err_NewOrderNum:
If ((Err = InUseErr) Or (Err = LockErr) Or (Err = RiErr)) Then
NumLocks = NumLocks + 1
If (NumLocks < NumReTries) Then
For lngX = 1 To NumLocks ^ 2 * Int(Rnd * 20 + 5)
DoEvents
Next lngX
Resume Next
Else
End If
Else
MsgBox &quot;Error &quot; & Err.Number & &quot;: &quot; & Err.Description, vbOKOnly & vbCritical, &quot;Get Qi Number&quot;
GoTo NormExit
End If
End Function

 

[RoyVidar]

Hi!

To use the dao library in access 2000, in any module Tools | References, chekc the Microsoft DAO library.

Also, declare any database, querydefs and recordsets explicitly using the dao keyword:

[tt]dim rs as dao.recordset
dim db as dao.database[/tt]

HTH Roy-Vidar

 

[ImStuk]

Thank you. That worked, but now I am getting the following error on this line:

With tblNewOrderNumber
.Edit &quot;Error right here&quot;
!OrderNumber = lngBigOrderNumber
.Update
End With

Compile Error:
Method or data member not found

 

[RoyVidar]

Thanx for the star!

To me that reads like you have selected/checked the correct library, but still use the &quot;old&quot; declarations.

You will need to declare your recordsets like this:

[tt]Dim tblNewOrderNumber As DAO.Recordset
Dim qryNewOrderNumberMax As DAO.Recordset[/tt]

A little explanation:
Access 97 uses the DAO library as default recordset manipulation object library. Access 2000+ uses ADO. So when converting a 97 db to 2000, everything is as it was. When creating new databases, BOTH libraries are probably present when you've selected the DAO. Both libraries have Recordset object, and they share a lot of methods, but .Edit is DAO.

Post back if this doesn't solve the situation!

HTH Roy-Vidar

 

[ImStuk]

Wow...thanks. That was it! I was going crazy. I keep running into issue's with 2000 like this.