Combo box variables

[Guest]

I have a combo box that displays two columns when it's dropped down. The
box is bound to column 1 and when the box is updated the form moves to a
record where some field on the form matches the box's value.

When the AfterUpdate event occurs, is there some way to capture the value in
column 2 as well? In other words, I want the form to go to a record where
one field matches column 1 and another field matches column 2.

Thanks much for any help with this.

David

 

[Allen Browne]

You can use two fields in the FindFirst argument, like this:

Private Sub NameOfYourComboHere_AfterUpdate()
Dim strWhere As String
Dim rs As DAO.Recordset

With Me.[NameOfYourComboHere]
If Not IsNull(.Value) Then
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

strWhere = "([SomeField] = """ & .Column(0) & _
""") AND ([AnotherField] = """ & .Column(1) & """)"
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "No match"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

.Value = Null
End With
End Sub

Notes:
1. The Column property is zero-based.

2. Drop the extra quotes in strWhere if the fields are Number (not Text.)

 

[Guest]

Thanks Allen.

I didn't know that one could reference any of the columns other than the one
to which the combo box is bound. I'll try this out.

Thanks again.

David
----------

You can use two fields in the FindFirst argument, like this:

Private Sub NameOfYourComboHere_AfterUpdate()
Dim strWhere As String
Dim rs As DAO.Recordset

With Me.[NameOfYourComboHere]
If Not IsNull(.Value) Then
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

strWhere = "([SomeField] = """ & .Column(0) & _
""") AND ([AnotherField] = """ & .Column(1) & """)"
Set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "No match"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

.Value = Null
End With
End Sub

Notes:
1. The Column property is zero-based.

2. Drop the extra quotes in strWhere if the fields are Number (not Text.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a combo box that displays two columns when it's dropped down. The
box is bound to column 1 and when the box is updated the form moves to a
record where some field on the form matches the box's value.

When the AfterUpdate event occurs, is there some way to capture the

value

in
column 2 as well? In other words, I want the form to go to a record where
one field matches column 1 and another field matches column 2.