QpenRecordset

[chas47]

I am trying to open a recordset so I can search a field [pass] for its
contents till I find a match.
----
Private Sub Command3_Click()

Dim DbsWebClubs As Database ' I get this error "User-defined type not
defined"
Dim rstTab169 As Recordset
Dim Pass As Field

Set DbsWebClubs = OpenDatabase("Dev-WebClubs.mdb")
Set rstTab169 = _
DbsWebClubs.OpenRecordset("D_TblT169", _
dbOpenForwardOnly)

Do While Not rstTab169.EOF
If Pass = "Mypass" Then
MsgBox Pass
End If
rstTab169.MoveNext
Loop

DbsWebClubs.Close
End Sub

 

[Dirk Goldgar]

I am trying to open a recordset so I can search a field [pass] for its
contents till I find a match.
----
Private Sub Command3_Click()

Dim DbsWebClubs As Database ' I get this error "User-defined type not
defined"
Dim rstTab169 As Recordset
Dim Pass As Field

Set DbsWebClubs = OpenDatabase("Dev-WebClubs.mdb")
Set rstTab169 = _
DbsWebClubs.OpenRecordset("D_TblT169", _
dbOpenForwardOnly)

Do While Not rstTab169.EOF
If Pass = "Mypass" Then
MsgBox Pass
End If
rstTab169.MoveNext
Loop

DbsWebClubs.Close
End Sub

It appears you don't have a reference set to DAO. In the VB Editor, click
Tools -> References.... If you're using Access versions 2000 to 2003,
locate "Microsoft DAO 3.6 Object Library" in the list, and check the box
next to it. If you're using Access 2007 or 2010, locate "Microsoft Office
12.0 Access database engine Object Library", and check the box next to
*that*. Doing so should correct that error.

That said, your approach of looping through the recordset looking for a
particular value seems wrong-headed to me, unless there's a lot you haven't
told us. If all you want is to find the record(s) that hold(s) the value,
just open your recordset on a query that asks only for those records:

Set rstTab169 =DbsWebClubs.OpenRecordset( _
"SELECT * FROM D_TblT169 WHERE [Pass] = ""MyPass""", _
dbOpenForwardOnly)

' If you only want to find out if such a record exists,
' follow with logic like this:

If rstTab169.EOF Then
MsgBox "Not Found"
Else
MsgBox "Found it!"
End If

' If you want to do something with all the matching records,
' follow with logic like this:

Do Until rstTab169.EOF

' ... do something with this record ...

' Move to the next record.
rstTab169.MoveNext

Loop

In either case, don't forget to close your recordset:

rstTab169.Close

One further note: if "Dev-WebClubs.mdb" is the current database where this
code is executing, you don't need to -- nor want to -- open a new database
object to refer to it. Use CurrentDb to get a reference to it. Instead of

Set DbsWebClubs = OpenDatabase("Dev-WebClubs.mdb")

.... do this:

Set DbsWebClubs = CurrentDb

In this case, don't close the database object you got from CurrentDb,
because you didn't open it.

 

[Douglas J. Steele]

Why bother? Why not just use DLookup to see whether the value exists in the
table, rather than using a recordset?

However, in answer to your specific question, it sounds as though you don't
have a reference set to DAO. While in the VB Editor, select Tools |
References from the menu. If Microsoft DAO 3.6 Object Library isn't one of
the selected references at the top of the list, scroll through the list
until you find it, then select it. (This assumes you're using Access 2003 or
earlier, or an MDB file in Access 2007. If you're using an ACCDB file in
Access 2007, you need to look for Microsoft Office 12.0 Access database
engine)