Record sort for SubForm

[Guest]

I have searched and read responses on my topic but want to ask for a bit more
detail.

My database has a personnel form that has an authorizations subform. Their
may be numerous authorization records for a particular person. I want the
sorted in a custom order such as TRAINED AND AUTHORIZED, ONE-TIME, CANCELLED,
TRAINING REQUIRED.

I followed the help file and created a small query that, using the SWITCH
function assigns a numerical value to my text value, then sorts on the
numeric. Thing is I can't figure out how to apply it to my form. Do I need to
make this query the entire record source for the form? I am concerned with
this as I don't really like using query based forms to update data. Or is
this much ado about nothing?

Thanks

 

[Al Camp]

RoadKyng,
You've done most of the heavy lifting already. Your "Sort Order"
solution is fine, but I'd like to add a suggestion.
Rather than use the query calcualtion to assign the "sorting values" to
your text (on the fly), use a table instead.
A table (ex. tblTransTypeSort) like this for example... use your own
names...
TransType SortOrder
Trained and Authorized 1
OneTime 2
Cancelled 3 etc.....

This will be a master lookup table for your sort order. In the query
behind your subform, link your data table through TransType to your
tblTransTypeSort, and include the SortOrder in your subform query, and sort
Ascending on that.

Why do it this way...?
This accomplishes the same thing as your method, BUT... If another
TransType is ever added (and you must always condsider that it might), you
won't have to go to every form, query, or report and add that new "sort
value" for the new Type.
Just go to table tblTransTypeSort, enter the new Type, and it's
appropriate sort value, and all your queries will automagically adjust.

Re: Using queries behind update forms... that's not a problem.
Actually, a query is, as far as the form is concerened, a table, but a table
with sorting and criteria capabilities as well.
I rarely use a "straight" table at all.

 

[Marshall Barton]

I have searched and read responses on my topic but want to ask for a bit more
detail.

My database has a personnel form that has an authorizations subform. Their
may be numerous authorization records for a particular person. I want the
sorted in a custom order such as TRAINED AND AUTHORIZED, ONE-TIME, CANCELLED,
TRAINING REQUIRED.

I followed the help file and created a small query that, using the SWITCH
function assigns a numerical value to my text value, then sorts on the
numeric. Thing is I can't figure out how to apply it to my form. Do I need to
make this query the entire record source for the form? I am concerned with
this as I don't really like using query based forms to update data. Or is
this much ado about nothing?

Much ado about nothing ;-)

It's a rare situation where you can use a table for a form's
record source, if only because of the sorting issue you're
asking about.

However, I do find creating a saved query for every simple
form somewhat of a pain. An alternative is to use an SQL
statement as the record source. This may not be advisable
in your specific case above, but instead of setting the
record source to a table, set it to:
SELECT * FROM table ORDER BY somefield

 

[Graham Mandeno]

Most forms are based on queries, so your concerns are, as you say, much ado
about nothing.

However, sorting by the result of a Switch function will be very slow.
Also, what safeguards do you have to ensure that the text entered in the
"Status" field is one of the four valid options?

Another approach would be to add another table, AuthStatus, with these
fields:
AuthStatusID: numeric (byte) primary key
AuthStatusText: text, no duplicates

Fill it with four (?) records, one for each possible status, with ID numbers
in the order you want them to sort.

Then, instead of a text field in your Authorizations table, use a numeric
(byte) field containing the corresponding AuthStatusID number, and related
(many-to-one) to the AuthStatus table.

You can then easily and quickly sort by this numeric field. Selecting a
status can be easily done using a combo box based on your AuthStatus table.

If you have more than one possible sort order - for example, you sometimes
want "CANCELLED" to come before "ONE-TIME" - then you can add additional
"Sort Order" field(s) to your AuthStatus table. It's then easy to base your
form on a query which includes both the related tables, and order the
results by whichever sort order you choose.

 

[Guest]

excellent advice from all of you. Thank you so much.

 

[Guest]

Al,
I fillowed your method and it works as far as the sorting is concerned.
However, when I try and add a new sub-record using a button, I get and error
message stating "Can't go to new record". The code line is: DoCmd.GoToRecord
, , acNewRec.
Does this have something to do with not finding a corresponding record type
in the Sort table? What I did was to set a relationship between the Status
field of the two tables (in the subform's query) I then added the sortvalue
field to the query and sorted on that.

 

[Al Camp]

RoadKyng,
Give ma a bit to check that out. I know I've used it in reports many
times, but I want to test in forms.
I'm suspecting that the subform recordset, with the Lookup table linked,
may not be "updateable".
(try a relationship of "show all in Data and only those in Lookup that
match")
I'll get back ASAP.
I'm thinking it should work, but the realtionship between your Data table
and the Lookup table may have to be tweaked.

 

[Al Camp]

RoadKyng,
Minor point first... please don't <snip> my reply when you respond to my
post. I want to be able to see what I replied to you vs. your new post.
I tested this, and it works... let's say your primary subform table is
tblTransactions, with a field called TransType.

I created a table (tblTransTypes) with these fields
TransType SortOrder
Trained and Authorized 1
OneTime 2
Cancelled 3 etc.....

I made the TransType field in tblTransTypes... Indexed No Dupes.
Now, in your subform, use this table to populate a combobox for each
record's TransType entry. Set the LimitToList = Yes.

The query behind the subform should link your tblTransactions to
tblTransType through TransType, "show all in the primary table, and only
those in TransType where they match."

My subform sorted according to the tblTransType SortOrder value, and I
could add new records to the subform with no problem. After adding a new
record/s, Refresh... or Requery... and the records will sort properly.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Al,
I followed your instructions and it worked perfectly. Thank you very much
for your time and assistance. I will definately keep you in mind for
consulting services on our comp/data management needs.

Sincerely

Gavin Hill

 

[Al Camp]

RoadKyng,
That's very kind of you to say, but please don't feel "obliged" in any
way.
All responders on these NGs do so completely voluntarily.
Glad your all set!