Method or Data Member not found

[Duck]

I am trying to learn VBA for Access coding and I can't figure out why
the following bit of code fails to compile with the error message:
"Method Or Data Member Not Found" I am merely trying to find all the
customers in our customer table with the last name of Jones and add
their Name and address to a new table. I wrote this procedure in a
standard module, and I do have a reference to DAO 3.6 library.

Public Sub FindJones()

Dim dbPico As DAO.Database
Dim rstCustomer As DAO.Recordset
Dim rstJonses As DAO.Recordset

Set dbPico = CurrentDb
Set rstCustomer = dbPico.OpenRecordset("tblCustomer")
Set rstJonses = dbPico.OpenRecordset("tblJonses")
rstCustomer.MoveFirst
Do While rstCustomer.EOF = False
rstCustomer.Find "LName = 'Jones'"
If Not rstCustomer.NoMatch Then
rstJonses.AddNew
With rstJonses
![CustID] = rstCustomer![CustID]
![FName] = rstCustomer![FName]
![LName] = rstCustomer![LName]
![Address] = rstCustomer![Address]
.Update
End With
End If
Debug.Print rstCustomer![FName] & " " & rstCustomer![LName] &
" Added"
Loop

End Sub

 

[Dirk Goldgar]

I am trying to learn VBA for Access coding and I can't figure out why
the following bit of code fails to compile with the error message:
"Method Or Data Member Not Found" I am merely trying to find all the
customers in our customer table with the last name of Jones and add
their Name and address to a new table. I wrote this procedure in a
standard module, and I do have a reference to DAO 3.6 library.

Public Sub FindJones()

Dim dbPico As DAO.Database
Dim rstCustomer As DAO.Recordset
Dim rstJonses As DAO.Recordset

Set dbPico = CurrentDb
Set rstCustomer = dbPico.OpenRecordset("tblCustomer")
Set rstJonses = dbPico.OpenRecordset("tblJonses")
rstCustomer.MoveFirst
Do While rstCustomer.EOF = False
rstCustomer.Find "LName = 'Jones'"
If Not rstCustomer.NoMatch Then
rstJonses.AddNew
With rstJonses
![CustID] = rstCustomer![CustID]
![FName] = rstCustomer![FName]
![LName] = rstCustomer![LName]
![Address] = rstCustomer![Address]
.Update
End With
End If
Debug.Print rstCustomer![FName] & " " & rstCustomer![LName] &
" Added"
Loop

End Sub

DAO recordsets don't have a Find method; they have FindFirst, FindNext,
FindPrev, and FindLast methods instead. *ADO* recordsets have a Find
method.

 

[Allen Browne]

Which line generates the error?

Several things could go wrong, e.g.:
- Depending on whether the tables are local or attached, you get a different
type of recordset type (Table or Dynaset), and then different methods apply
(and others fail.)

- If there are no records, the MoveFirst will fail.

- FindFirst might work better than Find (assuming a dynaset.)

Rather than oepn the entire table, it might be better to fetch just the
jones to start with:
strSql = "SELECT CustomerID FROM tblCustomer WHERE LName = 'Jones';"
Set rstCustomer = dbPico.OpenRecorset(strSql)
If rstCustomer.RecordCount = 0 Then
rstjones.AddNew
...

(Presumably this is just an example; you would not have multiple customer
tables for different clients.)

 

[Ken Sheridan]

You are using DAO as the data access technology, but have called the Find
method, which is a method of ADO. DAO supports the FindFirst and FindNext
methods, but if you use the latter of these instead of Find in your code
you'd end up permanently stuck in the loop after the last one is found, so
you'd need to combine them like so:

rstCustomer.MoveFirst
rstCustomer.FindFirst "LName = ""Jones"""
Do Until rstCustomer.NoMatch
With rstJonses
.AddNew
![CustID] = rstCustomer![CustID]
![FName] = rstCustomer![FName]
![LName] = rstCustomer![LName]
![Address] = rstCustomer![Address]
.Update
End With
Debug.Print rstCustomer![FName] & " " & rstCustomer![LName] & "
Added"
rstCustomer.FindNext "LName = ""Jones"""
Loop

Or you can loop through all rows and examine each for a LName value of Jones.

rstCustomer.MoveFirst
Do While Not rstCustomer.EOF
If rstCustomer.Fields("LName") = "Jones" Then
With rstJonses
.AddNew
![CustID] = rstCustomer![CustID]
![FName] = rstCustomer![FName]
![LName] = rstCustomer![LName]
![Address] = rstCustomer![Address]
.Update
End With
End If
Debug.Print rstCustomer![FName] & " " & rstCustomer![LName] & " Added"
rstCustomer.MoveNext
Loop

Note that I've used double quotes rather than the single quote character as
the text delimiter. The latter is fine for names like Jones, but would not
work with my name in its original Irish form of Cináed O'Siridean for
instance as it contains an apostrophe, which is the same character as a
single quote of course. To include double quotes in a literal string
expression a contiguous pair of double quotes characters is used, like this:

I take it that you are only doing this as an exercise and do not have a
tblJoneses in a real-life application. That would violate the fundamental
basis of the relational model, as Jones is a value of attribute type LNname.
Tables model entity types, e.g. Customers, and columns attribute types, so to
have a tblJoneses would amount to 'encoding data as table names', which
contradicts the Information Principle:

'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000

You can of course return a result table of Jones with a simple query:

SELECT *
FROM tblCustomer
WHERE LName = "Jones";

Ken Sheridan
Stafford, England

I am trying to learn VBA for Access coding and I can't figure out why
the following bit of code fails to compile with the error message:
"Method Or Data Member Not Found" I am merely trying to find all the
customers in our customer table with the last name of Jones and add
their Name and address to a new table. I wrote this procedure in a
standard module, and I do have a reference to DAO 3.6 library.

Public Sub FindJones()

Dim dbPico As DAO.Database
Dim rstCustomer As DAO.Recordset
Dim rstJonses As DAO.Recordset

Set dbPico = CurrentDb
Set rstCustomer = dbPico.OpenRecordset("tblCustomer")
Set rstJonses = dbPico.OpenRecordset("tblJonses")
rstCustomer.MoveFirst
Do While rstCustomer.EOF = False
rstCustomer.Find "LName = 'Jones'"
If Not rstCustomer.NoMatch Then
rstJonses.AddNew
With rstJonses
![CustID] = rstCustomer![CustID]
![FName] = rstCustomer![FName]
![LName] = rstCustomer![LName]
![Address] = rstCustomer![Address]
.Update
End With
End If
Debug.Print rstCustomer![FName] & " " & rstCustomer![LName] &
" Added"
Loop

End Sub