Solved

Create recordset using a query as source.

Posted on 2008-10-02
12
701 Views
Last Modified: 2010-04-21
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
Comment
Question by:newholyman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 17

Expert Comment

by:Natchiket
ID: 22631625
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
 

Author Comment

by:newholyman
ID: 22633435
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
 
LVL 17

Expert Comment

by:Natchiket
ID: 22633531
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:newholyman
ID: 22633960
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
 
LVL 17

Expert Comment

by:Natchiket
ID: 22634327
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
 
LVL 17

Expert Comment

by:Natchiket
ID: 22634678
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
 

Author Comment

by:newholyman
ID: 22636165
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
 

Author Comment

by:newholyman
ID: 22636418
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
 
LVL 17

Expert Comment

by:Natchiket
ID: 22636914
try
qdf.Parameters("[forms].[report menu].[rpt_date]") = Forms![report menu]![rpt_date]
0
 

Author Comment

by:newholyman
ID: 22637243
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
 
LVL 17

Accepted Solution

by:
Natchiket earned 500 total points
ID: 22637282
line 6: you need and S of the end of Form  --->  Forms![report menu]![rpt_date]
see my earlier comment
0
 

Author Closing Comment

by:newholyman
ID: 31502664
Thank you very much for the support.  It was easy to follow the instructions.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question