Which Record to display 1st?

[perryche]

I have 5records, e.g. Rc1, Rc2, Rc3..., when user open up a form, I
want it to open to a particular record (say Rc3) then when user chooses
the Record Selector [<], it will go to Rc2 and [>], it will go to Rc4.

If I use Sort in the query, it is sorted alphabetically;
if I do not sort, whatever is the latest record will be on top.

Any help/insight will be appreciated.

Perry

[perryche]

Would something like this work? If so, what is the proper syntax?

DoCmd.FindRecor d (Now() >= [Date1] And Now() < [Date2])

[David W. Fenton]

pe******@yahoo. com wrote in
news:11******** **************@ z14g2000cwz.goo glegroups.com:

I have 5records, e.g. Rc1, Rc2, Rc3..., when user open up a form,
I want it to open to a particular record (say Rc3) then when user
chooses the Record Selector [<], it will go to Rc2 and [>], it
will go to Rc4.

If I use Sort in the query, it is sorted alphabetically;
if I do not sort, whatever is the latest record will be on top.

Records are presented in the order that you tell them via the SORT
ORDER of the underlying query, or via that property on the form (I
prefer the former, since you can always override it with the latter
if you need to).

If there is no sort order set in the underlying recordsource or in
the form, the records will appear in an unpredictable order. If you
have a non-random Autonumber field as the first field in your
recordset, it will often sort in Autonumber order, which would be
the order the records were added.

If you want it sorted by date, then sort it by date.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

pe******@yahoo. com wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.com:

Would something like this work? If so, what is the proper syntax?

DoCmd.FindRecor d (Now() >= [Date1] And Now() < [Date2])

You need bookmark navigation, using the form's Recordsetclone.

With Me.RecordsetClo ne
.FindFirst "[Date1]<=#" & Date() & "# AND [Date2]>#" & Date() &
"#" If .NoMatch Then
MsgBox "No records matched your criteria."
Else
Me.Bookmark = .Bookmark
End If
End With

Now, I changed a number of things in your criteria. First, I used
Date() instead of Now(), since Now() returns a date/time, including
the time part, whereas Date() returns just the date part. If your
field is storing only dates, then you don't need to compare it to
the current date/time, just to the current date.

Secondly, I changed the order of criteria. You had them defined
backwards to my way of thinking, comparing the dynamic value to the
stored value. I guess there's actually nothing wrong with that, but
it looks wrong!

Of course, I'm not entirely certain that reversing the signs gets
exactly the same result.

It could be that this criterion definition for the .FindFirst:

Date() Between [Date1] And [Date2]

might actually work, as well, but it depends on how important the
boundary for Date2 is, because the Between operator is inclusive, if
I'm not mistaken.

Now, the other issue is that I wouldn't use bookmark navigation for
this at all, because you're not going to a single record. That is,
there could be many records that match your criteria, so you don't
know which one you're going to end up on.

For the criteria you gave, instead of sorting them and then
navigating to the first one, I'd just filter the form to display
only the records you seek. That can be done either by setting the
form's .Filter property (and then turning it on by setting
Me.FilterOn = True) to the criteria in the .FindFirst above, or by
changing the form's underlying recordsource.

I'm more likely to do the latter because I find filters to be too
easily changed or lost, with no method for undoing additional
filtering/sorting one step at a time. If you change the underlying
recordsource, the user can filter and sort the result to her heart's
content without mucking up the basic data set.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Filips Benoit]

First find the id of the record you want to start with using iStartRecordID
= DLookup("ID",3T ableName","Your Criteria")
Then use DoCmd.FindRecor d iStartRecordID in the OnLoad-Event

Filip
<pe******@yahoo .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I have 5records, e.g. Rc1, Rc2, Rc3..., when user open up a form, I
want it to open to a particular record (say Rc3) then when user chooses
the Record Selector [<], it will go to Rc2 and [>], it will go to Rc4.

If I use Sort in the query, it is sorted alphabetically;
if I do not sort, whatever is the latest record will be on top.

Any help/insight will be appreciated.

Perry

[perryche]

Filip and others,
First, thank you all for your input. I am very green in VBA.
Here is what I have, but it is not working

On Form Open,

Dim iStartRecordID As Variant
iStartRecordID = DLookup("FieldN ame", "TableName" , Now() >=
[FieldName] And Now() < ([FieldName] + 60))
DoCmd.FindRecor d iStartRecordID

Perry

[perryche]

David,
Your method worked. Thank you. However, it is a little more
complicated... Here is a more detail about my form:

Main Form w/ one Subform. These record find is within the subform. If
the main form is first opened, your method works beautifully when I put
the codes in the Form_Open event. If now, I jump to another record in
the subform, the code stops working. (I have tried to put it in
on_change, on_query, on_current, on_lostfocus... nothing seems to
work.) What am I doing wrong?

Perry

[David W. Fenton]

pe******@yahoo. com wrote in
news:11******** **************@ g47g2000cwa.goo glegroups.com:

Your method worked. Thank you. However, it is a little
more
complicated... Here is a more detail about my form:

Main Form w/ one Subform. These record find is within the
subform. If the main form is first opened, your method works
beautifully when I put the codes in the Form_Open event. If now,
I jump to another record in the subform, the code stops working.
(I have tried to put it in on_change, on_query, on_current,
on_lostfocus... nothing seems to work.) What am I doing wrong?

Is your subform displaying child records of the parent form?

If you want to navigate within a subform, just replace this in the
code:

With Me.RecordsetClo ne

with:

With Me!SubformName. Form.RecordsetC lone

and it should work just fine.

Of course, again, I still think you should be filtering, not using
bookmark navigation.

Also, one thing I left out in my code, before you set Me.Bookmark =
Me.RecordsetClo ne.Bookmark, you should add:

If Me.Dirty Then Me.Dirty = False

Why not just save the record in all cases? Because that causes
problems if your back end is replicated.

[BTW -- it's considered impolite to both post and email. I don't
respond to emailed question, only in the newsgroup]

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[perryche]

David,
I appoligize. I did not know that [in the BTW comment]. I will
give your codes a try tomorrow am. I will post the results here.

BTW, where should I place this code in? On_Current? On_update?

Perry

[perryche]

David,
I appoligize. I did not know that [in the BTW comment]. I will
give your codes a try tomorrow am. I will post the results here.

BTW, where should I place this code in? On_Current? On_update?

Perry