Continuing combo box problem

[Tony Williams]

I have a sub form based on based on table tbllevy which has a control
txtmemnbr. There is also another control which is a combo based on a query
which inturn is based on a table tblindividual. The combo has 3 columns:
txtfullnamea, txtsurname, and txtmemnumber. I have the second 2 columns
hidden and the combo list is sorted on the second column txtsurname. The
Control source of the combo is tbllevy.txtfullname as I want to store the
value picked from the combo, the row source of the combo is a query with SQL =

SELECT qryLevycombo.[txtfullnamea], qryLevycombo.txtsurname,
qryLevycombo.txtmemnumber FROM qryLevycombo ORDER BY qryLevycombo.
[txtsurname];

The Bound Column is column 1
When the value is picked from the combo I want the vale of the control
txtmemnbr to equal the value of column 3, txtmemnumber so I have this in the
Afterupdate of the combo

Private Sub cmbfullname_AfterUpdate()
txtmemnbr = cmbfullname.Column(3)
End Sub

BUT it doesn't work! I can select a name and the value is saved in the
tbllevy but the value of txtmemnbr isn't automatically picked up from the
choice from the combo and appear on the form in the control txtmemnbr.

Can someone explain what I'm doing wrong?
Thanks
Tony

 

[Douglas J. Steele]

The Column collection starts numbering at 0. That means that the third
column is referred to as cmbfullname.Column(2)

 

[Tony Williams]

Thanks Douglas that did it!
Cheers
Tony

The Column collection starts numbering at 0. That means that the third
column is referred to as cmbfullname.Column(2)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a sub form based on based on table tbllevy which has a control
txtmemnbr. There is also another control which is a combo based on a query
which inturn is based on a table tblindividual. The combo has 3 columns:
txtfullnamea, txtsurname, and txtmemnumber. I have the second 2 columns
hidden and the combo list is sorted on the second column txtsurname. The
Control source of the combo is tbllevy.txtfullname as I want to store the
value picked from the combo, the row source of the combo is a query with
SQL =

SELECT qryLevycombo.[txtfullnamea], qryLevycombo.txtsurname,
qryLevycombo.txtmemnumber FROM qryLevycombo ORDER BY qryLevycombo.
[txtsurname];

The Bound Column is column 1
When the value is picked from the combo I want the vale of the control
txtmemnbr to equal the value of column 3, txtmemnumber so I have this in
the
Afterupdate of the combo

Private Sub cmbfullname_AfterUpdate()
txtmemnbr = cmbfullname.Column(3)
End Sub

BUT it doesn't work! I can select a name and the value is saved in the
tbllevy but the value of txtmemnbr isn't automatically picked up from the
choice from the combo and appear on the form in the control txtmemnbr.

Can someone explain what I'm doing wrong?
Thanks
Tony