Full Version: sort new records
UtterAccess Forums > Microsoft® Access > Access Forms
Kristy9910
I'm working in Access where I have a list of schools that belong to a particular school district. Each school has its own record and the records appear alphabetically based on the school name. There are instances where I have to add new schools for a district. However, when I do this, the new schools appear last. Is there a way to have these records sorted alphabetically?
Thanks.
freakazeud
Hi,
Welcome to UA forums.
Oassume you are displaying these schools in some way with a form, right.
What is the form based on? A table or a query.
Try to base it on a query. In the query in the field you want to sort on do acsending.
Now everytime you add something it will still be acsending eventhough it might not be in the table.
If you want to add something and see the it be alphabatized correctly right after add a Me.Requery to the form so that it refreshes and shows the new entry as well alphabatized.
HTH
Good luck
Kristy9910
Thanks, that worked except I think I have a bigger problem. The forms are already created and have data entered. Is there a way I can change that one field to be based on the query that has the schools alphabetized or would I have to recreate that entire form using the query and not the table?
freakazeud
HI Kristy,
You won't need to recreate your whole form, but you will need to work with subforms.
A single form can just have one recordsource.
So if you don't want the query to be there recordsource then you will need to work with subforms. So that the schools can use the sorted query as a recordsource and the rest info uses just the table.
Check out my attached pic for clarification.
HTH
Good luck

P.S.
reading over this again I think I missunderstood you.
You can just go in the forms design view and change all the fields data source to the fields in the query. Ones you changed the forms recordsource all the fields should recognize the new fields available. So if you go into a fields property and go to data then select the field from the query which is the right field.
Hope that makes sense.
Edited by: freakazeud on Fri May 6 14:25:11 EDT 2005.
Svink
Hi,
I have the exact same problem as Kristy and I was wondering how you would add a Me.Requery statement to the form so that it refreshes. I tried using the weird refresh button option when you make a command button, but it doesn't seem to do anything.
Thanks
Svink
freakazeud
Hi,
not sure if kristy really had a requery problem rather then not having right recordsources.
A Me.Refresh normally does not do anything so you are right about using me.requery.
To add that to a button create a new button, cancel the button wizard. Go to the buttons event property (double click bottons border) and select on click ... Select event procedure and this will open the vba code window.
It should be preformatted for you with something like:
ub YouButtonName_OnClick
End Sub
So just add a Me.Requery between the beginning and end and it should get your what you want.
This could obviously be added to any other event if you wanted to.
HTH
Good luck
Svink
Hi,
Thanks for your help. But I also found out that because I used the wizard with my command buttons, it programmed the add and delete buttons to refer to the records in the table. Hence, when I add a new entry or delete one, it does so in the corresponding tables, but does not do it in the query that I created. However, the form views information from the query and so when I update the data withe the requery it doesn't really matter since the changes were only made in the tables. Would you know how I could make the changes in the query as well? I know basics in VB and am pretty good in programming, but when it comes to Access in VB, I'm totally clueless.
freakazeud
Ok lets streighten this out.
A query is based on a table and just shows what a table shows. A query can be used to display the underlaying information in the table differently, e.g. with sorting, criteria...
So depending on what your form is based on it should show the same no matter what.
If the form is based on the table then a query run on that table will show the same thing. If your form is based on a query it should already show the correct underlaying info.
You can not delete/add records in a query as it just displays what's going on in the underlaying table and updates depending on what is happening in the table.
So you must have some kind of sturctural issue.
Maybe you can describe a little more what you are trying to achive so we can figure out ur situation.
HTH
Good luck
Svink
Well, that's really funny, cause when i add a new entry, it creates a new entry properly in my table, but the query doesn't change. So I tried making the query all over again, but the query still doesn't change when my tables change. Maybe I'm not making a query properly . . . but I really don't see how I could be doing it so wrong.
asically, I just want to be able to add a new record into a database and then have it alphabetized. It's really a simple task.
freakazeud
can you attach a sample? .zip it and keep it under 500k. Make sure you strip all confidential data.
Ocan take a look at it then.
Thanks.
Svink
Here's a sample of my database with false information. You must run the "APP-append" form. Thanks
freakazeud
All right.
Why are you using a query in the first place?
Your tab (form) generalinfo is based on the tablegeneral info. Why is the main form based on the allinfo query? I do not see anywhere why you actually have a query since your data entry in the forms is based on tables.
Furthermore you should probably start using naming conventiones. Name your tables tbl...forms frm... queries qry... reports rpt... Your db is very confusing and you can't really see what you are referencing if they just have regular names.
Svink
uhmm, well, when I was getting help from other people about record manipulation they told me to put all the data into a query and refer to it in order to sort out a previous problem I had, which was that the controls didn't display the records when I was referring to the tables. It seemed to work, so I stuck with it. And that's the same reason why my main form is based on the query.,
So, uhmmm, what do you suggest I should do?
ScottGem
Frankly, I think you are trying to get too fancy and doing things before you completely understand them. If you look at my sample HRIS database, you see I make use of subforms and the Access navigation tools rather then create my own.
suggest you use a main form/subform setup rather then what you have.
HAs for sorting. You can set the default sort order of your main table or set the Order of the form.
freakazeud
I agree with Scott (of course frown.gif
You can base your subforms on queries or tables itself. Your one query is way to complex and has way too many fields and it is not being used anyway. Try to break down the data.
HTH
Good luck
Kristy9910
Well, I still haven't got my school name to sort alphabetically. I tried your button with the requery, but still no luck. I'm pretty confident I have the correct record source. I am working with subforms. My main form lists the district, and the subform lists the schools in the district. My forms are based on tables and not queries (which is in conflict with what you previously suggested). I'm attaching a file that includes a print screen of my form as well as my database diagram (relationships). Thanks for your continued support.
ScottGem
Kristy,
Right click on the control you want to sort by and choose Sort. Then in the Order By property of form make sure the correct sort field is shown.
Kristy9910
I tried that, and even though the correct field is in the Order By property of the form (It's showing LA_Schools.Name) it still isn't working. Does this have anything to do with the fact that I am woring in Access 2002 connected to SQL Server?
ScottGem
Well having Name as a field name could be a problem since Name is a reserved word in Access.
Is LA_Schools the table name? Is the subform bound to La_Schools?
Kristy9910
Here are two of my tables with the column names of each table
LA_DISTRICTS (main form)
ID
DistrictName
CDSNumber
LA_SCHOOLS (sub form)
ID
DistrictID
Name
ParentField: LA_DISTRICTS.ID
ChildField: LA_SCHOOLS.DistrictID
Sometimes I have to add new schools to a particular school district, but that new school appears as the last record. I would like the new school entry to be sorted within the other school names alphabetically.
The question is: How can I get the school names to be alphabetical after I close the form and then re-open the form.
ScottGem
The Form is not going to sort until the form is requeried or reopened. If the from is not sorting correctly I suspect its due to using Name as the fieldname.
brasco1234
Kristy-
I am a total Access newbie but I was monitoring this post because I had a similar thing I wanted done. I found a solution on the boards from a while back and it works for me, hopefully it will for you. It may be incredibly basic and you already tried it-if so, I apologize, but once again it worked for me.
I put the following code in a Event Procedure in the AfterInsert event of the Form body (the tab in the upper left corner of your form).
e.OrderByOn = True
Me.OrderBy = "FieldName ASC"
Me.Refresh
The field name should be the field you want sorted by, in my case its a field called "LastNme" which is my customers' last names (obviously). This does not sort the TABLE automatically, unfortunately. But the form sorts perfectly. The way it works for me is after I complete a new record, it automatically sorts and takes me back to the first record. I hope you can use this.
Chris
ScottGem
Chris,
The reason it doesn't sort the table is because you aren't applying a sort to the table, only to the form. It is really immaterial how a table is sorted. What you want to do is apply your sorts where the data is viewed. Since, in most cases, one doesn't usually work directly with tables, you apply your sorting to Forms, Queries and Reports.
When you apply a sort manually on a Form, it automatically sets the the OrderByOn property to True. That's why I suggested that Kristy manually set the sort, then save the form. From there the OrderBy property should work.
Kristy9910
I finally got it to work. Thank you so much for your help.
Kristy
ScottGem
glad to assist
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.