asked on

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

What is the SQL for qryFaxReminder ?

mx

eshurak

ASKER

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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]") ));

eshurak

ASKER

I'm getting the same results using EVAL.

eshurak

ASKER

The code stops at "Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)"

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

eshurak

ASKER

1. Yes form is open.
2. No.
3. EVAL in PARAMETERS does not work.

ASKER CERTIFIED SOLUTION

eshurak

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eshurak

ASKER

Thanks for your help.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well, that's what Eval() in the SQL gets around ... doing the qry.Parameters in code.

Does that work ?

mx

eshurak

ASKER

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

No, I meant what you posted ... re "I'm going this route."

?

eshurak

ASKER

Oh, yes. The qry.Parameters works.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Very strange ... sorry

eshurak

ASKER

I found this solution without expert assistance.