Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

open query in a recordset

Posted on 2011-09-08
15
Medium Priority
?
379 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

670 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