Acc 2010 need to loop through combobox items & find data in second column 1

[DougP]

Any ideas how to refer to it? So I have 2 columns and 1st column bound

I have some text I want to find in the second column of the combo box
Vendor ID
ABC URDFG
XYZ FUDGD
RTF SDFFS

so I have "FUDGD" in my search string and I want to loop thru combo box and set it on "XYZ"

    ' get vendor name from first column in TimesheetF1
    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLCode As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset

    SQLString = "SELECT TimesheetF1.F1 From TimesheetF1 GROUP BY TimesheetF1.F1;"

    Rs1.Open SQLString, Conn2, adOpenStatic, adLockOptimistic
    For a = 1 To Me.cboChoosePTNumber.ListCount
        if Me.cboChoosePTNumber.ItemData(a).column(2)   <>>> how do I refer to the secondcolumn in this loop
        .Column(1). = Rs1!F1
    Next

DougP

 

[SkipVought]

forum705

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

columns are 0 indexed. The first is 0 the second is 1

 

[DougP]

OK but that's not the issue. I can't get the syntax.

    For a = 1 To Me.cboChoosePTNumber.ListCount
        if Me.cboChoosePTNumber.ItemData(a).column(1)   = Rs1!F1
    Next

Me.cboChoosePTNumber.ItemData(a).column(1)
does not work
Somehow I need to refer to the index of the item and the second column.
even though the IDE verifies it, when I run it, I get object required and it's not on the recordset the F1 has the value.
I don't want to do a dlookup to find F1 but I guess I have to instead of looping through the items.

DougP

 

[PHV]

For a = 0 To Me!cboChoosePTNumber.ListCount - 1
If Me!cboChoosePTNumber.Column(1, a) = Rs1!F1 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

PHV thanks; I have no idea how you know all stuff this? I have been doing VBA since early 1990's and still don't know everything. Office 4.3 if you can remember that far back.

DougP

 

[PHV]

I have no idea how you know all stuff this
I simply use the F2 and F1 keys when in VBE ...