Solved

open query in a recordset

Posted on 2011-09-08
15
371 Views
Last Modified: 2012-05-12
Hello,

I'm trying to open a query in a recordset and I'm getting a 3061 error.  I've tried to do this in several ways and I've had no luck.  I'm using Access 2010 with SQL Server 2005.  

Any idea would be great.  Thanks!

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    strSQL = "Select NHID From qryFaxReminder"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Open in new window

0
Comment
Question by:eshurak
[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
  • 9
  • 6
15 Comments
 
LVL 75
ID: 36506831
What is the SQL for qryFaxReminder ?

mx
0
 
LVL 3

Author Comment

by:eshurak
ID: 36506863
PARAMETERS [Forms]![frmFaxReminder]![WeekFromNow] DateTime;
SELECT tblFacilityInfo.nhid, tblAdministrator.Administrator, tblContacts.ContactName, tblFacilityInfo.Fax, tblFacilityInfo.Phone, tblFacilities.FNAME, tblFacilityInfo.DateSched1, tblFacilityInfo.DateSched2, tblFacilityInfo.DateSched3, tblAdministrator.ContactNotAdmin
FROM ((tblFacilityInfo LEFT JOIN tblContacts ON tblFacilityInfo.nhid = tblContacts.nhid) INNER JOIN tblFacilities ON tblFacilityInfo.nhid = tblFacilities.nhid) LEFT JOIN tblAdministrator ON tblFacilityInfo.nhid = tblAdministrator.nhid
WHERE (((tblFacilityInfo.DateSched1)=[Forms]![frmFaxReminder]![WeekFromNow]));

Open in new window

0
 
LVL 75
ID: 36506874
Try this ... using Eval()

PARAMETERS [Forms]![frmFaxReminder]![WeekFromNow] DateTime;
SELECT tblFacilityInfo.nhid, tblAdministrator.Administrator, tblContacts.ContactName, tblFacilityInfo.Fax, tblFacilityInfo.Phone, tblFacilities.FNAME, tblFacilityInfo.DateSched1, tblFacilityInfo.DateSched2, tblFacilityInfo.DateSched3, tblAdministrator.ContactNotAdmin
FROM ((tblFacilityInfo LEFT JOIN tblContacts ON tblFacilityInfo.nhid = tblContacts.nhid) INNER JOIN tblFacilities ON tblFacilityInfo.nhid = tblFacilities.nhid) LEFT JOIN tblAdministrator ON tblFacilityInfo.nhid = tblAdministrator.nhid
WHERE (((tblFacilityInfo.DateSched1)=EVAL("[Forms]![frmFaxReminder]![WeekFromNow]") ));
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:eshurak
ID: 36506917
I'm getting the same results using EVAL.
0
 
LVL 3

Author Comment

by:eshurak
ID: 36506930
The code stops at "Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)"
0
 
LVL 75
ID: 36506938
Really ... ok ...

1) Is that Form open when you run the code?

2) Are there *any* misspelled objects (typos) in [frmFaxReminder]![WeekFromNow]  or the query name itself.

Actually ... may need EVAL in the PARAMETERS part also:


PARAMETERS EVAL("[Forms]![frmFaxReminder]![WeekFromNow]") DateTime;
SELECT tblFacilityInfo.nhid, tblAdministrator.Administrator, tblContacts.ContactName, tblFacilityInfo.Fax, tblFacilityInfo.Phone, tblFacilities.FNAME, tblFacilityInfo.DateSched1, tblFacilityInfo.DateSched2, tblFacilityInfo.DateSched3, tblAdministrator.ContactNotAdmin
FROM ((tblFacilityInfo LEFT JOIN tblContacts ON tblFacilityInfo.nhid = tblContacts.nhid) INNER JOIN tblFacilities ON tblFacilityInfo.nhid = tblFacilities.nhid) LEFT JOIN tblAdministrator ON tblFacilityInfo.nhid = tblAdministrator.nhid
WHERE (((tblFacilityInfo.DateSched1)=EVAL("[Forms]![frmFaxReminder]![WeekFromNow]") ));

mx
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507011
1. Yes form is open.
2. No.  
3. EVAL in PARAMETERS does not work.
0
 
LVL 3

Accepted Solution

by:
eshurak earned 0 total points
ID: 36507032
I'm going this route.

    Dim rs As DAO.Recordset
    Dim qry As DAO.QueryDef
    Set qry = CurrentDb.QueryDefs("qryFaxReminder")
    qry.Parameters("[Forms]![frmFaxReminder]![WeekFromNow]") = [Forms]![frmFaxReminder]![WeekFromNow]
    Set rs = qry.OpenRecordset(dbOpenSnapshot, dbPessimistic)

Open in new window

0
 
LVL 3

Author Comment

by:eshurak
ID: 36507041
Thanks for your help.
0
 
LVL 75
ID: 36507045
Well, that's what Eval() in the SQL gets around ... doing the qry.Parameters in code.  

Does that work ?

mx
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507053
No, the EVAL did not work.

PARAMETERS [Forms]![frmFaxReminder]![WeekFromNow] DateTime;
SELECT tblFacilityInfo.nhid, tblAdministrator.Administrator, tblContacts.ContactName, tblFacilityInfo.Fax, tblFacilityInfo.Phone, tblFacilities.FNAME, tblFacilityInfo.DateSched1, tblFacilityInfo.DateSched2, tblFacilityInfo.DateSched3, tblAdministrator.ContactNotAdmin
FROM ((tblFacilityInfo LEFT JOIN tblContacts ON tblFacilityInfo.nhid = tblContacts.nhid) INNER JOIN tblFacilities ON tblFacilityInfo.nhid = tblFacilities.nhid) LEFT JOIN tblAdministrator ON tblFacilityInfo.nhid = tblAdministrator.nhid
WHERE (((tblFacilityInfo.DateSched1)=Eval("[Forms]![frmFaxReminder]![WeekFromNow]")));

Open in new window

0
 
LVL 75
ID: 36507076
No, I meant what you posted ... re "I'm going this route."

?
0
 
LVL 3

Author Comment

by:eshurak
ID: 36507103
Oh, yes.  The qry.Parameters works.
0
 
LVL 75
ID: 36507119
Very strange ... sorry
0
 
LVL 3

Author Closing Comment

by:eshurak
ID: 36527968
I found this solution without expert assistance.
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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 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