Form Will Not Sort!!!

[Guest]

Hello All,

I have been pulling my hair out on a simple problem. Hoping someone here
has the anwer. I am trying to sort a field on a form by coding it's "on
open" property. The field is a combo box. Here is the code:

Me.OrderBy = "Occurrence ASC"
Me.OrderByOn = True


It appears to group the same words into groups. However, the entire list is
not sorted in ascending order. Any help would be appreciated.

Regards
L

 

[Jeff Boyce]

Have you considered creating a query to underlie the form? You could set
whatever sort order you wanted in that query.

 

[Connie]

If you want just the combo box items to be sorted .. then on the Rowsource
property of the combo box click the 3 dots to the right ... this will take
you into what looks like a query. Then in the row that says "Sort" select
the field you want to sort on.

HTH
Connie

 

[Guest]

I have tried using the query builder to sort the form and selecting ascending
order, however, the same problem still persists. Very frustrating...

 

[Guest]

Ya, I'm trying to sort the entire row, not just the combox order...

 

[Marshall Barton]

I have been pulling my hair out on a simple problem. Hoping someone here
has the anwer. I am trying to sort a field on a form by coding it's "on
open" property. The field is a combo box. Here is the code:

Me.OrderBy = "Occurrence ASC"
Me.OrderByOn = True


It appears to group the same words into groups. However, the entire list is
not sorted in ascending order. Any help would be appreciated.

This sounds like you're suffering from the evils of a Lookup
field. This issue has nothing to do with the form and you
should focus your attention on the table, before trying to
make the form sort the way you want.

Note that a **Field** in a **Table** can not be a combo box,
it can only be a number, date or text. If it looks like a
combo box, it's just the formatting of the field's value
that's deceiving you into thinking about it the wrong way.

Most likely the field is a number that's used as a foreign
key into another table. The other table contains the text
you see in the combo box and want to sort on.

If this is correct, you should go into table design and
change the lookup property of the field to a text box so you
can see the field's actual value and plan accordingly.

 

[Guest]

Thank you Marshall! You are absolutely correct...However I went into the
table and changed the lookup property of the field to text. Number are now
stored in the field instead of text. This is due to getting rid of the look
up property. How can I resolve this problem...Any help would be appreciated.

 

[Marshall Barton]

In most circumstances, you can use a query that Joins the
two tables (on the field in question) and then add the name
field from the second table to the query's field list. With
that in place, you can specify the field as the sort field
or use it in the form's OrderBy property.

 

[Graham Mandeno]

As Marsh has pointed out, you are actually sorting on a numeric code which
is stored in the Occurrence field, not on the text. Presumably the
translation from code to text is represented in the records of another
table - let's call it Occurrences.

Instead of using your base table as the form's RecordSource, create a query
based on your table AND the Occurrences table. Join the two tables on their
related fields (if you have set up a relationship then this should have
happened automatically). In the query grid, add all the fields from the
first table (drag the * to the grid), and add the text field
(OccurrenceDescription ?) from the Occurrences table. Sort the query on the
text field.

This will give you a query which looks just like the original table, with
the addition of an extra field - the OccurrenceDescription.

Now, remove the Me.OrderBy... code from Form_Open and you should be fine.

 

[Guest]

Everyone, this was JUST the problem I've been struggling with. Thanks!

As Marsh has pointed out, you are actually sorting on a numeric code which
is stored in the Occurrence field, not on the text. Presumably the
translation from code to text is represented in the records of another
table - let's call it Occurrences.

Instead of using your base table as the form's RecordSource, create a query
based on your table AND the Occurrences table. Join the two tables on their
related fields (if you have set up a relationship then this should have
happened automatically). In the query grid, add all the fields from the
first table (drag the * to the grid), and add the text field
(OccurrenceDescription ?) from the Occurrences table. Sort the query on the
text field.

This will give you a query which looks just like the original table, with
the addition of an extra field - the OccurrenceDescription.

Now, remove the Me.OrderBy... code from Form_Open and you should be fine.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you Marshall! You are absolutely correct...However I went into the
table and changed the lookup property of the field to text. Number are
now
stored in the field instead of text. This is due to getting rid of the
look
up property. How can I resolve this problem...Any help would be
appreciated.