How to simple change order in access form

Question

How to simple change order in access form

peter478

SSCrazy

Points: 2939
More actions
December 17, 2014 at 12:26 am

#292900

Hi all,
In my form I have a few record, where I want to change their order.
Is there a way how to do it?
My idea is that every record has own order number, and can be changed by user.
for example ...If I change record from sixth possition to possition one. Rest of all
is recalculated following order.
Is this possible?
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

The order of records being displayed on a form is determined by the record source for the form - so if your form has a SQL statement as its record source, and that statement has an ORDER BY clause, then the records will be displayed in that order. So if you want to change that order based on changes in the data or some other external source, you have to at a minimum refresh the form using either a macro or VBA, or in some cases you may need to dynamically change the record source in VBA. Hopefully that is enough to get you started.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

peter478 SSCrazy Points: 2939 More actions · Answer 2

WendellB (12/17/2014)
The order of records being displayed on a form is determined by the record source for the form - so if your form has a SQL statement as its record source, and that statement has an ORDER BY clause, then the records will be displayed in that order. So if you want to change that order based on changes in the data or some other external source, you have to at a minimum refresh the form using either a macro or VBA, or in some cases you may need to dynamically change the record source in VBA. Hopefully that is enough to get you started.

Yes, I use SQL statement for my form with ORDER BY clause, it is ok, but I need to also dynamically change the order of records, based on actuall view in form, and also user should also change the order of records.

I dont know how to ... at first I'm trying to number(sort) every record from 1 to N, in actual view form. This is also problem, how to do it...

WendellB SSCrazy Eights Points: 8678 More actions · Answer 3

To do as you describe really requires the use of VBA - macros are too limited. I presume this is a continuous form that displays multiple records, and you wish to change the order of the records being displayed either after one record is edited, or by the use of a combo box. In the first case, where the data has changed, you would typically use the AfterUpdate event on the form to run VBA that says Me.Requery.

In the second case, you would need more elaborate code that would change the Order By clause of the SQL string that is the record source. It would display a series of sort choices (By Name, By Date Entered, By Order Number, etc.), and depending on the choice made, you would modify the form record source SQL string accordingly. See this link VBA SQLstr to change subform record source ... for more details on the process.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

peter478 SSCrazy Points: 2939 More actions · Answer 4

WendellB (12/18/2014)
To do as you describe really requires the use of VBA - macros are too limited. I presume this is a continuous form that displays multiple records, and you wish to change the order of the records being displayed either after one record is edited, or by the use of a combo box. In the first case, where the data has changed, you would typically use the AfterUpdate event on the form to run VBA that says Me.Requery.
In the second case, you would need more elaborate code that would change the Order By clause of the SQL string that is the record source. It would display a series of sort choices (By Name, By Date Entered, By Order Number, etc.), and depending on the choice made, you would modify the form record source SQL string accordingly. See this link VBA SQLstr to change subform record source ... for more details on the process.

Yes it is continues form with multiple records.I need to change task priority for every record, and for this I want to use combo box, where i can change priority number. When a task with highest priority is done, it dissapier from the form view, and every record is re-counted for priority.

Do you know a way how to generate order number just in form for every record from 1 to N , without adding special sql int column into table?

WendellB SSCrazy Eights Points: 8678 More actions · Answer 5

The combo box should be a useful way of setting the priority for each record - you can make the priority a numeric column, and hide the actual field while providing a text field that describes the priority associated with that number. One a record is marked complete, you can use the expression Me.Requery to display the remaining records.

I'm not sure I understand what you mean by "order number" - if you are talking about the sequence that records are entered, the way we do it is with and Identity property that is automatically incremented by SQL Server as new records are entered. There are other methods that involve finding the maximum value of a column and then incrementing it, but that become an issue with systems where there are a number of users entering new records. Hope this helps.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!