Listbox in subform not working

Al Kramer

Member
Local time
Today, 09:56
Joined
Jan 7, 2025
Messages
30
I have a very simple Membership application. only Three tables, Demographics, Payments, and a gift_type table to supply the list of gift types to the gift_type field in Payments
1737904647326.png

No matter how I set the Bound Property in the listbox the list box returns column 1 (the id)
Columns are set to 2.

1737905021795.png


1737905205016.png



I have had quirky problems in subforms before.
Any help appreciated.

Thanx
Al
 

Attachments

  • 1737905128466.png
    1737905128466.png
    10.7 KB · Views: 13
I have a very simple Membership application. only Three tables, Demographics, Payments, and a gift_type table to supply the list of gift types to the gift_type field in Payments
View attachment 118233
No matter how I set the Bound Property in the listbox the list box returns column 1 (the id)
Columns are set to 2.

View attachment 118234

View attachment 118236


I have had quirky problems in subforms before.
Any help appreciated.

Thanx
Al
In addition to the number of columns, you can specify the width of each column in the list box. Set the width of the first column to 0 to hide it in the interface.

You a much more serious problem, though, as reflected in the question and screenshots.

First, there is no referential integrity set between Payment and Gift_Type tables. That allows users to put any old "G_Type" value they please in the Payment field. Probably not what you want to have happen.

Second, your screen shot indicates that you are actually storing the value of the Gift_Type, rather than the Primary Key, in the field called "Gift_Type". Another way in which your users can end up putting any old value that they can think up in that field, not necessarily one of the approved values in the Gift_Type table.

This set up could be described as "suggesting" what users might want to enter rather than "requiring" them to enter only valid values.

You can correct this problem by replacing "Gift_Type" in the Payments table (which appears to be a Text field), with a number field of Long Integer Datatype. Call it "Gift_TypeID" to reflect the fact that you'll store the Primary Key from the "Gift_Type" selected as a Foreign Key.

Also, Bind the list box to the first column--the one containing the Primary Key, i.e. "Gift_TypeID".
 
Thanx GP

I put the referential integrity late in my efforts, thought it might help. Made no diffrence.

I DO want to place the gift type NOT the key(id) into the payments table.

Again Thanx
 
Thanx GP

I put the referential integrity late in my efforts, thought it might help. Made no diffrence.

I DO want to place the gift type NOT the key(id) into the payments table.

Again Thanx
I removed the referential integrity, deleted and recreated the listbox. bound column 2 , coulmn count 2
Still get the 1st column returned.
 
Thanx GP

I put the referential integrity late in my efforts, thought it might help. Made no diffrence.

I DO want to place the gift type NOT the key(id) into the payments table.

Again Thanx
I think it’s safe to say that no long time Access developer would store the gift type as opposed to the ID. Can you provide a justification for storing the text?
 
You need to fix your relationship. Did you do that? Relationships are ALWAYS Primary key of the 1-side table to data field of the many-side table. You have it on data field to data field.

Then you bind to column 1 and adjust the column widths property to set the first field length to 0 and the second field to 1. That hides the first field and shows the second field.

Just to be clear, it is the column widths property that controls what columns show and it is the bound column property that controls which value gets saved when you pick a value from the column.
 
I have a very simple Membership application. only Three tables, Demographics, Payments, and a gift_type table to supply the list of gift types to the gift_type field in Payments
View attachment 118233
No matter how I set the Bound Property in the listbox the list box returns column 1 (the id)
Columns are set to 2.

View attachment 118234

View attachment 118236


I have had quirky problems in subforms before.
Any help appreciated.

Thanx
Al
Hi Al
Are you able to upload a copy of your database with no confidential data?
 
You need to fix your relationship. Did you do that? Relationships are ALWAYS Primary key of the 1-side table to data field of the many-side table. You have it on data field to data field.

Then you bind to column 1 and adjust the column widths property to set the first field length to 0 and the second field to 1. That hides the first field and shows the second field.

Just to be clear, it is the column widths property that controls what columns show and it is the bound column property that controls which value gets saved when you pick a value from the column.
1737914618651.png

Current state of relationships.
Again, I's like to store the Gift_type NOT the key(Id) to payments
 
If you don't want to store ID, then remove the autonumber from Gift_Type and make G_Type the PK. As long as the autonumber is present in the table, it is the AUTONUMBER which should be the PK and it is the PK that is stored for a lookup field, NEVER the text field when an ID is present.

Also, naming your PK's "ID" is poor practice and just obfuscates your relationships. When you store the FK, either use the exact name of the FK when it is used as a PK OR, add a suffix. GiftTypeID_FK

PS, removing the relationship doesn't fix the problem;)

Also, all caps just makes the column names hard to read and you should never use embedded spaces or special characters in your column names
 
Again, I's like to store the Gift_type NOT the key(Id) to payments

Well, you can lead a horse to water, but you cannot make them drink. :(
 
@Al Kramer You're very new here and seem to be very inexperienced. You have been given good advice on how to handle lookup fields correctly and yet you ask us again how do do it incorrectly. Do you know something we don't know? If you post your db, someone will fix this lookup field so it works correctly for you so you can see how it is done. I don't know what your future plans are for this application but without a firm foundation and a solid schema, you are doomed to hassle after hassle as you try to make your wrong choices work.

Did you not understand my suggestion in #11?
 
I removed the referential integrity, deleted and recreated the listbox. bound column 2 , coulmn count 2
Still get the 1st column returned.
Did you not bother to read post #2? :(
 
If you don't want to store ID, then remove the autonumber from Gift_Type and make G_Type the PK. As long as the autonumber is present in the table, it is the AUTONUMBER which should be the PK and it is the PK that is stored for a lookup field, NEVER the text field when an ID is present.

Also, naming your PK's "ID" is poor practice and just obfuscates your relationships. When you store the FK, either use the exact name of the FK when it is used as a PK OR, add a suffix. GiftTypeID_FK

PS, removing the relationship doesn't fix the problem;)

Also, all caps just makes the column names hard to read and you should never use embedded spaces or special characters in your column names
Pat,
Thanx for your time & patience.
I followed all your advice & still no luck.
I just deleted the Id field from Git_type & changed the bound column & column count to 1 & it worked!

Again, thanx for hanging in with me.
Al
 
Pat,
Thanx for your time & patience.
I followed all your advice & still no luck.
I just deleted the Id field from Git_type & changed the bound column & column count to 1 & it worked!

Again, thanx for hanging in with me.
Al
Hi Al
It looks like you do not want to take good advice.
If I were you I would re-read all posts again to see where you are going wrong.
 
I would like to introduce you to my IL :)
 
goto desitgn view of your subform and on the "Gift Type" column, change
the Bound Column: 1 (property sheet->data)

next goto Format tab.
Column Count: 2
Column Widths: 0;2.54 (if your unit is "cm", 1 if "in")

save and view your form.
 

Users who are viewing this thread

Back
Top Bottom