Accessing columns in ComboBox

[roberto21]

Access 2016, all tables linked to actual tables stored under sqlexpress.


I have a form with a combo box field, say Cbox01, populated with surnames taken from a table listing all members of the group, along with other info, among those CodeNo, unique for each member.
Of course, surnames may not be unique, but CodeNo is.
Now, the user writes the surname to be handled, and is presented, if surname not unique, with a list of all people with that surname. The user selects one and other fields of the form are filled using code in sub Cbox01_AfterUpdate(), e.g.

me.name= me.Cbox01.column(1)   'where column(1) contains the name of the selected person
me.Code = me.Cbox01.column(2)   'where column(2) contains the CodeNo of the selected person

and up to this point, everything works.
Now, if I try to use me.Cbox01.column(2) in the form code to continue processing, I get the CodeNo for the FIRST person in the list, not the one I selected. Is this normal?

Just to be concrete:
Assume I enter Smith and the combobox presents me with the following

Smith Anne 123
Smith Becky 444


I select the second, Becky, the code in afterupdate is executed and me.name = me.Cbox01.column(1) is Becky, and me.Code = me.Cbox01.column(2) is 444: OK.


Now in the form code I need again those value, but now me.Cbox01.column(1) is Anne, and me.Cbox01.column(2) is 123. I know that I can use the correct values in me.name and me.code, but is this how it is supposed to work?

[Welshgasman]

I do not believe so?
Are you requerying the combo at all?
I would certainly use Me.FirstName (name is a reserved word) and Me.Code as less to type anyway?

[roberto21]

Thanks. I do not believe this should happen either, but it happens. Don't worry about the variable names, in my code they are actually in italian, it was just an example (combo box is called DaCognome, name is Nome, CodeNo is Carta and so on).
No, I am not requerying. As i said, the combobox data includes three fields of the Members table (surname, Name and codenumber) sorted by surname. After selecting the correct member, the fields in the form Nome and Carta get the values combobox.column(1) and.column(2) via the AfterUpdate sub, and they are ok; but using column(1) and column(2) in the form code, they are wrong (checked with the debugger). I discovered the error by chance, when I hit a non unique surname and selected the third in the list. Of corse now I will use the form values (me.Nome and me.Carta), but this is a strange behavior anyway. Thanks for your attention.

[June7]

Is this a bound combobox?

Is this duplicating Nome and Carta into another table? Why? Save the ID and retrieve related data in query for report output.

If you want to provide db for analysis, follow instructions at bottom of my post. Since data is in SQLServer, will have to convert to local tables.

[CJ_London]

you would normally bind the control to the column with the unique ID (presumably CodeNo) and you need to use this value for any related code.

[roberto21]

I am not sure I understand completely. Please note, I am really a novice, learning "on the job".
The ComboBox is unbound. The form is used to record a payment from the selected person into another table. The record to be written will contain the amount paid and the reason of the payment, and has a field in which are listed surname, name and codeid of the person. This field is filled as follows

Nnote = Forms!FrmRicevuteNuova2.Note & " - " _
            & Me.DaCognome & " " & Me.Nome & " (" & Me.Carta & ")"

in the form code. So there is no duplication of Nome and Carta in another table, since they are only used for Field Nnote as added information. In any case, if I use (as I was doing before) me.DaCognome.column(1) instead of me.Nome and me.DaCognome.column(2) instead of me.carta, I get the wrong values (corresponding to the FIRST person with that non-unique surname).

I confess I don't get how I should bind the control to the "unique" column. At the moment, the combobox Data property is (Numero Tessera is the unique codeid)

SELECT LibroSoci.Cognome, LibroSoci.[Nome ], LibroSoci.[Numero Tessera]
ORDER BY LibroSoci.Cognome, LibroSoci.[Nome ]

Thanks for your patience.

[CJ_London]

I'm talking about the bound column, not binding to a controlsource



bound columns start from 1 rather than columns which start from 0 (which admittedly is a bit confusing) so if bound column=1, it is referring to column(0). Your unique ID is in column(2) so bound column should =3

[June7]

Usually a combobox RowSource would have the primary key field as first column with 0" width so it doesn't show. Example for a combobox named cbxCust to list customers for an Orders form:

RowSource: SELECT ID, CustomerName, CustomerPhone FROM Customers;
ControlSource: Customer
BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0";1";1"

This will cause the ID primary key to be not visible while CustomerName and CustomerPhone will show in the dropdown. User can type customer name in box to select an item or click the dropdown. CustomerName will show in box but ID will save to Orders table.

To reference CustomerName column: cbxCust.Column(1)

If you want to provide db for analysis, follow instructions at bottom of my post.

[roberto21]

Thanks. I did try it immediately, setting bound column to 3. It seems to work: the only thing is that now to get the surname (cognome) I have to code

Nnote = Forms!FrmRicevuteNuova2.Note & " - " _
            & Me.DaCognome.column(0) & " " & Me.Nome & " (" & Me.Carta & ")"

since me.Dacognome now gives the codeid (bound column). Iwill continue trying and testing. Thanks a lot.

Just saw the post from VIP. I had already the column with codeid (third column) set at 0, the combobox only shows surname and name (column 0 and 1). Setting the bound column to 3 seems to do the trick, with the change in code reported above. Thanks again