Solved How to enter data linked to one entered in combobox? (1 Viewer)

[QMarshal]

Hi! Thank you so much for looking this one up! Ran into a simple problem learning MS Access.

Created a simple form, comboboxes and such, Linked form and comboboxes to needed columns. But! In Data source property I set another table, joined to main one, so form, once displayed, will show understandable text, instead of IDs, that populate my main table. The problem is that now, when user tries to select text value, while working with form, Access tries to set numeric value of ID in my main table to text value of selected option. I need to find a way to reconvert text through 2nd table to ID, that I'd need to get into my main table. Thanks in advance for help. Can't get screenshots cause working in another lang, so there's no point.

 

[pbaldy]

Make sure the bound column property of the combo reflects the ID column rather than the text column.

 

[bob fitz]

I don't understand what you're trying to do exactly, but you may find it helpful to know that the first column of a combo is often hidden but holds the ID of the table. The user actually selects a text value shown in the second column but the data saved is the ID from the first column

 

[QMarshal]

Make sure the bound column property of the combo reflects the ID column rather than the text column.

As far as I understand, bound column reflects where in the end do I want to write data to. It is set to ID column, which is right as I want to write to main table ID that corresponds to text, entered by user (link through 2nd table). It just doesn't know that it needs to convert back to ID from text. I could write a small query for that, but there should be less spaghetti way

 

[QMarshal]

Scheme of things

 

[MajP]

he problem is that now, when user tries to select text value, while working with form, Access tries to set numeric value of ID in my main table to text value of selected option. I need to find a way to reconvert text through 2nd table to ID, that I'd need to get into my main table.

The trick is to return and bind the ID column, but hide it by setting its width to 0

 

[MajP]

Example
Select ID, Text from someQuery
ColumnCount: 2
Bound Column:1
Column Widths: 0"; 1"

You will only see the text but store the ID.

 

[QMarshal]

Red is how it wrongly goes, trying to get text into ID field. Green is how it supposed to go.
My data field is main.ID as field of table my form is assigned to.

 

[QMarshal]

Example
Select ID, Text from someQuery
ColumnCount: 2
Bound Column:1
Column Widths: 0"; 1"

You will only see the text but store the ID.

Seen it in a turorial, but thought it's wacky AF. It it really how ppl do it? Wild.

 

[MajP]

Yes. It is exactly how to do it. AFAIK it is the only way without lots of code and an ubound control.

 

[bob fitz]

Can

Seen it in a turorial, but thought it's wacky AF. It it really how ppl do it? Wild.

Definately