How to get value in a combo box into the table field, I only get the reference number

[hjnash]

Good morning all, I have created a combo box in my form and it has a few options that are linked to
my Category field in my table "Deposits". I tried several ways to get the value for a selection to
show up in the table but only the pointer number shows up. So In my form I enter a new record,
I select Auto: Gas from the combo box, save record. I check the table and it shows a 1 in the
Category field I need to see Auto:Gas. Any pointers here?

[Rod]

The 'value' of a combo box is the value of the bound column (usually column 0) which may be/often is hidden and is usually the primary key of the row source. The value you see is the value of the first visible column. So if you use something like mycombo.value (or using defaults, simply mycombo) then you are in fact retrieving the value of the bound column, not necessarily the value you see.

In order to retrieve the value you see, you must know which column it is in (relative to zero). Assume it is the second column then mycombo.column(1) will retrieve the value you want.

Get back to me if you want more help.

[hjnash]

Hello Rod and thanks for your reply!
I suspect that you are right regarding the column 0 being used. I deleted my combo and rebuilt it
looking for options in the combo build GUI for selecting what column to use but its not an option.
I checked for VBA code that may have been created but saw none; so how might I tell the combo
box to use column 2?

[Rod]

Can you save and post your db as v2007?

[hjnash]

I saved the file as an mdb for access 2003, From access 2013 I did not find an option for access 2007!

[Bob Fitz]

Why would you want to do this?
Storing the PK of the Categories table as a FK in the Deposits table is the right way to save your data. You should not be saving the description of the Category.

The numbers stored in the Deposits table don't mean much to us humans when we look at them, but raw tables are not meant to be viewed. The data held in them should be retrieved with queries and then displayed in forms or reports.

[Rod]

Bob, I agree wholeheartedly but wanted to see the data relationships before following up.

hjnash: v2003 is fine. I'll take a look today and get back to you.

[hjnash]

Good Morning I just saw the reply from Bob, I am a bit confused; I figured I did not explain my goals well.
I want to use a drop down box to enforce populating the Category field in the DepositCategory field with the same
spelling for each category avoid a typo. Therefore the table that is storing this data should have
the value of the selection not the index or pointer number. I expect Auto:Gass not a (1).

[hjnash]

Well today a little more coffee and I got it working!! I found that I can select the bound column to 2 and this allows the value in the
drop down box to be used. See attached jpeg file if you want. Thanks for your help. OH I am open to advice on this so I do it
in a proper manner.

[Bob Fitz]

Well today a little more coffee and I got it working!! I found that I can select the bound column to 2 and this allows the value in the
drop down box to be used. See attached jpeg file if you want. Thanks for your help. OH I am open to advice on this so I do it
in a proper manner.

Just because you can do it does not meant that you should! Please read my post #6. IMHO This is not what you should be doing.

[hjnash]

Hello Bob and thank you for helping me. To start I did read your post but I was not sure I understand why
some one would not want to have the value of the dropdown box to populate the database with a readable
value. I also wanted some more time to read about this tool (combo box). You noted PK and FK, well I had to google that
as I did not know what you meant; now I know what you are saying I am still not sure why we don't do this
in practice. In the photo below the value under Category was populated by selecting Category in the combobox.

[June7]

True, saving the autonumber that is set as PK is often advisable but I don't think absolutely has to be always. If a descriptor is short then it could just as well be the PK and the value saved. This eliminates the issues associated with lookup alias and table joins in queries. Memory is cheap so unless the db is going to get ginormous and will exceed 2gb, I don't see that the reduction in stored data is all that advantageous in this case.

If you want to save the descriptor, set it as PK so it cannot be duplicated and the ID field can be removed.

Sidenote: Payee has two e's.

[hjnash]

The data base is going to be small I suspect less than 5 megs. each month I would start a clean database for the
month. I don't need to keep or join the data. BUT, If using a combo box to store a value of data is bad I have to
learn the why, currently it makes perfect sense to do it. I suspect I am missing the point.

[June7]

Using a combobox is not the issue. It is the value saved from the combobox that is. Bob advises you save the autonumber value. I suggest that is not critical and saving the descriptor is a valid option in this case.

Why a new db every month and not just one?

[hjnash]

I only need to track where the money went one month at a time and make adjustments to the budget.
So the amount in one category is subject to change from month to month. But mostly I want to do a
new database each month because so far I can't come up with a way to get the balance in any
one category. So If I budget 600 for groceries and it the last week of the month I have no way to
see how much has been spent I have to use a calculator; therefor, I want to keep the entries small.
Also I am doing this for a learning challenge so I expect to change the design each month for a while.