Query problem 1

[BabyPowder2u]

I am trying to use a query to pull all records that match the Rmkey (plus other criteria) from a recordset.
The problem is the query is not recognizing the RmKey from the recordset &
prompting for rstAvailRm!RmKey

My code is:

Set rstAvailRm = New ADODB.Recordset
rstAvailRm.CursorLocation = adUseClient
rstAvailRm.Open "tblRmList", CurrentProject.Connection, adOpenStatic, adLockOptimistic
' tblRmList has 2 fields: RmKey & RoomName
' tblScheduleHours has 7 fields, including RoomID which corresponds to RmKey of tblRmList

MkSchedList = "Select * into tblSchedList from tblScheduleHours Where " & _
"((rstAvailRm!RmKey =RoomID) and " & _
"((DayInUse Between #" & dtTmpStartday & "# and #" & dtTmpLastday & "#) " & _
"And (HourInUse Between [Forms]!frmSchedReqNoSubForm![cboStartHour1] and 2300)) " & _
"Or ((DayInUse Between #" & dtTmpFirstday & "# and #" & dtTmpEndday & "#) " & _
"And (HourInUse Between 0 and [Forms]!frmSchedReqNoSubForm![cboEndHour1])))"


With rstAvailRm
If .RecordCount > 0 Then
.MoveFirst
Do While ((Not .EOF) )
DoCmd.RunSQL MkSchedList
.moveNext
Loop 'RmLoop
End If
End With

 

[PHV]

With rstAvailRm
Do While Not .EOF
MkSchedList = "Select * into tblSchedList from tblScheduleHours Where " & _
"((RoomID=" & !RmKey & ") and " & _
"((DayInUse Between #" & dtTmpStartday & "# and #" & dtTmpLastday & "#) " & _
"And (HourInUse Between [Forms]!frmSchedReqNoSubForm![cboStartHour1] and 2300)) " & _
"Or ((DayInUse Between #" & dtTmpFirstday & "# and #" & dtTmpEndday & "#) " & _
"And (HourInUse Between 0 and [Forms]!frmSchedReqNoSubForm![cboEndHour1])))"
DoCmd.RunSQL MkSchedList
.MoveNext
Loop 'RmLoop
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BabyPowder2u]

Thanks again PHV. This has tremendously simplified my coding requirements

T