Subform not consistently updating from RecordSource in Access 97

[stuart.medlin]

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

[Allen Browne]

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.

<st***********@ncmail.netwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...

>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

[stuart.medlin]

Thanks Allen for your response. This seems to have fixed the problem.
I am curious, though, why having this in a query in a record source for
the subform does not work? If I just opened up the subform, then it
was always displaying the correct records.

[Allen Browne]

Stuart, it's difficult to answer that question without knowing what your
functions do.

It appears that they take no arguments, so Access does not see them as
dependent on anything, and therefore does not see a need to requery them. If
they actually read a value from a form or something like that, they do have
dependcies that are not obvious to Access.

Assigning the literal values at the time they need to be updated ensures
they are right, and is much more efficient and reliable, so you have avoided
the whole dependencies issue.

--
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.

<st***********@ncmail.netwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

Thanks Allen for your response. This seems to have fixed the problem.
I am curious, though, why having this in a query in a record source for
the subform does not work? If I just opened up the subform, then it
was always displaying the correct records.