Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Show last record upon opening
- Thread starter Minuet
- Start date
missinglinq
Programmer
When most forms are opened, the records have been sorted in some fashion. Are you talking about the "last record" after this sorting or are you talking about the last record entered in the db?
m1kee's advice is spot on for the former case. If you want the last record entered (as is sometimes the case) a simple way would be to have an autonumber generated with each record, and then in the query behind the form, have the autonumbers sorted in Descending order.
The Missinglinq
"It's got to be the going,
not the getting there that's good!"
-Harry Chapin
m1kee's advice is spot on for the former case. If you want the last record entered (as is sometimes the case) a simple way would be to have an autonumber generated with each record, and then in the query behind the form, have the autonumbers sorted in Descending order.
The Missinglinq
"It's got to be the going,
not the getting there that's good!"
-Harry Chapin
- Thread starter
- #4
This (DoCmd.GoToRecord , "", acLast) didn't seem to work with my program. My Access program consists of a main form which contains a button that, when clicked, opens a subform. This subform contains another subform that displays small records of three fields each in a list view. Because I frequently add records to this sub-subform, the list of records is becoming very long and I have to scroll down quite a ways to see the last record entered. I would like for Access to automatically scroll down and show me the last record upon opening the subform. Is this posssible?
Put the DoCmd in the "On Load" event of the final subform, the one which displays the records. If you put it on the main form, it will display the last record of the record source used for that form.
Let me know how you get on.
Mikee.
Let me know how you get on.
Mikee.
- Thread starter
- #6
I really appreciate you trying to help me, Mikee! Unfortunately, I still can't get it to work. I am wondering if it doesn't work because the list of records in the subform are filtered according to the the value chosen in a combo box. It might help if I describe what I use my forms for.
I work in a medical office and use Access to store patient information. My main form contains detailed patient information such as name, health numbers, address, diagnoses, etc. I have an "Appointment" button on my main form which takes you to a subform within a subform. The first subform contains very basic patient information (name, dob, patient id number). It also has a chart#/name lookup combo box on the form. I use the subform contained in this subform to keep track of patients' PAST appointments. Every day when a patient comes in, I enter the date of the appointment so that when patients call wanting a list of their appointments for a certain year, I can print out a report of their appointment dates. Because patients are seen at our office quite frequently, the lists of appointments are getting extensive which is why I want Access to scroll to the bottom of the list for me.
Is there anything else I can try to make this work?
I work in a medical office and use Access to store patient information. My main form contains detailed patient information such as name, health numbers, address, diagnoses, etc. I have an "Appointment" button on my main form which takes you to a subform within a subform. The first subform contains very basic patient information (name, dob, patient id number). It also has a chart#/name lookup combo box on the form. I use the subform contained in this subform to keep track of patients' PAST appointments. Every day when a patient comes in, I enter the date of the appointment so that when patients call wanting a list of their appointments for a certain year, I can print out a report of their appointment dates. Because patients are seen at our office quite frequently, the lists of appointments are getting extensive which is why I want Access to scroll to the bottom of the list for me.
Is there anything else I can try to make this work?
I would follow Missingling's suggestion.
You want to control the order in which records are displayed. The most direct and simple way to do that is to change the sort order in the underlying query. Ifr you put an autonumber field in the small table, and sort your query in decending order, then you will achieve your goal.
Things will get a bit complex because this is a subform. However, the best solution is query-based.
You want to control the order in which records are displayed. The most direct and simple way to do that is to change the sort order in the underlying query. Ifr you put an autonumber field in the small table, and sort your query in decending order, then you will achieve your goal.
Things will get a bit complex because this is a subform. However, the best solution is query-based.
- Thread starter
- #8
Okay, I have another idea. Would it be possible to program my sub-subform to display only the records entered in the last year, but have a button on the form that can be used to display all records? That way my list won't be so long, but if I need to see older records, I can just click the button. How could I do this?
- Thread starter
- #10
This is a more detailed explanation of a query-based solution.
Goal: Display records in a form in inverse order. For example the last record entered will display first.
Objects required: 1 table, 1 query, 1 form. In this example, I will call them T1, Q1, and F1.
Open T1 in design view. Create a new field and choose "autonumber" as the data type. Name the new field. Save your changes and close T1. Obviously, T1 will also contain the data that you want to display (in its other fields).
Use the query wizard to create a new query (Q1) that contains all of the fields in T1. Then open the new query in design view. Notice that on the left one of the rows is called "sort". Notice that the autonumber field occupies one of the columns. Go to the cell that is at the intersection of the autonumber column and the sort row. That cell has a pulldown allowing you to sort in acending or descending order. Set the sort to descending. Then save your changes.
Make Q1 the control source for F1. Control source is one of the properties of F1. Q1 provides the T1 data inverted.
The other guy's statement would work as well as mine. He is using VB to solve the problem, and I am using SQL. Both approaches are valid.
I noticed that your project includes subforms within subforms. That can be a tricky proposition. I would try to avoid that if possible. If you want to put alot of data into a small amount of real estate, I have an alternative for you that is much simpler conceptually. Create multiple subforms on the main form, one on top of the other. Set the visible property on them to no. The user can toggle the visible property to make the appropriate data appear. This avoids subforms within subforms.
Goal: Display records in a form in inverse order. For example the last record entered will display first.
Objects required: 1 table, 1 query, 1 form. In this example, I will call them T1, Q1, and F1.
Open T1 in design view. Create a new field and choose "autonumber" as the data type. Name the new field. Save your changes and close T1. Obviously, T1 will also contain the data that you want to display (in its other fields).
Use the query wizard to create a new query (Q1) that contains all of the fields in T1. Then open the new query in design view. Notice that on the left one of the rows is called "sort". Notice that the autonumber field occupies one of the columns. Go to the cell that is at the intersection of the autonumber column and the sort row. That cell has a pulldown allowing you to sort in acending or descending order. Set the sort to descending. Then save your changes.
Make Q1 the control source for F1. Control source is one of the properties of F1. Q1 provides the T1 data inverted.
The other guy's statement would work as well as mine. He is using VB to solve the problem, and I am using SQL. Both approaches are valid.
I noticed that your project includes subforms within subforms. That can be a tricky proposition. I would try to avoid that if possible. If you want to put alot of data into a small amount of real estate, I have an alternative for you that is much simpler conceptually. Create multiple subforms on the main form, one on top of the other. Set the visible property on them to no. The user can toggle the visible property to make the appropriate data appear. This avoids subforms within subforms.
- Thread starter
- #12
OhioSteve,
I'm not sure we're on the same wavelength here. My subform within a subform contains multiple records, each record consisting of a few fields, the main one being a date. I have entered the following code into the form's record source to display only the most recent dates.
SELECT tblAppointments.* FROM tblAppointments WHERE ((tblAppointments.ApptDate)>=DateAdd("d",-365,Now())) ORDER BY tblAppointments.ApptDate ASC
Sometimes, though, I would like to be see all records entered, and I would like to be able to click a button that causes my subform to display all records. Does that make sense?
By the way, my form-subform-subform arrangement cannot be changed.
I'm not sure we're on the same wavelength here. My subform within a subform contains multiple records, each record consisting of a few fields, the main one being a date. I have entered the following code into the form's record source to display only the most recent dates.
SELECT tblAppointments.* FROM tblAppointments WHERE ((tblAppointments.ApptDate)>=DateAdd("d",-365,Now())) ORDER BY tblAppointments.ApptDate ASC
Sometimes, though, I would like to be see all records entered, and I would like to be able to click a button that causes my subform to display all records. Does that make sense?
By the way, my form-subform-subform arrangement cannot be changed.
Hi
Would setting the recordsource of the sub form to:
SELECT tblAppointments.* FROM tblAppointments ORDER BY tblAppointments.ApptDate DESCENDING
and forgetting about only displaying the most recent records etc, not solve your problem, it would bring most recent appointment first, is that what you require ?
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
Would setting the recordsource of the sub form to:
SELECT tblAppointments.* FROM tblAppointments ORDER BY tblAppointments.ApptDate DESCENDING
and forgetting about only displaying the most recent records etc, not solve your problem, it would bring most recent appointment first, is that what you require ?
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #14
KenReay,
I should have started a new thread - this is getting confusing! Instead of just arranging my records in descending order, I would rather only display the most recent records, but click a button to display all of the records if I need to see all of them. My database is a little hard to explain, but I assure you that this is the best option. I just don't know how to do it.
I should have started a new thread - this is getting confusing! Instead of just arranging my records in descending order, I would rather only display the most recent records, but click a button to display all of the records if I need to see all of them. My database is a little hard to explain, but I assure you that this is the best option. I just don't know how to do it.
Hi
OhioSteve gave you the answer:
"You would make the control source for the subform records be a query. Then that query would have a date parameter.
When the button was pressed, it would change the control source for the subform and refresh the screen."
but to flesh it out a little what is teh existing recordsource of your sub form?
If it is a table, then make instead a query based on that table.
So either way you have the rowsource as a query, with not restrictions (criteria), now make a copy of that query and set a critera on the date column of > DateAdd("yyyy",1,Date())
Say the two queries are called queryAll and queryYear.
Set the recordsource of your subform to queryYear
In your button on click event put
Me.MySubFormControl.FORM.Recordsource = "QueryAll"
Me.MySubFormControl.Requery
you need to use your own control and column names of course
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
OhioSteve gave you the answer:
"You would make the control source for the subform records be a query. Then that query would have a date parameter.
When the button was pressed, it would change the control source for the subform and refresh the screen."
but to flesh it out a little what is teh existing recordsource of your sub form?
If it is a table, then make instead a query based on that table.
So either way you have the rowsource as a query, with not restrictions (criteria), now make a copy of that query and set a critera on the date column of > DateAdd("yyyy",1,Date())
Say the two queries are called queryAll and queryYear.
Set the recordsource of your subform to queryYear
In your button on click event put
Me.MySubFormControl.FORM.Recordsource = "QueryAll"
Me.MySubFormControl.Requery
you need to use your own control and column names of course
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question