By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,122 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

Which Record to display 1st?

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
Would something like this work? If so, what is the proper syntax?

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

Nov 13 '05 #2

P: n/a
pe******@yahoo.com wrote in
news:11**********************@z14g2000cwz.googlegr oups.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
Nov 13 '05 #3

P: n/a
pe******@yahoo.com wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:
Would something like this work? If so, what is the proper syntax?

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


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

With Me.RecordsetClone
.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
Nov 13 '05 #4

P: n/a
First find the id of the record you want to start with using iStartRecordID
= DLookup("ID",3TableName","YourCriteria")
Then use DoCmd.FindRecord iStartRecordID in the OnLoad-Event

Filip
<pe******@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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

Nov 13 '05 #5

P: n/a
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("FieldName", "TableName", Now() >=
[FieldName] And Now() < ([FieldName] + 60))
DoCmd.FindRecord iStartRecordID

Perry

Nov 13 '05 #6

P: n/a
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

Nov 13 '05 #7

P: n/a
pe******@yahoo.com wrote in
news:11**********************@g47g2000cwa.googlegr oups.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.RecordsetClone

with:

With Me!SubformName.Form.RecordsetClone

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.RecordsetClone.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
Nov 13 '05 #8

P: n/a
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

Nov 13 '05 #9

P: n/a
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

Nov 13 '05 #10

P: n/a
pe******@yahoo.com wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
BTW, where should I place this code in? On_Current? On_update?


I don't know. It depends on what you're trying to do.

If you are really trying to simulate filtering, then you really
should just change the subform's recordsource.

If you just want the record selector positioned, then, yes, you
could do it in the main form's OnCurrent event.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #11

P: n/a

David,
Thank you so very much. However, I am still having little
problem. If I put the following codes, the exact code BTW, it works,
but not able to go to another record within the subform. It acts like
filtering, not record selector position. (I placed the codes in the
Subform On_Current event.)

With Me.RecordsetClone
.FindFirst "[CertBeginDate]<=#" & Now() & "# AND
([CertBeginDate]+60)>#" & Now() & "#"
If .NoMatch Then
Exit Sub
Else
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With

Is this right? BTW, I have to use Now() becuase it won't accept Date()
in VB code here. For me, as far as the time, it really does not matter
to me at this moment.

Thanks again.
Perry

Nov 13 '05 #12

P: n/a
pe******@yahoo.com wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
Thank you so very much. However, I am still having little
problem. If I put the following codes, the exact code BTW, it
works, but not able to go to another record within the subform.
It acts like filtering, not record selector position. (I placed
the codes in the Subform On_Current event.)
No, it has to be in the *parent* form's OnCurrent event, because if
you put it in the OnCurrent of the child form, arrival on every
record will immediately fire the code and take you back to the same
record.
With Me.RecordsetClone
.FindFirst "[CertBeginDate]<=#" & Now() & "# AND
([CertBeginDate]+60)>#" & Now() & "#"
If .NoMatch Then
Exit Sub
Else
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With

Is this right? BTW, I have to use Now() becuase it won't accept
Date() in VB code here. For me, as far as the time, it really
does not matter to me at this moment.


I still think you're doing the wrong thing.

Please explain what the parent and child forms are for, what the
relationship is between the two, and what you are trying to
accomplish. So far, it's all been completely abstract, and I've been
trying to answer the questions you've asked (as I understand them),
but I'm beginning to suspect that you've landed on a solution to a
problem that won't work well. Therefore, to help, we need to know
more about what the *problem* is in the first place.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #13

P: n/a
David W. Fenton <dX********@bway.net.invalid> wrote:

: No, it has to be in the *parent* form's OnCurrent event, because if
: you put it in the OnCurrent of the child form, arrival on every
: record will immediately fire the code and take you back to the same
: record.

:> With Me.RecordsetClone
:> .FindFirst "[CertBeginDate]<=#" & Now() & "# AND
:> ([CertBeginDate]+60)>#" & Now() & "#"
:> If .NoMatch Then
:> Exit Sub
:> Else
:> If Me.Dirty Then Me.Dirty = False
:> Me.Bookmark = .Bookmark
:> End If
:> End With
:>
: I still think you're doing the wrong thing.

: Please explain what the parent and child forms are for, what the
: relationship is between the two, and what you are trying to
: accomplish. So far, it's all been completely abstract, and I've been
: trying to answer the questions you've asked (as I understand them),
: but I'm beginning to suspect that you've landed on a solution to a
: problem that won't work well. Therefore, to help, we need to know
: more about what the *problem* is in the first place.

I think that your explanation is going to help me with a
problem that I've been struggling with for a while now

My forms are for viewing records only; nothing will ever
change in the database [if it works right...]
The main form shows information for an 'agency'; an embedded
subform lists the 'representatives' of the agency, and it
embeds yet another subform that lists more detailed information
on the selected 'representative'

A calling form allows a user to choose either a specific 'agency'
or a specific 'representative' for whom to view data. If [s]he
chooses a specific 'agency' then the default first
'representative' should be selected. But if [s]he chooses a
specific 'representative' (and these are assumed attached to
one agency only) then that 'representative's' 'agency' should
display, along with its list of 'reprensentatives', such that
the user's choice is selected and the subform embedded in the
*subform* displays that 'representative's' detailed
information.

I pass the 'representative' ID-field in the Docmd.openform....
when opening the parent called form--but that ID-field exists
only in the query underlying the child form, so I need the
parent to pass it on to that child

So the

:> With Me.RecordsetClone

line will need to use the syntax that you show in another post
for cloning the subform's recordset. I have been trying to do
something like this all week but I keep getting the message
'runtime error 424 object required'--I have not yet tried the
Me. alias for the parent form. (and of course I need to
remember to bypass all this when user chooses an agency)
Sorry for the longwinded post.

--thelma

: --
: David W. Fenton http://www.bway.net/~dfenton
: dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #14

P: n/a
David,
are you sure "Me.Bookmark = Me.RecordsetClone.Bookmark" is correct
syntax? It keeps giving an error message and ask me to debug.

P.S.: I placed the code in the mainform.

Nov 13 '05 #15

P: n/a
Thelma Lubkin <th****@alpha2.csd.uwm.edu> wrote in
news:d8**********@uwm.edu:
David W. Fenton <dX********@bway.net.invalid> wrote:

: No, it has to be in the *parent* form's OnCurrent event, because
: if you put it in the OnCurrent of the child form, arrival on
: every record will immediately fire the code and take you back to
: the same record.

:> With Me.RecordsetClone
:> .FindFirst "[CertBeginDate]<=#" & Now() & "# AND
:> ([CertBeginDate]+60)>#" & Now() & "#"
:> If .NoMatch Then
:> Exit Sub
:> Else
:> If Me.Dirty Then Me.Dirty = False
:> Me.Bookmark = .Bookmark
:> End If
:> End With
:>
: I still think you're doing the wrong thing.

: Please explain what the parent and child forms are for, what the
: relationship is between the two, and what you are trying to
: accomplish. So far, it's all been completely abstract, and I've
: been trying to answer the questions you've asked (as I understand
: them), but I'm beginning to suspect that you've landed on a
: solution to a problem that won't work well. Therefore, to help,
: we need to know more about what the *problem* is in the first
: place.

I think that your explanation is going to help me with a
problem that I've been struggling with for a while now

My forms are for viewing records only; nothing will ever
change in the database [if it works right...]
The main form shows information for an 'agency'; an
embedded subform lists the 'representatives' of the agency,
and it embeds yet another subform that lists more detailed
information on the selected 'representative'

A calling form allows a user to choose either a specific
'agency' or a specific 'representative' for whom to view
data. If [s]he chooses a specific 'agency' then the default
first 'representative' should be selected. But if [s]he
chooses a specific 'representative' (and these are assumed
attached to one agency only) then that 'representative's'
'agency' should display, along with its list of
'reprensentatives', such that the user's choice is selected
and the subform embedded in the *subform* displays that
'representative's' detailed information.

I pass the 'representative' ID-field in the
Docmd.openform.... when opening the parent called form--but
that ID-field exists only in the query underlying the child
form, so I need the parent to pass it on to that child
This is where I think I'd be likely to not use OpenArgs, and instead
open the form with acHidden, and have some custom properties that
I'd set and let the code inside the Property Let statements do the
navigation.

The Property Let would be in the parent form's module and would look
something like this:

Public Property Let ChildID (plngChildID As Long)
Dim lngParentID As Long
Dim bolParentFound As Boolean

lngParentID = DLookup("[ParentID]","ChildTable", _
"[ChildID]=" & lngParentID)
With Me.RecordsetClone
.FindFirst "[ParentID]=" & lngParentID
If .NoMatch Then
MsgBox "Parent record not found!"
Else
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
bolParentFound = True
End If
End With
If bolParentFound Then
With Me!ChildForm.Form.RecordsetClone
.FindFirst "[ChildID]=" & plngChildID
If .NoMatch Then
MsgBox "Child record not found!"
Else
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
End If
End With
End If
Me.Visible = True
End Property

You would set it like any property of the form:

Forms!frmParentForm.ChildID = 123

and the setting of it would cause the form to navigate to the
appropriate records and then reveal itself.
So the

:> With Me.RecordsetClone

line will need to use the syntax that you show in another
post for cloning the subform's recordset. I have been
trying to do something like this all week but I keep
getting the message 'runtime error 424 object required'--I
have not yet tried the Me. alias for the parent form. (and
of course I need to remember to bypass all this when user
chooses an agency)


I don't really understand what you mean here.

Say you have a parent form, frmParent.

On it you have a child form, subChildForm.

The child form is embeded in a subform control called ChildForm.

To navigate via bookmarks in the child form from the parent form,
you'd use:

With Me!ChildForm.Form.RecordsetClone
End With

Working backwards, that's the recordset clone of the form embedded
in the subform control named "ChildForm" (keep in mind that the
subform's name is independent of the name of the subform control
that holds it).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #16

P: n/a
pe******@yahoo.com wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
are you sure "Me.Bookmark = Me.RecordsetClone.Bookmark" is
correct
syntax? It keeps giving an error message and ask me to debug.

P.S.: I placed the code in the mainform.


Yes, that's definitely the correct syntax for navigating in the
*parent* form.

What is the error number?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #17

P: n/a
rkc
Thelma Lubkin wrote:

I think that your explanation is going to help me with a
problem that I've been struggling with for a while now

My forms are for viewing records only; nothing will ever
change in the database [if it works right...]
The main form shows information for an 'agency'; an embedded
subform lists the 'representatives' of the agency, and it
embeds yet another subform that lists more detailed information
on the selected 'representative'


If you're not entering or editing any information I think I would
lose the subforms and replace them with a couple of listboxes.
No bookmark navigation involved. Just update the recordsource of
the representives list based on the agency id and the details
listbox based on the representitve id.
Nov 13 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.