Problems declaring objects!

[JasonHampson]

I've had some experience with visual basics in excel but I'm having problems getting started in access. I can create tables and relationships but I want to use VBA to go through records compare values in fields, change values, add and remove records. I've followed examples in the help menu e.g.:

Sub FindRecord()
Dim dbs As Database, rst As Recordset
Dim strCriteria As String

' Return reference to current database.
Set dbs = CurrentDb
' Define search criteria.
strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"
' Create a dynaset-type Recordset object based on Orders table.
Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
' Find first matching record.
rst.FindFirst strCriteria
' Check if record is found.

If rst.NoMatch Then
MsgBox "No record found."
Else
' Find other matching records.
Do Until rst.NoMatch
Debug.Print rst!ShipCountry; " "; rst!OrderDate
rst.FindNext strCriteria
Loop
End If
rst.Close
Set dbs = Nothing
End Sub

But the ‘Dim dbs As Database’ part gives a ‘User-defined type not defined’ error message. If I comment out declaring the database object it seems to work ok. And if I declare the recordset I get a ‘Method or data member not found’ for this line ‘rst.FindFirst strCriteria’. It just seemed a bit strange that it didn’t work when I declared some variables. I’m using access 2002 if anyone had any ideas.

 

[chrissie1]

much asked question
when you are in the code window click on references in the tools menu and search for DAO 3.6 check and then your code should work just fine

Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[JeremyNYC]

But you'll actually want to do this:
dim db as dao.database
dim rst as dao.recordset

New version of access ahve two ways of making these variables, DAO and ADO. DAO is the older, generally faster way. ADO is the newer, more flexible way.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[JasonHampson]

Thanx for your help!!!