Combo Box - Wrong Column in Table (1 Viewer)

xyba

Registered User.
Local time
Today, 10:21
Joined
Jan 28, 2016
Messages
189
Hi

I have a form with a combo box relating to a table of two columns (autonumberID, Name). The combo box properties are set to col count 2, bound column 2 and column width 0;2.

However, column 1 (autonumberID) is written to my main data table instead of column 2 (Name).

Can anyone please advise what I need to do to correct this?
 

xyba

Registered User.
Local time
Today, 10:21
Joined
Jan 28, 2016
Messages
189
Thanks, I tried that but it didn't work for some reason. Anyway, I've sort of solved it by deleting the ID field as it wasn't required anyway.
 

Minty

AWF VIP
Local time
Today, 10:21
Joined
Jul 26, 2013
Messages
10,355
To be honest it's much more normal to store the ID . If you are storing a name, let's say
"Maggie Smith" Unique ID 23
and she gets married and becomes Maggie Jones, you would simply update the master record and magically any reference to ID 23 would still return the correct current name.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:21
Joined
Feb 28, 2001
Messages
27,001
However, column 1 (autonumberID) is written to my main data table instead of column 2 (Name).

Access has this little quirk about certain collections - they number starting from 0, not 1.


Therefore, in a 2-column combo box, there IS no column 2. There is only 0 and 1.
 

zeroaccess

Active member
Local time
Today, 05:21
Joined
Jan 30, 2020
Messages
671
To be honest it's much more normal to store the ID . If you are storing a name, let's say
"Maggie Smith" Unique ID 23
and she gets married and becomes Maggie Jones, you would simply update the master record and magically any reference to ID 23 would still return the correct current name.
Not to mention storing that value as data type "byte" for the smallest possible data size. You only need larger than byte for lookup fields if you're going to have more than 255 options.
 

Cronk

Registered User.
Local time
Today, 21:21
Joined
Jul 4, 2013
Messages
2,770
@Zero Access Are you that worried about storage/db size that you wouldn't use an autonumber for the key field?
 

zeroaccess

Active member
Local time
Today, 05:21
Joined
Jan 30, 2020
Messages
671
@Zero Access Are you that worried about storage/db size that you wouldn't use an autonumber for the key field?
I use autonumbers for all PK fields. I'm referring to the storing of lookup values in your entry records. Since you're only storing a 5, 56, 92, etc, I use the byte format unless the lookup is going to have more than 255 choices.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:21
Joined
Feb 28, 2001
Messages
27,001
Sometimes when a natural short key exists it is preferable to an autonumber longer key, which for native Access tables I believe MUST be LONG.
 

zeroaccess

Active member
Local time
Today, 05:21
Joined
Jan 30, 2020
Messages
671
Sometimes when a natural short key exists it is preferable to an autonumber longer key, which for native Access tables I believe MUST be LONG.
That is correct. Just for clarity, here's an example of my use of the "byte" data type:

On my data entry form, I have a User ID and Status ID. Each of these will source values from tblUsers and tblStatus (primary key values) respectively, and record them to the "One" table in my one-to-many relationship as "byte" types since there are only 20 Users and 3 Statuses. Basically, since the primary key of those tables will never exceed 255, Byte is the most efficient way to record all of these small numbers. If I ever had 256 users, or 256 statuses, or anything like that, I'd have to upgrade them to Integers.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Sep 12, 2006
Messages
15,614
Not sure, but byte might be signed, and therefore represent -128 to plus 127.
I only use longs, personally.
 

zeroaccess

Active member
Local time
Today, 05:21
Joined
Jan 30, 2020
Messages
671
I could test that today - one of my lookup tables has 121 items.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:21
Joined
Feb 28, 2001
Messages
27,001
Dave, if the statuses are unique, signed and unsigned become immaterial for the lookup. I've used byte status codes. As long as it is a status code and you never do any math operation other than "=" or "<>" (equal or not equal) you will never know or care about signed vs. unsigned.

A byte status is good when the table having said status is huge. In my case, the status code on my largest table would have been in between 250K and 500K records. Avoiding four bytes for a LONG when one byte would do saved me between 750K and 1.5M bytes. On SQL server, you don't care because that's a drop in the bucket. But for a native Access back-end with its 2 GB hard limit (and about 1.2 Gb practical limit because of bloat issues), economies of storage are never wrong.
 

zeroaccess

Active member
Local time
Today, 05:21
Joined
Jan 30, 2020
Messages
671
The many table behind my subform will see about 100k records / year added, so I was planning ahead in being skimpy with the byte fields for lookups. :)

Also, consider network traffic needs to accommodate smaller vs larger chunks of data.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:21
Joined
Feb 28, 2001
Messages
27,001
@zeroaccess - true that. Network traffic also needs all the help it can get. Though a better approach if the traffic is across a network is to assure proper indexing since if a query CAN use an index, it will pull over the smaller index to be able to then selectively download the records its needs. But smaller records do indeed help when there are enough of them.
 

Users who are viewing this thread

Top Bottom