How to use The OpenRecordSet to open a query?

[cuok]

Hi All!

Please help to change the bold row (down) that opens a table to open a query instead of a table

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb()

Set rs = db.OpenRecordset("tblName&quot
With rs
.MoveFirst

Do Until rs.EOF

If ((Me.DateIn > rs![DateIn]) And (Me.DateOut < rs!DateOut)) Then MsgBox Me.DateIn & &quot;>&quot; & rs![DateIn] & &quot; And &quot; & Me.DateOut & &quot;<&quot; & rs!DateOut
.MoveNext
Loop
.Close
End With
Set rs = Nothing

Thanks in advance
CUOK

 

[wemeier]

You should just have to replace the table name with a query name.

 

[cuok]

GOOD MORNING WEMEIER AND ALL!

I tried your advice and I got the error &quot;Too few parameters expected 1&quot;

thanks for any more help

CUOK

 

[wemeier]

Make sure that the query is a Select query and make sure it doesn't use any implicit parameters (undeclared variables). Could you post the exact line that you're using and the contents of the query? I'll take a look at it.

 

[cuok]

Hi wemeier !
thnks for keeping touch with me i was not here for last day and only tomorrow morning i' see your answer (here its now 1:40 before morning )


The first query i named &quot;qryHospitalSub&quot; is:

SELECT Tbl_Hospitalizations.Id AS ID, Tbl_Patients.FirstName, Tbl_Patients.LastName, Tbl_Hospitals.HospitalId, Tbl_Hospitals.HospitalName AS Hospital_Name, Tbl_Class.className, Tbl_Hospitalizations.HosCause, Tbl_Hospitalizations.DateIn, Tbl_Hospitalizations.DateOut, Tbl_Hospitalizations.SumDayIshpoz
FROM Tbl_Patients INNER JOIN ((Tbl_Hospitalizations INNER JOIN Tbl_Hospitals ON Tbl_Hospitalizations.HospitalId = Tbl_Hospitals.HospitalId) INNER JOIN Tbl_Class ON Tbl_Hospitalizations.classId = Tbl_Class.classId) ON Tbl_Patients.Id = Tbl_Hospitalizations.Id
WHERE (((Tbl_Hospitalizations.Id)=[forms]![frmrashi]![id]) AND ((Tbl_Hospitals.HospitalId)<>32))
ORDER BY Tbl_Hospitalizations.DateIn;

The seconed query i named &quot;qryHospitalSubBetween&quot; is:

SELECT qryHospitalSub.ID, qryHospitalSub.DateIn, qryHospitalSub.DateOut, qryHospitalSub.Hospital_Name FROM qryHospitalSub;


the code i'm using is:


'=====================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Dim msg2 As String


Set rs = db.OpenRecordset(&quot;qryHospitalSubBetween&quot
With rs
.MoveFirst

Do Until rs.EOF
msg2 = &quot;USER MISTAKE!! &quot; & vbNewLine & &quot;The Patien had been recorded as hospitalized at &quot; & rs!Hospital_Name & &quot; Between: &quot; & vbNewLine & rs![DateIn] & &quot; and &quot; & rs!DateOut

msg2 = msg2 & vbNewLine & &quot;Please check your entries or &quot; & vbNewLine & &quot;press the 'SHOW ALL HOSPITALIZATIONS' button &quot; & vbNewLine & &quot;To see all &quot; & rs!Id & &quot; hospitalizations&quot;
If ((Me.DateIn > rs![DateIn]) And (Me.DateOut < rs!DateOut)) Then MsgBox msg2, vbCritical, &quot;CUOK MESSAGE:&quot;: Cancel = True
.MoveNext
Loop
.Close
End With
Set rs = Nothing

the bold row got the error.

THANKS AGAIN

CUOK

 

[cuok]

DEAR wemeier.

I forgot to say that the seconed query built on the first one!!
CUOK

 

[cuok]

Thank you wemeier


I solved it by ajdesalvo answer in thread702-471344

thank a lot
CUOK