Sort subform records on numeric values stored in a text field on Main Form

[Green]

Hello,

I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.

I am unsure of where to proceed from here. Any help would be much appreciated.

Brian

[ranman256]

put the sort order in the query that the subform is connected to.

[June7]

Options:

1. sort parameter in subform RecordSource

2. static setting of the OrderBy property of subform

3. code sets the OrderBy property of the subform.

[Green]

When you say put the sort order in the query, do you mean from the Advanced filter/sort? Or is there somewhere else you mean? Sorry I am learning as I go.

[Green]

The sorting sequence changes with each main form so would it need to be set by Code in the OrderBy property? How would I do this?

[June7]

If you want the sort order to be dynamic then need VBA code.

What are the rules for setting the criteria? Why would the sort order change just because moved to another record on main form?

[Green]

The subform (list of tasks) provides the relationship to the main form. Each main form is a maintenance Task Sheet made up of numerous tasks. However the tasks for each task sheet will not always be the same. Also the sequence of tasks within the subform on each main form(task sheet) will change. The ID for the tasks are what need to be sorted within the Subform but the sequence for the sortings exists within each main form.

Hope this wasn't too confusing.

[June7]

Confusing? None of it makes sense to me.

What do you mean by 'each' main form - how many 'main' forms are there?

What determines the sequence of tasks? If you don't want to order by the ID then what do you want to order by? Records must be ordered by values in some field.

[Green]

Sorry, there is only one main form. I meant as I move through the record within the main form.

The sequence right now only exists in a text field found within each record. I would like to sort the order in the sub form based on the sequence found within this text field. Each record has a new sequence within this text field. Rather than sort "1, 2, 3, 4, 5", I would rather define the sequence of the order "3, 4, 1, 2, 5". I do want to sort the order of the ID within the subform, just by a defined sequence.

[June7]

There is a field with a string value like "3, 4, 1, 2, 5"? Well, that won't work.

You don't sort the ID, you sort records. There must be a field that has values to define the sort order. Each record must have a single value in this field.

How many subform records are there? Always 5 - no more no less?

[Green]

I have only one subform in the mainform. There are 1236 records within the main form. The sub form associates anywhere from 1 to 65 tasks to each record. There are a total of 2663 tasks. Everything works fine for view except for the sequences found within the sub form that relates to the record in the main form.

[June7]

Okay, my comments still apply.

So the field could have a string with up to 65 comma delimited elements? Or what am I not understanding?

[Green]

Ok, so if I can not sort the records within the Subform with the ID #, should I create another column within the Table that is associated with the subform for sorting? My initial line of thought for the ability to sort the records within the subform based on numerical values in a text box were based on the following webpage.

https://support.office.com/en-us/article/Sort-records-on-numeric-values-stored-in-a-text-field-502a36c4-2b6e-4453-91b6-7e842d234157

Also, yes the field could have up to 65 comma delimited elements.

[June7]

That has no relevance to your stored CSV string.

As I said, field must have single piece of discrete data. That article concerns situation where single, discrete number values are saved in a text field instead of a number field.

[Green]

Here is a screen shot of what we have been talking about.