For some reason this is eluding me...
I have a subform datasheet that contains appointments imported form Outlook.
If the appointment is associated with an Entity in the database, it will
have a valid ID; if the appointment is not associated with an Entity (if the
appointment is a holiday, for example) then the ID will not be valid, or
will be 0. Nevertheless, the Datasheet should display all appointments
whether the ID is valid or not.
The below routine is supposed to go to the next valid Entity appointment,
and skip over invalid IDs (for example, if the next Entity appointment is on
Dec. 28th, I don't want to go to the 25th). So I'm trying to purge the rst
of any IDs that are not valid and thus force rst.FindFirst to find the first
Entity appointment occuring after the current date.
I don't want to remove the non-Entity appointments from the Datasheet - I
just want to ignore them in this routine so I can use a Bookmark to go to
the next valid Entity appointment. But, AFAIK, I can only use Bookmarks
with a cloned Recordset. When I delete the non-Entity appointments from the
cloned rst, the records no longer appear in the Datasheet - they are, of
course, deleted.
How can I keep the non-Entity appointments in the Datasheet and still use a
Bookmark to go to the next valid Entity appointment?
Thanks in advance.
Public Sub GoToNext()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Do Until rst.EOF
If Nz(DLookup("Entity_ID", "tblEntity", "Entity_ID = " &
rst!Entity_ID), 0) Then 'Eid is valid - do nothing
Else 'remove from rst
rst.Delete
End If
rst.MoveNext
Loop
rst.MoveFirst
rst.FindFirst "[ApptDate] >= Date()"
If rst.NoMatch Then GoTo Exit_Here
Me.Form.Bookmark = rst.Bookmark
Exit_Here:
rst.Close
Set rst = Nothing
End Sub