findfirst use

[keri]

Hi everyone,

I'm using findfirst in code as below. It searches a query that shows
appointment date and account name and should return the account name of
the first appointment on each day into the same date in a calendar
form.

If rs.RecordCount 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
myDate = Format((f("date" & i)), "mm/dd/yyyy")
rs.FindFirst ("appdate = #" & myDate & "#")
If rs.NoMatch Then
f("text" & i).BackColor = 10944511
Else:
f("text" & i) = rs!ACCOUNTNAME
f("text" & i).BackColor = 12058551

However if I have 2 appointments on the same date it is returning the
last appointment in the query from that date. Why? From being new to
this I presumed findfirst would find the first in the list!

I am trying to get the code to return all of the appointments on each
day. I tried altering the code to this;

If rs.RecordCount 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
myDate = Format((f("date" & i)), "mm/dd/yyyy")
rs.FindFirst ("appdate = #" & myDate & "#")
If rs.NoMatch Then
f("text" & i).BackColor = 10944511
Else:
rs.FindNext ("appdate = #" & myDate & "#")
If Not rs.NoMatch Then
f("text" & i) = rs!ACCOUNTNAME
f("text" & i).BackColor = 12058551

Which then returns the first record on a date.

How do I get it to return both records, not one or the other?
(I am learning by trial and error and my knowledge of code is still
tiny so simple explanations would be appreciated!)

Thanks everyone.

[tina]

suggest you add an OrderBy clause to your query, so that the records are
returned in the order that you need. then add the following code to the
procedure, *before* the "If rs.RecordCount 0 Then " line, as

rs.MoveLast
rs.MoveFirst

to ensure that all the records are loaded to the recordset, and then the
FindFirst action begins at the "top" of the records.

hth
"keri" <ke*********@diageo.comwrote in message
news:11**********************@73g2000cwn.googlegro ups.com...

Hi everyone,

I'm using findfirst in code as below. It searches a query that shows
appointment date and account name and should return the account name of
the first appointment on each day into the same date in a calendar
form.

If rs.RecordCount 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
myDate = Format((f("date" & i)), "mm/dd/yyyy")
rs.FindFirst ("appdate = #" & myDate & "#")
If rs.NoMatch Then
f("text" & i).BackColor = 10944511
Else:
f("text" & i) = rs!ACCOUNTNAME
f("text" & i).BackColor = 12058551

However if I have 2 appointments on the same date it is returning the
last appointment in the query from that date. Why? From being new to
this I presumed findfirst would find the first in the list!

I am trying to get the code to return all of the appointments on each
day. I tried altering the code to this;

If rs.RecordCount 0 Then
For i = 1 To 37
If IsDate(f("date" & i)) Then
myDate = Format((f("date" & i)), "mm/dd/yyyy")
rs.FindFirst ("appdate = #" & myDate & "#")
If rs.NoMatch Then
f("text" & i).BackColor = 10944511
Else:
rs.FindNext ("appdate = #" & myDate & "#")
If Not rs.NoMatch Then
f("text" & i) = rs!ACCOUNTNAME
f("text" & i).BackColor = 12058551

Which then returns the first record on a date.

How do I get it to return both records, not one or the other?
(I am learning by trial and error and my knowledge of code is still
tiny so simple explanations would be appreciated!)

Thanks everyone.

[keri]

I already have an order by in the code, (this is above the code posted
earlier). I have tried changing the order by to another field eg.
app_time, but I get an error of too few parameters.
sql = "SELECT * FROM [querycalendar] WHERE ((MONTH(appdate) = " &
f!month & " AND YEAR(appdate)= " & f!year & ")) ORDER BY appdate;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)