select row in combo box based on value in 2 columns

[supmktg]

I have a combo box (cboContact) with multiple columns on a form:
ContactID | Name | RegionID
15 | Alan | 1
15 | Alan | 2
16 | Bob | 1
16 | Bob | 2

ContactID is the bound column.

The row source is:

SELECT tblContacts.ContactID, tblContacts.Name, tblContactRegions.xRefRegionID
FROM tblContacts INNER JOIN tblContactRegions ON tblContacts.ContactID = tblContactRegions.xRefContactID

I'm trying to set Me.cboContact = ([ContactID]=15 and [xRefRegionID] =2) using the on open event of the form.

How can I accomplish this?

Thanks,
Sup

 

[arnelgp]

you do it on the form's load event:
public sub form_load()
dim i as integer
for i = 0 to me.cboContact.ListCount-1
if me.cboContact.column(0, i) = 15 and me.cboContact.Column(2, i) = 2 then
me.cboContact.Value = me.ItemData(i)
Exit for
End If
Next
End Sub

 

[supmktg]

Hi arnelgp,

Thanks for the quick reply.

"me.ItemData(i)" threw a data member not found error, so I changed it to " me.cboContact.ItemData(i)"

Unfortunately, this sets cboContact to the item that has the correct ContactID, but the RegionID 1 instead of RegionID 2?

I'm doing this on the on open event, but I also tried it on load with the same result.

Sup

 

[pbaldy]

I suspect you'll have issues like this without a unique key field. You'd want that as the bound column. In your case, both have the same value so Access isn't sure which one you want.

 

[Solo712]

I suspect you'll have issues like this without a unique key field. You'd want that as the bound column. In your case, both have the same value so Access isn't sure which one you want.

That appears to be correct. The way around it would be to retain the index (i) say in a global variable 'ssel' of the match in arne's search loop and then refer to the values as cboContact.column(0, ssel) for contactID and cboContact.column(2, ssel) for the region.

Best,
Jiri

 

[supmktg]

What I'm trying to do is set the combo to the correct ListIndex. The combo does have only one unique that contains those records. The code correctly identifies the ListIndex of that unique record. However, when it selects the record in the combo it selects the first record that contains the correct bound field and ignores the ListIndex.

I've attached an example that demonstrates the issue.

Any help would be appreciated!

Thanks,
Sup

 

[Solo712]

What I'm trying to do is set the combo to the correct ListIndex. The combo does have only one unique that contains those records. The code correctly identifies the ListIndex of that unique record. However, when it selects the record in the combo it selects the first record that contains the correct bound field and ignores the ListIndex.

I've attached an example that demonstrates the issue.

Any help would be appreciated!

Thanks,
Sup

It will work with a slight mod (hilited in red):

intRegion = Split(Me.OpenArgs, ":")(1)
For i = 0 To Me.cboContact.ListCount - 1
       If Me.cboContact.Column(0, i) = intContact And Me.cboContact.Column(2, i) = intRegion Then
        Exit For
       End If
       Next
       Me.cboContact.SetFocus
       Me.cboContact.ListIndex = i
MsgBox "You've selected:" & vbCrLf & "Contact = " & intContact & vbCrLf & "Region = " & intRegion & _
vbCrLf & "Which is ListIndex " & i & _
vbCrLf & vbCrLf & "The combo selected:" & vbCrLf & "Contact = " & Me.cboContact.Column(0[COLOR=red], i[/COLOR]) & vbCrLf & "Region = " & Me.cboContact.Column(2[COLOR=red], i[/COLOR])

Best,
Jiri

 

[supmktg]

Hi Jiri,

adding the code in red incorrectly changes the message box to indicate a correct selection, while in fact, the selection is not correct.

The issue is that the correct ListIndex for ContactID 1 (Alan) and Region 2 (South) is ListIndex 1.

The For loop correctly returns the ListIndex of 1 to the variable i, but then sets the combo to ListIndex 0, which is not i.

Me.cboContact.ListIndex = i

There is no reference to anything but the ListIndex as far as I can tell, so I'm baffled as to how it chooses the right contact at all.

 

[sneuberg]

I think you are going to have to use the xRefID of the tblContactRegions as the bound column for this combo box. That uniquely identifies what you want. The row source of the combo box would be

SELECT tblContactRegions.xRefID, tblContact.ContactName, tblRegion.RegionName
FROM (tblContactRegions INNER JOIN tblContact ON tblContactRegions.xRefContactID = tblContact.ContactID) INNER JOIN tblRegion ON tblContactRegions.xRefRegionID = tblRegion.RegionID;

I don't think setting the listindex does anything. I think you need to set the value of the combo box to show something as selected.

 

[Solo712]

Hi Jiri,

adding the code in red incorrectly changes the message box to indicate a correct selection, while in fact, the selection is not correct.

The issue is that the correct ListIndex for ContactID 1 (Alan) and Region 2 (South) is ListIndex 1.

The For loop correctly returns the ListIndex of 1 to the variable i, but then sets the combo to ListIndex 0, which is not i.

Me.cboContact.ListIndex = i

There is no reference to anything but the ListIndex as far as I can tell, so I'm baffled as to how it chooses the right contact at all.

I see now ! The ListIndex property goes by the bound column. So for all Alan choices the LI will be set to 0 and for Bob to 4, irrespective what region is attached. So, yes, the bound column must be a unique value for this scheme to work.

Best,
Jiri

 

[arnelgp]

here try this and study the rowsource of your form2.

 

[sneuberg]

If you xRefID of the tblContactRegions as the bound column as shown in Mr. arnelgp's post the form load code can be simplified to:

Private Sub Form_Load()

If CurrentProject.AllForms("frm1SelectCombo").IsLoaded Then
    Me.cboContact.Value = DLookup("[xRefID]", "[tblContactRegions]", "[xRefContactID] = " & Nz([Forms]![frm1SelectCombo]![Combo0]) & " AND [xRefRegionID] = " & Nz([Forms]![frm1SelectCombo]![Combo2]))
End If
    
End Sub

 

[supmktg]

Thank you so much to everyone for your help!

I now have a much deeper understanding of selecting an item using ListIndex, and I am now fully aware that choosing a combo box selection based on ListIndex will not work properly if the bound column is not a unique value.

Thank you, Thank you,
Sup