473,695 Members | 2,594 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Which Record to display 1st?

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
17 2068
Would something like this work? If so, what is the proper syntax?

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

Nov 13 '05 #2
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
Nov 13 '05 #3
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
Nov 13 '05 #4
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

Nov 13 '05 #5
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

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1781
by: SAN CAZIANO | last post by:
I have to insert a button in the left of all record in a table to print the current record, simply create a table and print it in the printer. anyone have an idea or there is a printer/report utility
3
13823
by: Michael Charney | last post by:
I have a form where records are entered. I have turned off the built in record control. I created buttons to advance and go back through the records as well as an add new record button, but I also want to display the current record number. I have a blank control on the form to display the number but I can not figure out how to assign it to that blank control? Anyone know how to do this?
3
2907
by: DataBard007 | last post by:
My Access 97 database has a form that contains text boxes that display people's names, addresses, phone numbers, etc. The record source is a single table. I created a NextRecord and Previous Record command button that, when clicked, will display the next record and the previous record, respectively. But what if I want a quick glance at all my records? Is there a way to get the records to display automatically, one after another, with a...
12
20920
by: Wadim Grasza | last post by:
I want to store and display (on a form or a report) multiple pictures per record in an access database. The pictures are not stored within the database. They are stored as files and the database contains the paths to the pictures. The database consists of two tables: TABLE DATA ID Name LastName
6
2878
by: dbuchanan | last post by:
VS2005 I've been reading all the help I can on the topic (MSDN, other) but I can't make sense of this. Desired behavior; The user is to choose from the displayed list of the databound combobox and the coresponding 'Id' from the lookup table is to be inserted into the field of the new record. I have two simple tables. "tblPerson" is the data table. The lookup
4
5536
by: Susan Bricker | last post by:
I have a form that displays record information in Continuous Record display (scrollable list). One of the fields in the record is an Integer value called "rcode" (reason code). But, I don't want to display the reason code integer value. I want to display the reason code descriptive text (string data). So I have a TEXT BOX object on the form to display the text. I have an public array defined(global variable defined in CommonProcs...
0
1729
by: sumitdipsite2005 | last post by:
I am trying to use VB6 as a middleware between two 3rd party applications. "App. A" ----> VB6 ------> "App B" i am having no trouble sending data from VB to the "App B". But i am having some trouble in reading data from "App A".
3
2231
by: Gord | last post by:
If I have a form open with a subform control on it in datasheet view that has its record source set to a query or a table, is it possible to determine which record the user has clicked into with VB? I don't mean the text box with which you can determine the field/column, but the record (row). I guess what I'm looking for would be the bookmark? (or some line numbering?) or whatever information would aid in determining unambiguously which...
0
2703
by: solargovind | last post by:
Hello, I have few problem with Dlookup condition. I need to retrieve next record or previous record based on certain condition. The conditions are set in in the combo box. Here, I am trying to display the records in the text box based on my selection. I have given my selection in the combo box. For example from the student table, I am trying to display as below... a). Who are all coming from "Newyork" city in the 7th standard class. ...
0
9122
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6496
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5841
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4587
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3018
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2280
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1980
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.