Solved Wrong Data From Combo Box (1 Viewer)

AJJJR

Registered User.
Local time
Today, 05:57
Joined
Mar 19, 2018
Messages
56
Hi
I have a combo box, that I set up with the wizard, that is entering the wrong value into the designated field of the destination table.

The combo box is on a form--frmAccountsInputEdit--used to enter a new bank account into a table named tblAccounts containing two fields: [accountsBank], [accountsName], [accountsNumber] and an autonumber key field

I want the combo box to enter, into tblAccounts the value for the [accountsBank] field based on values in another table - tblBank

tblBank has an autonumber field as the key.

I want to use, from tblBank, the field [bankName]

So, a value from [tblBank].[bankName] would end up in [tblAccounts].[accountsBank]

The problem is that instead of that value, what is being entered is the value of the key field from tblBank

There are no lookup fields in either of the tables and I am using the wizard as I have always done.

Relevant info from the form frmAccountsInputEdit is:

Control Source - accountsBank
Row Source - SELECT [tblBank].[ID], [tblBank].[bankName] FROM tblBank ORDER BY [bankName];
Row Source Type -Table/Query
Bound Column -1

I'm not a sophisticated user of Access but I have used the wizard to make many combobox controls and have never had a problem. Would really appreciate any help you could provide.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:57
Joined
May 21, 2018
Messages
8,463
The bound column is the column that gets entered. You have 1 so that is the ID, 2 is the bankname. In code it is 0 based, but on the properties it is 1 based.
In this case you need to make sure your column count property is 2

If you want to see the name but store the id then
ColumnCount: 2
BoundColumn:1
ColumnWidths: 0";1"

Your query returns 2 columns (ID, Name), and it is bound to ID, but the formatting hides the ID column since it has zero width.
 

AJJJR

Registered User.
Local time
Today, 05:57
Joined
Mar 19, 2018
Messages
56
O.K
I did not realize that bound column was 1 based. In fact, I wasn't even sure what bound column was. I changed it to 2 and it does work. Thanks so much.

Any idea why this is happening. I am 100% sure that I chose the correct field in the wizard, and redid the combo box several times.

Thanks Again. It was driving me crazy.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:57
Joined
May 21, 2018
Messages
8,463
Do not know why but just check the important properties after the wizard.

ColumnCount:
BoundColumn:
ColumnWidths:
ControlSource:

If you do this in code and want the value of the second column and it is not the bound column it would be
x = me.MyComboBox.column(1).value
If you simply want the bound column regardless of which column
x= me.MycomboBox.value
 

AJJJR

Registered User.
Local time
Today, 05:57
Joined
Mar 19, 2018
Messages
56
Do not know why but just check the important properties after the wizard.

ColumnCount:
BoundColumn:
ColumnWidths:
ControlSource:

If you do this in code and want the value of the second column and it is not the bound column it would be
x = me.MyComboBox.column(1).value
If you simply want the bound column regardless of which column
x= me.MycomboBox.value

Thanks again.
 

zeroaccess

Active member
Local time
Today, 07:57
Joined
Jan 30, 2020
Messages
671
It's not recommended to store the same text strings (names, etc) over and over in a database. Recording the key field is the best practice. It will reduce size and data requirements as the database accumulates records.

If you draw a relationship between the two fields, the database will do the lookup.
 

AJJJR

Registered User.
Local time
Today, 05:57
Joined
Mar 19, 2018
Messages
56
It's not recommended to store the same text strings (names, etc) over and over in a database. Recording the key field is the best practice. It will reduce size and data requirements as the database accumulates records.

If you draw a relationship between the two fields, the database will do the lookup.


Sorry but I'm not sure what you mean by "store the same text strings (names etc) over and over in a database. How am I doing that as opposed to ""Recording the key field"

Thanks
 

Users who are viewing this thread

Top Bottom