RecordsetClone

[rick.chagnon]

From one form I am trying to open a different form based on a select date in a recordset. When I do this, it filters out only those records that have that date. I do not want this. I want to somehow create a RecordsetClone so after the new form opens, it will open to the first record with the selected date but then I can still be able to scroll forward or backwards through the records to see other records with different dates. I just can’t seem to get any code to work.

Specifically My code on the primary form is-

DoCmd.OpenForm "FlightList", acNormal, , "FltDate=#" & ([M3]) & "/" & ([MONTH]) & "/" & ([YEAR]) & "#", acFormReadOnly, acWindowNormal

‘Where [M3] is a number that represents a specific calendar day
‘[MONTH] and [YEAR] is the current month and year displayed on the primary form

For example: If [M3] = 12 and [MONTH] = January and [YEAR] = 2005 the result of my code is this –

DoCmd.OpenForm "FlightList", acNormal, , "FltDate= 12/January/2005#", acFormReadOnly, acWindowNormal

The result from executing this code on the primary form returns all records on the target form with a date of 12/January/2005. But now I can not navigate to any other date because the previous code creates a “filter” of the records. If I use additional code to remove the filter, the recordset on the target form reverts to the first record in the recordset. I would like to open the target form to display the current record as 12/January/2005. With the OnClick property assigned to a couple of command buttons, I would like be able to then navigate to the first record with a date prior to or just after the current record.

[rick.chagnon]

I have noticed that anyone that has viewed my question has not posted any possible answers. Does that mean that no one cares to answer or that this may be a little more of a challenge then someone can answer with success?? If anyone out there can help me I would be greatly appreciative.

[RobDog888]

I guess your question may need a little clarity. What version of Access are you running?

If you create a filter for a new form why do you need to navigate to other
dates outside your filter. That kind of defeats the purpose of a filter. Sounds
like you may need to filter, unfilter, and re-filter, or something similar unless
we can come up with a better solution/method.

Edit: Perhaps a subform instead. Then when you select the filter date
from the main form the subform's recordsource will be the SQL filter. If you
need to see other dates you can re-select whatever date you need and then
the subform will refresh with new filter records.

HTH