Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

open query in a recordset

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
eshurak
Asked:
eshurak
  • 9
  • 6
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What is the SQL for qryFaxReminder ?

mx
0
 
eshurakAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
eshurakAuthor Commented:
I'm getting the same results using EVAL.
0
 
eshurakAuthor Commented:
The code stops at "Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)"
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
eshurakAuthor Commented:
1. Yes form is open.
2. No.  
3. EVAL in PARAMETERS does not work.
0
 
eshurakAuthor Commented:
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
 
eshurakAuthor Commented:
Thanks for your help.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, that's what Eval() in the SQL gets around ... doing the qry.Parameters in code.  

Does that work ?

mx
0
 
eshurakAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No, I meant what you posted ... re "I'm going this route."

?
0
 
eshurakAuthor Commented:
Oh, yes.  The qry.Parameters works.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Very strange ... sorry
0
 
eshurakAuthor Commented:
I found this solution without expert assistance.
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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