97 VB doesn't work in 2000 - Help please!

[John F Kappler]

I have recently had to convert an Access 97 database into Access 2000

The database has some very simple VB code in it that is used to add or
update records in a table. However, this code now comes up with
compile errors as show below (by <<comments>>):

I've tried looking in the Help files and on here plus looking in the
"MSAccess2000 Visual Basic For Applications" manual (which,
incidentally, has an example of code almost the same as mine on page
147!!), but can't find what I need to change the code to.

Any help would be greatly appreciated,

TIA

JohnK

1) Updating Code:

Private Sub Enter_Click()
On Error GoTo UD_Error

Dim MyDb As Database
<<This line produces an ERROR "User defined type not defined">>
Dim MySet As Recordset
Dim EStckNo As Long, EQty As Integer
Dim TStck As Integer, TSql As String

Set MyDb = DBEngine.Workspaces(0).Databases(0)
<<My DB not defined - for obvious reasons!>>
DoCmd.Hourglass True

EStckNo = Forms![Stock - Enter Orders]![StockNo]
EQty = Forms![Stock - Enter Orders]![QtyOrdered]

TSql = "SELECT * FROM [Stock Items] WHERE (([Stock Number] = " &
[EStckNo] & "));"

Set MySet = MyDB.OpenRecordset(TSql)
MySet.Edit
<<ERROR - "Method or data member not found">>

TStck = MySet![Qty Ordered]
TStck = TStck + EQty
MySet![Qty Ordered] = TStck

MySet.Update
MySet.Close

Finishing:
MyDB.Close

UD_Exit:
DoCmd.Hourglass False
Exit Sub

UD_Error:

StMessage = Str(Err.Number) & " " & Error(Err.Number) & " Stock -
Enter Orders Macro Error"
MsgBox StMessage
Resume UD_Exit

End Sub

2) Add Record Code:

Private Sub Enter_Click()
On Error GoTo ES_Error

Dim MyDb As Database
<<This line produces an ERROR "User defined type not defined">>
Dim MySet As Recordset
Dim EDate As String, EType As String, EStckNo As Long, EQty As
Integer
Dim TStck As Integer, TSql As String

Set MyDb = DBEngine.Workspaces(0).Databases(0)
<<My DB not defined - for obvious reasons!>>
DoCmd.Hourglass True

EDate = Forms![Sales Entry]![DateSold]
EType = Forms![Sales Entry]![SaleType]
EStckNo = Forms![Sales Entry]![StockNo]
EQty = Forms![Sales Entry]![QtySold]

' Add sales record to Stock Sales table...

Set MySet = MyDb.OpenRecordset("Stock Sales")
MySet.AddNew
MySet![Date] = EDate
MySet![Sales Type] = EType
MySet![Stock Number] = EStckNo
MySet![Qty Sold] = EQty
MySet.Update
MySet.Close

Finishing:
' MyDb.Close

ES_Exit:
DoCmd.Hourglass False
Exit Sub

ES_Error:

StMessage = Str(Err.Number) & " " & Error(Err.Number) & " Sales
Entry Macro Error"
MsgBox StMessage
Resume ES_Exit


End Sub

 

[Douglas J. Steele]

Database is a DAO object. By default, Access 2000 (and 2002) use ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP

I have recently had to convert an Access 97 database into Access 2000

The database has some very simple VB code in it that is used to add or
update records in a table. However, this code now comes up with
compile errors as show below (by <<comments>>):

I've tried looking in the Help files and on here plus looking in the
"MSAccess2000 Visual Basic For Applications" manual (which,
incidentally, has an example of code almost the same as mine on page
147!!), but can't find what I need to change the code to.

Any help would be greatly appreciated,

TIA

JohnK

1) Updating Code:

Private Sub Enter_Click()
On Error GoTo UD_Error

Dim MyDb As Database
<<This line produces an ERROR "User defined type not defined">>
Dim MySet As Recordset
Dim EStckNo As Long, EQty As Integer
Dim TStck As Integer, TSql As String

Set MyDb = DBEngine.Workspaces(0).Databases(0)
<<My DB not defined - for obvious reasons!>>
DoCmd.Hourglass True

EStckNo = Forms![Stock - Enter Orders]![StockNo]
EQty = Forms![Stock - Enter Orders]![QtyOrdered]

TSql = "SELECT * FROM [Stock Items] WHERE (([Stock Number] = " &
[EStckNo] & "));"

Set MySet = MyDB.OpenRecordset(TSql)
MySet.Edit
<<ERROR - "Method or data member not found">>

TStck = MySet![Qty Ordered]
TStck = TStck + EQty
MySet![Qty Ordered] = TStck

MySet.Update
MySet.Close

Finishing:
MyDB.Close

UD_Exit:
DoCmd.Hourglass False
Exit Sub

UD_Error:

StMessage = Str(Err.Number) & " " & Error(Err.Number) & " Stock -
Enter Orders Macro Error"
MsgBox StMessage
Resume UD_Exit

End Sub

2) Add Record Code:

Private Sub Enter_Click()
On Error GoTo ES_Error

Dim MyDb As Database
<<This line produces an ERROR "User defined type not defined">>
Dim MySet As Recordset
Dim EDate As String, EType As String, EStckNo As Long, EQty As
Integer
Dim TStck As Integer, TSql As String

Set MyDb = DBEngine.Workspaces(0).Databases(0)
<<My DB not defined - for obvious reasons!>>
DoCmd.Hourglass True

EDate = Forms![Sales Entry]![DateSold]
EType = Forms![Sales Entry]![SaleType]
EStckNo = Forms![Sales Entry]![StockNo]
EQty = Forms![Sales Entry]![QtySold]

' Add sales record to Stock Sales table...

Set MySet = MyDb.OpenRecordset("Stock Sales")
MySet.AddNew
MySet![Date] = EDate
MySet![Sales Type] = EType
MySet![Stock Number] = EStckNo
MySet![Qty Sold] = EQty
MySet.Update
MySet.Close

Finishing:
' MyDb.Close

ES_Exit:
DoCmd.Hourglass False
Exit Sub

ES_Error:

StMessage = Str(Err.Number) & " " & Error(Err.Number) & " Sales
Entry Macro Error"
MsgBox StMessage
Resume ES_Exit


End Sub