Combo box in form error

[rpbsr]

I am trying to add a combo box to a form based on tblMembers that will allow
a member to be selected from a dropdown list along with data in related
fields. A suggestion I got was to setup the combo box (cboFindMember) witih
the following properties:

RowSource: SELECT MemberID, LastName, FirstName FROM tblMembers ORDER BY
LastName, FirstName;

BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;2.5cm;2.5cm
ListWidth: 5cm
LimitToList: Yes
AutoExpand: Yes

In the combo box's AfterUpdate event procedure put the following code:

Dim rst As Object

Set rst = Me.Recordset.Clone

With rst
.FindFirst "MemberID = " & cboFindMember
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

I wasn't told what the control source should be, so I chose "LastName".
Problem: When I click on the control I get "The value you entered isn't
valid for this field. For example, you may have entered text in a numeric
field or a number that is larger than the FieldSize setting permits".

Also, it was recommended to consider a refinement to the code:
Me.cboFindMember = Me.MemberID
Where in the code would this statement be placed?

Thanks.

 

[Beetle]

The control source of the combo box should be MemberID, not LastName.

The other line of code should go in the forms Current event. In the case
where a user navigates to a different record by using one of the form
navigation buttons, instead of the combo box, it would keep the combo
box in synch with the form.

 

[Klatuu]

Point of Order, Mr. Baily.
A Search Combo should not be a bound control. The problem is, when you try
to search for a record in the recordset with a bound combo, you actually
change the value of the current record. And, if the bound field is the
table's primary key, you will get a key violation error.

In fact, it is not necessary nor really even desirable (IMHO) to use a
control for the record's primary key field. It just needs to be in the
form's recordset.

 

[Beetle]

<removes egg from face>

Duh! I knew that. I just wasn't thinking clearly apparently. Thanks for
pointing out my mistake.

 

[Klatuu]

No problem. But then, I neber make mistrakes