• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

Create recordset using a query as source.

create recordset using access query.  The query is access 2003,  the table is in  Microsoft SQL 2000. Would example how code.  The tables are liked to SQL server.
0
newholyman
Asked:
newholyman
  • 6
  • 6
1 Solution
 
NatchiketCommented:
Something like this should do it

Dim db as DAO.Database
Dim rst as DAO.recordset
Dim qdf as DAO.QueryDefs

Set db = CurrentDb
Set qdf = db.QueryDefs("NameOfQueryHere")
Set rst = qdf.Openrecordset(dbOpenSnapshot)  'use dbOpenDynaset if you want an editable recordset
0
 
newholymanAuthor Commented:
I added the code you gave.  It received run-time error '3061' Too few parameters. Expected 1.
 I attached the line of where it failed.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Open in new window

0
 
NatchiketCommented:
ahh ok ... that means that there are unrecognised fields and/or you need to supply parameter values in order to run the query

to specify a paramter ...

Set qdf = db.QueryDefs("NameOfQueryHere")
qdf.Parameters("ParameterName") = "Value to use"   'note use correct format depending on data type
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

If it's an unrecognised field then run the query as normal from the database window and the database will prompt so you can identify unrecognised fields
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
newholymanAuthor Commented:
The only query parameter that I have is attach.  This query runs fine it is used in a production report.  It has not been change.  The form that the query uses is open.  Do you have any more suggest?  Are maybe I do understand what you ask.
[forms].[report menu].[rpt_date]

Open in new window

0
 
NatchiketCommented:
ahhh ok then there may be a problem ... if the Report Menu form isn't open when the code is run then the query won't like it.  But you're saying that the form is open when the query is run ?
Well if that is the case then you can get around it by renaming the parameter in the query e.g. to [rpt_date]

then ...

Set qdf = db.QueryDefs("NameOfQueryHere")
qdf.Parameters("[rpt_date]") = Forms![report menu]![rpt_date]  
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

0
 
NatchiketCommented:
actually you could just try leaving the [forms].[report menu].[rpt_date] in the query, and then something like

qdf.Parameters("[forms].[report menu].[rpt_date]") = Forms![report menu]![rpt_date]  

0
 
newholymanAuthor Commented:
I added the code it receive run-time eror 3265.  Item not found is this collection.  I included line the  code it failed on
qdf.Parameters("[rpt_date]") = Forms![report menu]![rpt_date]

Open in new window

0
 
newholymanAuthor Commented:
I added second suggestion  of code it failed with run-time error 424 oject required.  I have include the line code that fialed.
qdf.Parameters("[forms].[report menu].[rpt_date]") = Form![report menu]![rpt_date]

Open in new window

0
 
NatchiketCommented:
try
qdf.Parameters("[forms].[report menu].[rpt_date]") = Forms![report menu]![rpt_date]
0
 
newholymanAuthor Commented:
The code failed run-time error 424. object rquired
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("QRY_moneysheet_Batch_greeter_hq_filterdate_email_test")
qdf.Parameters("[forms].[report menu].[rpt_date]") = Form![report menu]![rpt_date]
Set rst = qdf.OpenRecordset(dbOpenSnapshot)

Open in new window

0
 
NatchiketCommented:
line 6: you need and S of the end of Form  --->  Forms![report menu]![rpt_date]
see my earlier comment
0
 
newholymanAuthor Commented:
Thank you very much for the support.  It was easy to follow the instructions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now