Can't get code to work in Access 2000 1

[warburp]

Hi

It has been a year or so since I have done any serious programming in Access and I cannot even remember the basics! I need to find details from a table against a particular part number but the code I have used is something I found in one of my old Access 97 databases. I have enabled DAO 3.6 but the code still does not want to run. It compiles ok, but I get a Type mismatch message on the Set r1 line.

Can anyone help please, the simple code is below:

Dim db As Database
Dim r1 As Recordset

Set db = DBEngine.Workspaces(0).Databases(0)
Set r1 = db.OpenRecordset("partlist", dbOpenTable)

r1.MoveFirst
Do Until r1.EOF
If r1!Partno = Me!Partno Then
Me!Description = r1!Description
Me!Weight = r1!Weight
Me![Cost Price] = r1![Cost Price]
Me!Price = r1!Price
Me![Annual Subscription Price] = r1![Annual Subscription Price]
r1.Close
GoTo End_Loop
End
Else
r1.MoveNext

End If

Loop

Thanks

 

[MakeItSo]

Have a try with

Dim db As DAO.Database
Dim r1 As DAO.Recordset
    
Set db = CurrentDB
Set r1 = db.OpenRecordset("partlist", dbOpenDynaset)

As you do not alter your recordset fields at all, you could also open as Snapshot:

Set r1 = db.OpenRecordset("partlist", dbOpenSnapshot)

Hope this helps,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[KenReay]

Hi

Is suspect you have a reference to ADO and DAO, some object are duplicated in the two libraries, so cause confusion, try qualifying your dims as shown below

Dim db As DAO.Database
Dim r1 As DAO.Recordset

Set db = CurrentDb()

...etc


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK