Sequential Numbers on Forms

[Hulm1]

I have a form "Job_Edit" with a subform. The subform called "JobTask" is a continuous form and includes a look-up combo box for a range of tasks. After I choose say 4 separate tasks, I can print a report in the correct order no problem because I inserted a text box with control source =1 and running sum in the report.

However, when I close the form and re-open it, the selected tasks have been re-ordered by their TaskID no. which of course is an autonumber. I have been exploring DMax and even manually entering a no in a text box next to each task and trying to use Order By.

Unfortunately this has all proved beyond me! Ideally, I would like the sort order to always be that in which the tasks were selected. Also ideally, if a new set of numbers is required, I would prefer these to be automatic. BUT they need to start at No.1 for each job in "Job_Edit".

Any help much appreciated.

Thank you

[Stewart Ross]

...However, when I close the form and re-open it, the selected tasks have been re-ordered by their TaskID no. which of course is an autonumber. I have been exploring DMax and even manually entering a no in a text box next to each task and trying to use Order By. ...

Hi. To avoid changing key field relationships for your tables - which would be a very bad thing to do if everything is otherwise working fine - the simplest thing I can think of which would resolve your difficulties for new records added is to add a new autonumber field called EntryOrder to the table which is the recordsource for your Job Task form. if Job Task is based on a query of the underlying table then all you need to do after adding the field is to include the new field in your query and sort on this field in ascending order.

If your Job Task table is not based on a query then create a new query, add all the fields of the Job Task table to the query (including the new EntryOrder field) and, as above, sort on this field in ascending order, then change the recordsource of your Job Entry form to the name of this new query.

A downside of this approach is that it may not do anything to overcome the current ordering of your existing records. It will ensure that all new job tasks are shown in entry order, regardless of whether the tasks are added all at once or in batches. The autonumber is really just providing a sequence number - it is not being used as any form of key field. The EntryOrder field will not necessarily be numbered in consecutive order (1, 2, 3 and so on), as new tasks may be added after adding other tasks for other jobs, but even so it will be sequentially numbered (1, 2, 7, 10, for instance). It is the sequential nature which will provide the ordering you seek without having to deal with incrementing a numeric counter field yourself.

-Stewart

[Hulm1]

Thanks very much for the reply. My table will not allow two autonumber fields, however. The primary key is the existing autonumber

[Stewart Ross]

Thanks very much for the reply. My table will not allow two autonumber fields, however. The primary key is the existing autonumber

Hi. The fact that you already have an autonumber field, although somewhat of a surprise to me, should allow the ordering of your records by entry order as I described.

Are you ordering your query on this field? Is there anything else which identifies tasks? If there is, order by these other fields first, then have the ordering of the autonumber field last in your query. You should then see your records in job and entry sequence.

-Stewart

[Hulm1]

The subform is not based on a query, just the table. I will choose from the combo box a specific task and then in the next record below, choose another task. It may be that the first task I choose has TaskID (the autonumber) 12. However, the next task I choose may have TaskID 3. When I close the form and re-open it, the records have been re-ordered to show TaskID 3 first.

Obviously this is not the point at all! As I want to show the tasks in the order in which they were selected. They will change from job (Main Form) to job.

Does this help?

Could I be looking for some dynamic counter?

[ADezii]

The subform is not based on a query, just the table. I will choose from the combo box a specific task and then in the next record below, choose another task. It may be that the first task I choose has TaskID (the autonumber) 12. However, the next task I choose may have TaskID 3. When I close the form and re-open it, the records have been re-ordered to show TaskID 3 first.

Obviously this is not the point at all! As I want to show the tasks in the order in which they were selected. They will change from job (Main Form) to job.

Does this help?

Could I be looking for some dynamic counter?

1. Try creating a Field named [Entered] {DATE/TIME} in your Table.
2. Assign this Field the Default Value of Now() which contains a Time as well as Date element.
3. Make the Record Source for your Sub-Form a Query containing the Table buit only now with the [Entered] Field as your Primary Sort (leftmost Field in Query Grid in Ascending Order).
4. Due to the Time Component of Now(), your Records may sort correctly.

[Stewart Ross]

Hi. The ordering you are getting is proving a problem because of your choice of key - tables based on autonumber keys will always be displayed in sequential order.

ADezii's suggestion of adding a date/time field is I reckon the best way to go in the circumstances.

As a piece of general advice it is usually better to define a query which includes all fields from the base table and base your form on the query, not the table. It allows much more scope for sorting records.

If you do add a date field as ADezii suggested, you will have to base your form on a query of your table, as you need to be able to change the ordering of records displayed from autonumber order to date order. You can't do this by basing the form directly on the table, because the primary key defines the default ordering of the table values, and with an Autonumber field as the key the table will always be shown ordered sequentially in autonumber order.

-Stewart

[Hulm1]

Thank you. That sorted the problem, After all that, however, it dawned on me that the Project Manager may wish to add a task. As that might be in the middle of the other tasks, my clever (or yours really) solution to automatic numbering ends up causing problems! So actually, manually entering the task order makes more sense after all.

Sorry to waste your time. That said, I just learned a good way of sorting by entry time!!!

Thanks all of you.

[NeoPa]

I've had that happen to me before.
The question was obviously asked honestly though, so you use whatever is the most appropriate idea available when it comes down to it.

[Hulm1]

Thanks again everyone who helped. How do we close these threads? Is it automatic? or do I do it?

Thanks

[NeoPa]

They don't get closed unless they are in breach of the rules.

As far as the question goes, they can be considered done with when you post saying you have a solution. That you've done so this one is no longer outstanding.