DAO Question

[Brian]

Hello All,
I am trying to read the contents of a Query called CustName. It is a listing
of customer names and their ID Number.
When I try to read this query using the following code, I get a "Invalid
Operation" error code 3219.

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("CustName", dbOpenTable)
rs.MoveLast
rs.MoveFirst

Do Until rs.EOF
If rs!CustID = Me.CustID Then
Me.CustName.Caption = rs!CustName
End If
rs.MoveNext
Loop

closeoutRoutine:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

What the heck have I missed?
Thanks in advance.

BRC

 

[Ken Snell [MVP]]

You cannot open a recordset based on a query using the dbOpenTable argument;
that argument is usable only for local (not linked) tables.

Try this:
Set rs = db.OpenRecordset("CustName", dbOpenDynaset)

 

[Brian]

Worked like a champ.
I tried dbOpenDynaset earlier realizing that it was the way to go but I must
have done something wrong.
Works now -- Thanks again Ken.

 

[Pat Hartman]

Why would you read an entire table only to obtain a field which should be in
the form's recordsource???? Change the form's RecordSource to be a query
that joins to the Customer table and select the customer name in addition to
all the fields from the other table. That way you can eliminate your code
entirely.

 

[Sandie]

Sorry to hijack - what if "CustName" is parameter query - how can you pass
the parameters in OpenRecordset command?

THANKS!!

 

[Ken Snell [MVP]]

Some generic code:
-------------------

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("CustName")
' Evaluate the parameters in the query "CustName"
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' code here to do something with the recordset
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing