Presumably your BeginDate() and EndDate() functions return a date value. You
might be able to solve the problem by concatenating that literal date into
the string that you assign to the RecordSource of the subform in its Open
event.
Private Sub Form_Open(Cancel As Integer)
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
strSql = "SELECT DISTINCTROW Transcript.* FROM Transcript " & _
"WHERE datekeyed >= " & Format(begindate(), strcJetDate) & _
" And datekeyed < " & Format(enddate(), strcJetDate) & _
" ORDER BY datekeyed;"
Me.RecordSource = strSql
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<stuart.medlin@ncmail.netwrote in message
news:1162507727.852060.63770@m7g2000cwm.googlegrou ps.com...
Quote:
>I have an Access 97 application that has a basic form (Transcript) and
subform. The subform has a query as a recordsource that returns
records from a table:
>
SELECT DISTINCTROW Transcript.NCID, Transcript.TransCode,
Transcript.TransMM, Transcript.TransYY, Transcript.DateKeyed,
Transcript.UserID
FROM Transcript
WHERE datekeyed >= begindate() and datekeyed < enddate()
ORDER BY datekeyed;
>
(the functions begindate() and enddate() are 2 functions I wrote to
return the date of the first of the
current month and the last day of the current month. In this case,
11/01/2006 and 11/30/2006)
>
The data is displayed in the subform as a datasheet. The records that
are returned are based on the date range specified by the user.
>
When I initially open the form, I set the default date range to the
first and last day of the current month. However, when I open up the
form/subform, the records displayed are not the records I expect, based
on the query above. What is displayed seems to be the records that were
last displayed when the user entered a different date range.
>
Access seems to be saving the last records displayed on the form, even
after the form is closed and is ignoring the query set up as the
subform's recordsource. If I compact the database, it seems to clear
out the problem and displays the correct records.
>
Anybody have a suggestion for what might be going on? I know it sounds
complicated, but I will be glad to try to explain further if needed.
>
Thanks!!!
>
Stuart
