ComboBox-only allows for first record in series

[Der Musensohn]

I am working with a personal finance tracking database. I have a form for
entering transactions that is based on a query using multiple tables. I want
to create a combobox to look up past transactions by Payee. I created a
combo box using the following: Payee.PayeeTable, Entry
Date.TransactionsTable, ID.TransactionsTable. When I try to go to a record
for a Payee with multiple dates the database only returns the first
transaction in the list (each column is sorted in ascending order). I've had
this problem with Access 2003 since day 1 and I found a way around it by
pasting code from an older combo box that worked and then changing the box #
and the field but that does not work with a box that I'm trying to create
now. Here is the "old" code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![Combo7])
Me.Bookmark = rs.Bookmark
End Sub

As I said, I tried to paste this into a new combo box but I would either
received data mismatch errors or else it would only return the first record
in the query. I tried changing the bound column to ID (as in column 3) but
it wouldn't return anything. I tried the ID column as column 1 but obviously
that only allows me to search via ID number and not type in text for the
payee.

If anyone has any suggestions I would really appreciate it.

Thanks.

 

[Jeanette Cunningham]

Hi Der Musensohn,
Does your form show all the records before you make a selection from the
combo?

If PayeeID is a number data type, you can use code like this.
We assume that PayeeID is the bound column of your combo and that the combo
is not bound (doesn't have a control source).

Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Me![Combo7])
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

set rs = Nothing
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia