Combo code problem

[Jody]

Hi,

I have a combo box whose row source is determined via the
SQL Statement:Query Builder. The purpose of the combo is
to allow a search, and not store data in a field. When a
value is selected, the entire record of which the value is
a part, is shown in a form.

The Query taps into a field called Subsnumber (text data
type) in a table called tblSubscriber. Some of the
Subsnumber data is identical, but each is part of a
distinct record.

The problem is that when I select any of these identical
data values, the same record shows in the form, rather
than the correct record. Here is the code that likely
controls this (which was created by a Wizard):

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProspSubscrID] = " & Str(Nz(Me!
[Combo78], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Does anyone know how to correct the code, so that the
proper record is shown for each identical data value?

Cheers,
Jody
..

 

[HSalim]

Jody,
What you are saying is that subsnumber is not unique other columns in your
table contribute to make the row unique, correct?

Therefore findfirst subsnumber will return the first record with a matching
subsnumber as you have just experienced. What you need to do is to
findfirst where subsnumber = me.combo78
and other field(s) that contribute to making this record unique.

However, this is unneeded effort.


Do the following in your combobox.
1. Change the rowsource query of your combobox to include the fields you
need.
for example, select subsnumber, subsname, subsaddress, ... from
tblsubscriber.

2. You can set the combobox's properties to show only the columns you want,
by setting
thecolumnwidths property. In this example, if you only want to show
subsnumber and subsaddress, you could specify that the columnwidths property
as 1", 0", 2"
the 0" will hide subsname.
You can also set the overall width of the dropdown by setting the listwidths
property.

3. in the After_Update event of the combobox, you can grab the values of
each of the additional columns of the rowsource of the combobox - the
current record selected


for example:
Private Sub cboItemID_AfterUpdate()
Me.txtType = Me.cboItemID.Column(2) ' this is the third column of the row
source
Me.TxtLabel = Me.cboItemID.Column(3) ' this is the fourth column of the
rowsource

notice that it is a Zero-based index, meaning the first column is Column(0)

HS