QueryDef.OpenRecordset Help

  • Thread starter Thread starter Mark Seaborn
  • Start date Start date
M

Mark Seaborn

So I have lines in my code.
Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset
Dim oQuery As QueryDef

Set curDatabase = CurrentDb
Set oQuery = curDatabase.QueryDefs("Query-ProjectAssignmentTreeCtrl")
Set rs = oQuery.OpenRecordset(dbOpenSnapshot, dbReadOnly, dbReadOnly)

I have checked with the debugger and I have a valid QueryDef Object
(oQuery). The last line of code causes an error that says "Too few
Parameters: Expected 1". I have tried all combinations of the optional
parameters to the OpenRecordset function using a QueryDef. Am I just doing
something stupid here?
 
Mark Seaborn said:
So I have lines in my code.
Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset
Dim oQuery As QueryDef

Set curDatabase = CurrentDb
Set oQuery =
curDatabase.QueryDefs("Query-ProjectAssignmentTreeCtrl") Set rs =
oQuery.OpenRecordset(dbOpenSnapshot, dbReadOnly, dbReadOnly)

I have checked with the debugger and I have a valid QueryDef Object
(oQuery). The last line of code causes an error that says "Too few
Parameters: Expected 1". I have tried all combinations of the optional
parameters to the OpenRecordset function using a QueryDef. Am I just
doing something stupid here?
Click to expand...

In all likelihood, the query itself makes reference to a control on a
form (probably as a criterion) or has some other parameter, or an
unrecognized name that the query engine interprets as a parameter. When
you run queries via the Access user interface, Access fills in the
form/control parameter references for you and prompts you for any other
parameter values, but when you use DAO to open a recordset on a query,
the parameters are not automatically resolved. You have to supply a
value for each one. You can do that by name:

oQuery.Parameters("MyParmName").Value = "foo"

or you can use a handy trick to assign values to any parameters that are
control references:

Dim prm As DAO.Parameter

For Each prm in oQuery.Parameters
.Value = Eval(.Name)
Next prm

oQuery.OpenRecordset(dbOpenSnapshot)

I don't think you need to specify dbReadOnly for a snaphot-type
recordset, and I'm fairly certain you don't need to specify it twice.
In fact, the help file says, "You can use dbReadOnly in either the
options argument or the lockedits argument, but not both. If you use it
for both arguments, a run-time error occurs."
 
Mark Seaborn said:
I have lines in my code.
Dim curDatabase As DAO.Database
Dim rs As DAO.Recordset
Dim oQuery As QueryDef

Set curDatabase = CurrentDb
Set oQuery = curDatabase.QueryDefs("Query-ProjectAssignmentTreeCtrl")
Set rs = oQuery.OpenRecordset(dbOpenSnapshot, dbReadOnly, dbReadOnly)
Click to expand...

The stuff below should work. It looks like you're reading the
database via a query. If you need to update the records during
reading, use rs.Edit and assign the new stuff to the fields, then
use rs.Update. To add a new field, use rs.Append, then fill
in the new fields, then rs.Update. To browse through the
records, just use rs.Move or rs.MoveNext, rs.MoveFirst,
rs.MoveLast or rs.MovePrevious.

Dim db As DAO.Database
Dim rs As DAO.RecordSet
Set db = DAO.OpenDatabase("fullpath.mdb")
Set rs = db.OpenRecordset("Query-ProjectAssignmentTreeCtrl")

Let me know if that works for you.
 
I run the query seperate in the query editor. This does not have any issue.
In fact the query references a value on the form and when the form is
displayed it is picking the value up from the form. Good thought though.
 
I am using the CurrentDb function to get the open database. I have a vaild
query object with the right information, so I know I am getting the database
open.
 
Mark Seaborn said:
I run the query seperate in the query editor. This does not have any
issue. In fact the query references a value on the form and when the
form is displayed it is picking the value up from the form. Good
thought though.
Click to expand...

That is exactly what I'm telling you. If the query runs in the query
designer, referencing a value on a form, it will *not* run via the DAO
OpenRecordset method unless you first resolve the parameter. Please try
the (corrected) code I posted.
 
Jim Carlock said:
The stuff below should work. It looks like you're reading the
database via a query. If you need to update the records during
reading, use rs.Edit and assign the new stuff to the fields, then
use rs.Update. To add a new field, use rs.Append, then fill
in the new fields, then rs.Update. To browse through the
records, just use rs.Move or rs.MoveNext, rs.MoveFirst,
rs.MoveLast or rs.MovePrevious.

Dim db As DAO.Database
Dim rs As DAO.RecordSet
Set db = DAO.OpenDatabase("fullpath.mdb")
Click to expand...

Why should Mark reopen a new database object pointing to the same file
that is currently opened by Access? CurrentDb, which he is using,
should do just fine.
Set rs = db.OpenRecordset("Query-ProjectAssignmentTreeCtrl")
Click to expand...

If this querydef makes reference to a form on a control, opening a
recordset on it this way will still give error 3061.
 
Oh, I follow you now. The test I spoke of was invalid. It was a bit bazar to
have to set a parameter like this the

oQuery.Parameters("[Forms]![ProjectAssignment]![TeamComboBox]").Value =
[Forms]![ProjectAssignment]![TeamComboBox]

But it worked thanks.
 
Back
Top