In a Base Form (NewMembers) I use a List Box to select a State or Province from a States table that contains SPid, SPName and Abv columns (in that order). A 2 character code (Abv) is then stored in the State/Providence of a new record. My issue is that I would like to “show” the spelled out state or province name (SPName) in the list box, but when selecting the state/province, I return the Abv field into the St/Prov form field. I can get one or the other, but not the combination. The Sql code is SELECT "Abv" FROM "States" ORDER BY "SPid" ASC I tried adding SPName,and concat (||) to no avail. I’ve spent considerable search time and thought I’d find a lot of examples, but nothing.
Any help is appreciated.
Joe C
Sorry you spent considerable time searching for this. Have posted this a time or two.
In your list box, on the Properties Data tab, Type of list contents should be Sql, as I’m certain you have. Your SQL statement should be:
SELECT "SPName", "Abv" FROM "States" ORDER BY "SPNAME" ASC
Then Bound field should be 1.
The first field in the Select statement is what is displayed in the list box (which is why the Order By change). The Bound Field is what is stored in the record with the first field selected being ‘0’ and the second field is ‘1’. Thus you will display the full state name and save the abbreviated name.
An aside here, don’t know why you have an ID field in this table as the State name or abbreviation is unique and can serve as the key here.
Edit:
Sample - ListBoxAltSave.odb
I copied the table from some web site and used it as copied, thus the SPid.
Yes, sql is selected. I tried your suggestion but it didn’t work. SPName was returned instead of Abv. I double checked your instructions (actually I copied and pasted them), and set bound field to 1. Did I miss something?
Should be OK. Not sure why it didn’t work. See sample in edited answer. Doesn’t use states but should give you same concept.
Can provide state sample is really needed.
I downloaded/looked at your sample and in both list boxes the choices are what is entered into the table fields, so it isn’t a comparative to "pick A, use B. The examples are similar to what I have now.
Maybe I don’t understand what you are looking for. As I understand, you have a new/existing member. For the state field in that record you want to use a list box. The list box should display the FULL name of the state but store the abbreviated name in the member record. Is that correct?
Also, did you actually look at the table? Not via the form! The form translates it right back from abbreviated to full for display only. On the sample the table PRIMARY, if displayed, will show that the ID field is stored.
I looked at the table after an update and it makes the entry/change correctly ( two digit code), but the form shows the full state/province name. Hmmm. A delima. do I change the form to reflect the long name to accommodate this change? When I select a different member, the two digit state now lists as the long name (or as much as can fit into the short box)…
As stated the list box is two way. Select a long name it saves the short. If the displayed record contains an abbreviated name matching one in the list box it will display the long name (or maybe even the one closest to it - never tried that).
If, however, you display this member field in say a text box, the abbreviated name will display because that is all it knows. Nothing to translate it.
Your decisions on what to display, store, translate.
Okay, thanks. It’s never easy, is it.
I apppreciate your insight and wisdom.
Informational only - The reason to all this is saving storage within the database. You want to have meaningful names displayed but the amount of space consumed can greatly increase with the number of records. In the sample provided, only the key is stored. This is a great space saver over what may be a very long name - i.e. stock number vs item name in auto parts.