Finding A Record Using Multiple Criteria

[Guest_imported]

I have a table that has a multiple-field index that uniquely identifies each record.

I have a form that has a combo box which is supposed to allow the user to select an item. Currently, the combo box has two columns which have enough information to uniquely select a record from the table.

I would like to be able to have it so that when an item is selected, the rest of the fields in the form will update according to the selected record.

I don't think it's possible for a macro to have two criteria in a FindRecord action since it can only access the bound column in a combo box, so I have been trying to do it with a module, since in a module I can use combobox.column(0) and combobox.column(1) to get the two values necessary to find the record.

So, is there a way to do a DoCmd.FindRecord with two criteria? If not, do you have any suggestions? I think that autonumbering would also do the job, but I would prefer not to use autonumbering.

Thanks for any help.

Vincent Cheung
Vincent.Cheung@mts.mb.ca

 

[Cagliostro]

Why to find record when you can apply a filter and release it if you don't need it more? John Fill

ivfmd@mail.md

 

[Guest_imported]

Because then if you go to select another item from the combo box, then there will only be one element in the combo box because of the filter.

Vincent Cheung
Vincent.Cheung@mts.mb.ca

 

[Cagliostro]

1. You can put in the form a buton to release filter.
2. If you are afraid of filters, use subforms. John Fill

ivfmd@mail.md

 

[Aivars]

Hi!
It's easy.

In this example cboComboBox.RowSource = "Select Table1.Field1, Table1.Field2, Table1.Field3 From Table1;"


Private Sub cboComboBox_AfterUpdate()
Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "Field1=" & Me.cboComboBox " & _
" And Field2=" & Me.cboComboBox.Column(1) & _
" And Field3=" & Me.cboComboBox.Column(2)
if not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing

End Sub

Good luck!
Aivars