Opening Forms In Specific Order

[StampMan]

Hi,
How can I change the order that my forms open in? I do NOT want to change my table primary key (Record Number) but I want my form to open in the order that is set by another unique field. So, rather than the forms opening in record number sequence they would open up in the sequence set by this other field in the table. I'm sure that I've achieved this before but can't remember how or where. Any advice greatly appreciated. :-)

[ranman256]

You control the form open order.
the last one you open will be on 'top' where the user sees.

use autoexec macro to open the forms in the order you want.

[ItsMe]

Are you referring to sorting the records by descending order or sorting records by descending order? This can be accomplished with a Query Object. Bind your form to a Query and manage sorting in the Query.

[StampMan]

I thought there was something along the lines of either "OrderBy" or "SortBy" somewhere in the forms property fields (when in design mode of course). I just want them to appear in ascending order but not by the record number (primary key) but by another field.????

[StampMan]

Thanks guys................... Found the property I needed to change to achieve this. :-)

Oooops.................... still having problems. :-(

[ItsMe]

There are properties in the form that can achieve this. I find it easier to do it in the query. I suggest using one approach, consistently. Otherwise, it may not be perfectly understandable why your application behaves a particular way.

[StampMan]

There are properties in the form that can achieve this. I find it easier to do it in the query. I suggest using one approach, consistently. Otherwise, it may not be perfectly understandable why your application behaves a particular way.

Hmmmmm, I see what you are saying. I've now encountered another problem. The forms now open in the order I want but with a numerical glitch. The field I want to sort my forms by is called 'UniqueCode'
It's a combination of letters and numbers, e.g. CGBQ-1024
But the sort order now comes out in this format....

CGBQ-112
GGBQ-1024
CGBQ-206
CGBQ-287
CGBQ-2113

See how it's sorting by fields with 1's first before moving onto 2's for example. CGBQ-1024 should come after 206 and 287 because it's a higher numerical value (thousands instead of hundreds) a proper sort would look like......

CGBQ-112
CGBQ-206
CGBQ-287
CGBQ-1024
CGBQ-2113

I wouldn't even know how to create or apply a query to remedy this............... Please help :-)

[ItsMe]

What you need to do is create an alias in a query. This alias will create a virtual column within your query. The alias will be the number part of your original field. For instance, the original field will have a Value of CGBQ-112 and the alias would have a value of 112.

MyAlias: Mid(FieldName, InStrRev(FieldName, "-") + 1)

You would type something like this in a new field in your query via the query designer. This new field will be the second column to receive a sort order. There are some additional steps that need to be taken in order for your main objective to be met. However, start by creating a query and an alias.

[StampMan]

Thank you, but,
It's official.................. I'm lost :/

[ItsMe]

You need to create something that will look at the letters and the numbers independently. A query can do this for you. It will manage it logically. You do not need to worry about why you would separate them. In the end, they will be put back together.

Start by creating a Query. Within the Ribbon and under the Create tab, click 'Query Design'. Add your Table(s). Create a SELECT query by bringing the desired fields into the grid at the bottom of the designer window. After that, create an additional column via copy paste. Use the code I provided in post #8. The only difference is you need to replace the text in red with the name of the field/column. Use the name of the column that has stuff like CGBQ-112 in it.


Go ahead and save your query. Now, you need to add some stuff so the query knows how to sort the data it retrieves. Go to your field, as it is represented within the grid, that contains the stuff with CGBQ-112 in it. In the row that is labeled 'Sort', use the pulldown to select 'Ascending'. SAVE your query.

Now, you can add a second request to sort by ascending. Do this in your Alias column you created when you pasted the code from post #8. Select Ascending from the pulldown provided within your Alias column. SAVE your query.

Now when you view your query via the View button under the Home tab of the Ribbon or by double clicking the Query from within the Navigation Pane, the sorting should work in the desired way.

Use your new query as the recordsource of your form.

[ItsMe]

I went ahead and tested the example I posted in post #10. Go ahead and go through that example. Afterwards, you will need to make a couple of adjustments.

You will need to create two alias'. I thought it might work with only one, but it does not. Also, the code in post #8 needs some additional stuff that will tell Access we are dealing with a number and not text.

So here are code examples for the two alias'

MyAlias: CInt(Mid([FieldName],InStrRev([FieldName],"-")+1))

MyTextAlias: Left([FieldName],InStrRev([FieldName],"-")-1)

With that, you will want to sort MyTextAlias first and sort MyAlias second. The priority will go on MyTextAlias. To do this, make sure you add MyTextAlias first. It should appear to the left of MyAlias.

As I said, go ahead and work through post #10 to get the alias thing under your belt. Then we can work on it together to fine tune it. I am attaching an example of a working query using two alias'.