Listbox counts one up when saving

[Nadine67]

Hello and thank you for any attention this post may receive

Windows 8.1


Access 2013

I have combo boxes on my form with text boxes that auto-populate based upon the selection in the combo box. On the form it works as I want it to - e.g. I select Apples (ID 1 in lookup tbl) and the associated text box populates correctly with seed (col 2 ID 1). However when I exit the form the record in the data table saves as Oranges (ID 2 I lookup tbl) and seed (col 2 ID 1).

I am stumped as to what is going on. Any suggestions?

Nadine

[Homegrownandy]

We need more information about your structure and relationships between the tables and the form.

[Nadine67]

Hello Andy

FORM - The RowSource of the cbxVarProd is 'SELECT [ProductType], [UnitType] FROM tblProductDecrease ORDER BY [ProductType];'
The Control Source of the associated auto populate txtbox is '=[cbxVarProd].[Column](1)'
There are no associated events which would index the combo box selection. The associated txt box captures the correct information. However the combo box changes to the next option in the list once the form is saved.

TABLE - has three fields - ProductID (primary key), ProductType, UnitType.
There are no queries nor relationships with this table.

I am so confused.

[Bob Fitz]

can you post a copy of the db

[John_G]

For your combo box, what have you set as the bound column property? (It should be 1)

[Missinglinq]

I suspect that the Combobox is bound to a Field in the underlying Table, and the Combobox, for this type of operation, should be unbound.

This doesn't mean that there shouldn't be a Bound Column, as per John-G, but that the Control Source Property of the Combobox should be empty.

Linq ;0)>

[Nadine67]

Hi John G and Missinglinq

The ComboBox has a Control Source is a field in the table, Row Source Type is Table/Query and the Bound Column is 0.

See attached db. The forms in question are 'frmCapture' and 'frmEdit'. The fields in the form are under the heading/tabpage Productivity and Variable Cost. The main data table is tblImprovementIdeas and the tables relevant to the Comboboxs are tblProductIncrease and tblProductDecrease.


My front-end is greater than the allowed limit of 500.0kb. Is there another way to attach the fe? I have attached the be only.

TRAIN_be.zip
Nadine

[orange]

You could add both the fe and be to the same zip.

[Missinglinq]

The ComboBox has a Control Source is a field in the table, Row Source Type is Table/Query

Then that is the cause of your problem! As I said, the Combobox, for this type of operation, has to be be Unbound. If you need to save the Combobox selection, you need to add a Textbox to the Form that is Bound to the Field in the Form's underlying Table/Query...the Field that currently populates the Control Source of the Combobox...but the Combobox, itself, has to be Unbound.

Linq ;0)>

[Nadine67]

So then the text field will require an AfterUpdate function to equal the value selected int he Combobox?

[Missinglinq]

Yes, you'd use the AfterUpdate event of the Combobox to assign the Value from it to the Textbox, which in turn would be Bound to the Field in the Form's RecordSource.

Linq ;0)>

[Nadine67]

Yes I tried that Orange, but the combine zip was greater than 500kb.

[orange]

Did you do a compact and repair on the front end first?

[Nadine67]

Yes I did and it went from 6.06Mb to 2.46Mb

[John_G]

Bound Column is 0.

That's a mistake (and an easy one to make). Setting the bound column to 0 gives you the listindex of the selected item (i.e. which ROW you selected), not a value in one of the columns. This is one time where the column reference is NOT zero-based (don't you just love Microsoft's 'consistency'?), so if you want the value from the first column, the bound column value must be 1.