How to make a foreign column (instead of the foreign key) to be usedby built-in sorting

[Yarik]

After using MS Access 2003 (ADP project) for quite a while, I've just
realized that I don't know how to implement something that appears to
be a very common requirement...

Let's say there are two tables, Customers and Orders. Each customer
has an ID and a Name. Each order refers to the customer using
CustomerID column. I have a continuous form to view/edit orders.
Instead of showing customer IDs, the form uses a combo box that has a
visible column showing customer names and a hidden column bound to
CutomerID column in Orders table.

Now, the problem is: When a user sets input focus to the combo box and
invokes Access' built-in sorting function, the orders are sorted by
customer IDs, whereas it is obviously desired for them to be sorted by
customer names. The question is: is there any practical way to make
the form work as desired (preferrably without giving up the
convenience of having the combo box)?

 

[Albert D. Kallal]

Are you using the combo box for display only, or do people actually edit the
cust name.

if you **just** using the combo box for dispaly, then that is VERY poor
choice.

Why not just build a the continues from based on a query that joines in the
2nd table (customers) to pull and "dipslay" the cusotmer as an actuall
collom. that way your sorting problme is solved...

Now, the problem is: When a user sets input focus to the combo box and
invokes Access' built-in sorting function, the orders are sorted by
customer IDs, whereas it is obviously desired for them to be sorted by
customer names. The question is: is there any practical way to make
the form work as desired (preferrably without giving up the
convenience of having the combo box)?

Ok, assuming you need the combo box, you likely have to put a column in that
is as left join of the customer table to get that column. You perhaps just
size the combo box quite small as to display only the first few characters,
and then place larger column beside the combo box which is from the customer
table and display the full name (when you change the combo box...that text
box will change).

It just not clear if you allow editing in this continues form or not....

 

[Yarik]

Are you using the combo box for display only, or do people actually edit the
cust name.

I'm using it to let the users change the order's reference to the
customer (to make order belong to another customer, if the user
chooses so).

if you **just** using the combo box for dispaly, then that is VERY poor
choice.

Sure, but that's not the case.

Ok, assuming you need the combo box, you likely have to put a column in that
is as left join of the customer table to get that column. You perhaps just
size the combo box quite small as to display only the first few characters,
and then place larger column beside the combo box which is from the customer
table and display the full name (when you change the combo box...that text
box will change).

Not sure that I am doing exactly what you are describing here.
Essentially, combo box has two columns - customer ID (invisible) and
customer name. The first column is bound to the CustomerID field in
the form's recordsource. Obviously, I would like to keep it that way.

It just not clear if you allow editing in this continues form or not....

Yes, I want to allow editing. And yet allow sorting by customer name.

 

[Albert D. Kallal]

I gave the two possibilities based on the scenarios that you have.

Since you need to edit the data, and it not just for display purposes, then
you can use my 2nd suggestion.

<quote>
Ok, assuming you need the combo box, you likely have to put a column in that
is as left join of the customer table to get that column.
</quote>

In other words you have to display that "text" as another column on the form
and then you can use that collum for sorting. Thus the suggest was to use a
"left join". Perhaps I was not clear, but this simply implies you have to
base the form on a query, and not the table. the the strop in your main at
a table, and then left join in the the description from the other table.
Doing this will give you an actual description column that is sortable.

Make sure you don't forget to disable editing on this description field,
since when you left join in other fields from another table, you can
actually edit the displayed informaton on the form...(and I don't think you
want that to occur in this case).

 

[Yarik]

Hello Albert,

I really appreciate all your help, but somehow I still do not
understand exactly what are you suggesting.

Let me try to be more specific and precise. Let's say the Orders table
has three columns: ID, Description, Customer_ID; and the Customers
table has two columns: ID and Name. I need a form that would allow
users (a) to edit an order (including change of its reference to a
customer) and (b) to sort orders by customer name.

The first variant:

Form
RecordSource.......: select * from Orders

Combobox
DataSource.........: Customer_ID
Columns............: 2
RowSource..........: select ID, Name from Customers
BoundColumn........: 1
ColumnWidths.......: 0"
Locked.............: No

This variant provides full order editing capabilities, including
change of the reference to a customer by changing selection in the
combobox (which shows only customer names). But this variant does not
provide sorting by customer name.


Now, let's consider the following variant:

Form
RecordSource.......: select
Orders.*,
Customers.ID as Customer_ID1,
Customers.Name as Customer_Name
from
Orders left join Customers
on Customers.ID = Orders.Customer_ID
Unique table.......: Orders

Combobox
DataSource.........: Customer_Name
Columns............: 2
RowSource..........: select ID, Name from Customers
BoundColumn........: 2
ColumnWidths.......: 0"
Locked.............: yes

This variant does provides sorting by customer name: if user sets
focus to the combobox and invokes the built-in sorting function, the
orders will get sorted exactly as required - alphabetically by
customer name. However, in this variant the combobox cannot be used to
change the order's reference to the customer (and yes, it should be
locked to avoid editing names of the customers!). As a matter of fact,
the combobox in this case is not better than a simple textbox.

I was wondering if there is any simple way to get best of both
variants: to keep the combobox bound to the Customer_ID column, but at
the same time get Access' built-in sorting function somehow use the
column corresponding to combobox's second column (instead of using the
column specified as the combobox's DataSource).

Using the more specific sample context defined above, could you please
clarify what were you suggesting me to do?

Thank you,
Yarik.

 

[Albert D. Kallal]

I NEVER EVER said that you going to modify or change the combo box.

Why on earth would you change the column that the combo box is bound to? I
NEVER suggested that you change this at all. Where did I even hint at such
nonsense?

I simply stated that you going to add additional column called "name" to
your form. End of story. You will still continue to use your combo box as is
that displays the name, and is bound to the id field.

The only real obvious drawback to my suggestion is that you will wind up
with the customer name being display two times (two columns). Thus if you
sort on the combo box, you set the order by customer id, and if you sort on
the "new" column that displays the name you be sorting by customer name.

Now, it quite likely that you don't need the ability to sort both ways (but,
it might be bonus).

So, it is quite likely that you don't really need the "name" column
displayed two times (one with the comb box and again as a standard column),
but this will give you the sorting ability and will do so without you having
to change one line of code or modify any of the existing controls on the
form.

You have to one additional column however. You could size the combo box so
small that you only really see the dropdown" arrow part. So, you not really
waste much space and the "name" column likely will not be displayed two
times this way. So, they can expand the combo box for selection, and when
they choose a value..that "new joined in" column we added will instantly
update to display the customer name.

I will often have a form with 4-5 different combo boxes, and when it comes
time to build a report...how on earth in the past did you pull in the
description values? (answer: you simply build a query, and drop in the 4-5
additional tables + descriptions fields from those tables -- I suggesting
that you do the same thing with the ONE customer table in your form. This
naturally how we display and pull data from other tables when using
something other then a combo box to display the "text" value in place of the
pk id.

 

[Yarik]

I NEVER EVER said that you going to modify or change the combo box.

Why on earth would you change the column that the combo box is bound to? I
NEVER suggested that you change this at all.  Where did I even hint at such
nonsense?

Well, I guess I just misunderstood you, sorry.

I simply stated that you going to add additional column called "name" to
your form. End of story. You will still continue to use your combo box asis
that displays the name, and is bound to the id field.

The only real obvious drawback to my suggestion is that you will wind up
with the customer name being display two times (two columns).

Unfortunately, this drawback is a show-stopper. Seeing the same data
twice is too confusing for a user. (Especially taking into account
some unpleasant effect that I mention below...)

Also I am not sure it would work for datasheet view (but I did not try
it yet).

You have to one additional column however. You could size the combo box so
small that you only really see the dropdown" arrow part. So, you not really
waste much space and the "name" column likely will not be displayed two
times this way. So, they can expand the combo box for selection, and when
they choose a value..that "new joined in" column we added will instantly
update to display the customer name.

This idea was very promising until I tried it. Unfortunately, there is
one unpleasant effect that ruins everything: when user makes a
selection using the combobox, the text field that shows the customer
name remains unchanged until user actually updates the record (e.g. by
moving to another record). Very confusing.

But it is very close!

Anyway, I really appreciate your sharing of all those ideas. Thank
you!

BTW, this is a relatively simple problem (to have a control bound to
one field and have it display/sort another). I have a use case when it
would be nice to have a control bound to one field, display another,
and sort by yet another, third field. )

I will often have a form with 4-5 different combo boxes, and when it comes
time to build a report...how on earth in the past did you pull in the
description values? (answer: you simply build a query, and drop in the 4-5
additional tables + descriptions fields from those tables -- I suggesting
that you do the same thing with the ONE customer table in your form. This
naturally how we display and pull data from other tables when using
something other then a combo box to display the "text" value in place of the
pk id.

Fortunately, the project I am working on does not require too many
reports. I am saying "fortunately" because, IMHO, Access reports are
R.P.I.A. Wherever possible, we prefer to use different approach:
generate an XML document with all the data necessary for the report
and then use XSL to produce either HTML or PDF. Makes it much easier
to parameterize reports, maintain consistent look-and-feel, and - most
importantly - nicely separate the UI designer's work from programmers'
work.

 

[Albert D. Kallal]

Unfortunately, this drawback is a show-stopper. Seeing the same data

twice is too confusing for a user.

You can try the re-sizing the combo box so small that you ONLY see the drop
down arrow. You then place this to the "right" of the text box that displays
the value. (you will not see the column two times and the result looks very
much like a regular combo box).

This idea was very promising until I tried it. Unfortunately, there is

one unpleasant effect that ruins everything: when user makes a
selection using the combo, the text field that shows the customer
name remains unchanged until user actually updates the record (e.g. by
moving to another record). Very confusing.

Hum, strange, I am not seeing that behavior. In my test, the text box
updates right
away when the combo box is selected. You could/can put in a me.refresh in
the after update event of the combo box to force this issue, but in my test
I don't need the me.refresh.

I will be the first to admit that the approach suggested is not ideal, but
it is possible
workaround...it really depends on how bad you need the sorting by right
clicking.

By the way, I think perhaps specify a right click menu for the combo would
be even be less of kluge here. So, when a user right clicks the combbox,
your custom menu would give two options, ascending, descending. So, we would
simply have the right click menu sort based on the description column (name)
we just added as per above (you would NOT need to place this column on the
continues form, but ONLY ensure it is part of the base reocrdset for the
form.

The code behind the right click menu (place that sorting code in the form
code module) would look like:

me.Orderby = "Customers.Name"
me.Orderbyon = true

And


me.Orderby = "Customers.Name DESC"
me.Orderbyon = true


The more I think about this...I would go with the right click idea.

 

[Yarik]

Hum, strange, I am not seeing that behavior. In my test,
the text box updates right away when the combo box is selected.

Do we have the same version of MS Access? I'm using Access 2003...

You could/can put in a me.refresh in
the after update event of the combo box to force this issue, but in my test
I don't need the me.refresh.

In my case, once selection in combobox is made, user remains in the
"record editing mode". The record is not updated yet and, in
particular, user still can cancel whatever changes he has made so far
(including selection in combobox). I did not try to call Me.Refresh,
but something tells me it will terminate the "record editing mode" -
either by cancelling all the changes or by updating the record
(neither of which is desireable, of course).

it really depends on how bad you need the sorting by right clicking.

That's the right way to put it. In my case... well, more precisely, in
some specific form in my current project that triggered this question,
I do NOT need it badly enough to justify any solutions that require
"twisting Access' hands" too much.

By the way, I think perhaps specify a right click menu for the combo would
be even be less of kluge here.

Maybe. But so far we have had very bad experience with substituting
Access' built-in popup menus by custom ones. Last time one of our guys
tried this to solve some other problem, we got some weird issues
(overall, the application became very unstable). Maybe that was just a
coincidence, but from then on we decided that menu substitution
qualifies as "twisting Access' hands too much". A no-no unless it
would solve a life-or-death kind of problem.

BTW, even if the previous idea with "semi-hidden" combobox somehow
worked, on our "twisting Access' hands" scale it probably would be a
borderline case. Our general philosophy with this ancient tool is:
don't try to deviate too far from Access default functionality and
behavior; otherwise, the cost of struggling with the tool usually is
not justified by the REAL benefits that users get in exchange. It's an
internal product, and we just cannot afford struggling with the tool
too much.

 

[Albert D. Kallal]

Do we have the same version of MS Access? I'm using Access 2003...

Yes, we do. You can find a working sample here:

http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm

(download the knowledge base sample).

Maybe. But so far we have had very bad experience with substituting

Access' built-in popup menus by custom ones. Last time one of our guys
tried this to solve some other problem, we got some weird issues
(overall, the application became very unstable). Maybe that was just a
coincidence

I been answering questions in this newsgroup for a bout 6-7 years. About
20,000 questions are posted in on a monthly bases. And, I have NEVER EVER
seen a question posted that hinted ANYTHING about stability
because you use short cut, or replaced one of the built in ones.

And, virtually ALL OF my applications that I deploy are runtime
enabled, and ALL HAVE HAD shortcut menus for 7+ years now.
Never a problem (and, a good
portion of those clients do have full version of access also). So, I
sincere doubt shortcuts are problem (they are not in my own experiance
and the questions in the newsgroups bear this out (over 1 million
questions).

Try my sample download. I also put in the combo box that updates the "joined
in" field, and I also put a right click menu item on the combo box to sort
ascend/desen (note that the code for the right click menu is in the forms
code
module).

 

[yaroslav.mezheritsky]

Try my sample download. I also put in the combo box that updates the "joined
in" field, and I also put a right click menu item on the combo box to sort
ascend/desen (note that the code for the right click menu is in the forms
code module).

Interesting! In your sample it works just like doctor ordered. But it
still does not work that way in mine.

Probably I forgot to mention that I have an ADP project, not an MDB
one. Do you think this may be the reason why Access form behaves
differently in my case?

I been answering questions in this newsgroup for a bout 6-7 years. About
20,000 questions are posted in on a monthly bases. And, I have NEVER EVER
seen a question posted that hinted ANYTHING about stability
because you use short cut, or replaced one of the built in ones.

Well, maybe you are right. Maybe the problems we had were not caused
by popup menu substitution. Like I said, it might have been a
coincidence.

At the same time, do you think that fiddling with shortcuts in an ADP
project might cause any problems?

I'll try to investigate this option on my own.

BTW, I more or less understand how did you provide custom _popup_ menu
for the combobox and how does your code make sure that this custom
popup menu sorts by the proper foreign field instead of the foreign
key. However, what I do not understand (yet) is why does it also work
that way when I invoke the _built-in_ sorting commands (the ones
invoked via toolbar or via menu items in the top-level "Records"
menu). Is it easy for you to explain this phenomenon here or should I
go RTFM?

Thank you so much for the sample and for all your help!

Yarik.