Return combobox tesxt rather than ID number

[bjornas]

I make a database to help me with grocery shopping and to compare proies between several vendors.

On the main form, there is a combobox for "compare unit". This used to make sure that all comparisons are made with the same unit. It is populated from a "unit table" consisting of an ID field and a text field; the latter with items such as "kg", "litres" and so forth. The value is stored in field called "Compare_unit"

I want to use the Compare_unit field elsewhere on the form, as part of a calculated field, like this: "Buy XXX kgs of YYY at a price of ZZZ, totalling AAA".

However, the calculated field only returns "Buy XXX 6 ...", where 6 is the ID field in the "unit table". I want the calculated field to return the text field corresponding to the ID-number.

What am I missing here?

Rgds., Bjørn Olav
Oslo, Norway

[Micron]

You'd need to refer to the combo column whose value you want. Column count is zero based so 0 is first column. Not sure what you mean by calculated field here; they're usually a bad idea. If you mean concatenated field, not so bad but not a great idea either. In a relational db what you really should be storing in table fields are those numbers (6) and you show the related values (Kg) on your forms and reports.

[bjornas]

How do I refer to a combobox column?

[Micron]

If you don't know how to do what you're advised to do, then do a bit of research work (like Google) for your own edification instead?
Here's just one of the results

http://access.mvps.org/access/forms/frm0058.htm

[bjornas]

Would you believe me if I said I of course tried googling before posting the question? My post was because my googling did not result in anything that I am able to understand and get to work.

Yes, I am a novice with VBA for Access, but I wouldnt ask questions if I knew how to go about it.

I found the solution myself, and I would like to share it in order to demonstrate the entry level of Access.

I editet the Row Source property of the combobox, There, I found a three column setup (very much like the query setup), one for the "ID" field, one for the "Unit" field and a third, which was also labelled "Unit"; all from the "Units" table. The second "Unit" -labelled column was particular in the sense that unlike the first, it was sorted and the "visible" checkbox was unchecked.

When I checked the "visible" box, the query thus produced obviously contained two "Unit" columns (one with heading "Expr1001" and one with heading "Unit"- not very useful itself. But lo and behold - now the calculated field returns the correct data as entered in Compare_unit elsewhere in the form. Why was that? The other "Unit" column was indeed visible? Why would I need two?

Not very intuitive, and no way I would have been able to google that solution. But the important thing is; it works.

Rgds., Bjørn Olav

[Welshgasman]

It would be
Me.ComboName.Column(1) or whatever number the column might be.
Me.ComboName would be the bound column.

I always have the PK (ID) field first and hidden and generally the second column being the sole visible column, even when I have other data in other columns. Seldom have I shown more than one column in a combo.

Get on to YouTube, plenty of videos there for the basics and more advanced processes.

[Micron]

Would you believe me if I said I of course tried googling before posting the question?

Sure I would and it would help if you mentioned that. Try to understand that we donate our time and it can be a wee bit frustrating when the immediate response to a direction is "How?". We get it all the time and I often ignore such responses, preferring not to spoon feed those who can't be bothered to look for tutorials when they have been told what they need to do or look for. It didn't help that you didn't comment on whether or not the link I gave you was relevant or not, so if you're not going to comment on the info provided, expect that sort of response going forward. Also, it's somewhat disingenuous to repeat what has been covered perhaps thousands of times before, hence the expectation that the OP will find what they've been told they need - or at least comment on the links provided.

The answer to your question of how was contained therein

Note that the Column property of a combo box is "zero-based," meaning that the first column (field) in the combo box's Row Source is column 0, the second column is column 1, etc.

and

Me.cboPersonID.Column(1)

which could have been 0 or any other number, depending on your combo design.