Select record based upon combo box

[Guest]

I have a Client form with a Combo Box that is bound to ClientID. The Combo
Box is related to a query that has ClientID and ClientName. I want to be able
to select a Client in the Combo Box and have it display the Client record.
However, when I try to select a Client it displays the message "control can't
be edited, it's bound to autonumber field "ClientID".

What am I doing wrong?

 

[Rick Brandt]

I have a Client form with a Combo Box that is bound to ClientID. The
Combo Box is related to a query that has ClientID and ClientName. I
want to be able to select a Client in the Combo Box and have it
display the Client record. However, when I try to select a Client it
displays the message "control can't be edited, it's bound to
autonumber field "ClientID".

What am I doing wrong?

You are trying to use a bound control to navigate your form. Your ComboBox can
either be bound (used to enter data) OR used to navigate using code in its
AfterUpdate event. You cannot use the same control to do both.

 

[Guest]

When I use the wizard to set up the combo box, it asks if I want Access to
store the value for later use. If I take that option and store the key in a
work field, can I use the work field to get the Client record?

 

[Rick Brandt]

When I use the wizard to set up the combo box, it asks if I want
Access to store the value for later use. If I take that option and
store the key in a work field, can I use the work field to get the
Client record?

If you want the ComboBox to take you to a specific record then choose the third
option in the wizard. It will create an unbound control that will do that.

 

[John Vinson]

I have a Client form with a Combo Box that is bound to ClientID. The Combo
Box is related to a query that has ClientID and ClientName. I want to be able
to select a Client in the Combo Box and have it display the Client record.
However, when I try to select a Client it displays the message "control can't
be edited, it's bound to autonumber field "ClientID".

What am I doing wrong?

Having it bound to the ClientID.

If you want a combo box to *FIND* a record, use an *unbound* combo -
nothing in its Control Source. The Combo Box Wizard will offer you a
choice of creating a combo box to find a record; if that doesn't work
for you it's easy enough to write VBA code in the (unbound!) combo's
AfterUpdate event to open the form's recordsetclone and find the
desired ID:

Private Sub cboClientID_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[ClientID] = " & Me.cboClientID
If rs.NoMatch Then
Msgbox "Client not found", vbOKOnly
Else
Me.Bookmark = rs.Bookmark
End If
End Sub

John W. Vinson[MVP]

 

[Guest]

Great advice. Thanks all. I am new to Access forms and sometimes I don't even
know how to ask the question. I spent serveal hours trying to figure this out

I have a Client form with a Combo Box that is bound to ClientID. The Combo
Box is related to a query that has ClientID and ClientName. I want to be able
to select a Client in the Combo Box and have it display the Client record.
However, when I try to select a Client it displays the message "control can't
be edited, it's bound to autonumber field "ClientID".

What am I doing wrong?

Having it bound to the ClientID.

If you want a combo box to *FIND* a record, use an *unbound* combo -
nothing in its Control Source. The Combo Box Wizard will offer you a
choice of creating a combo box to find a record; if that doesn't work
for you it's easy enough to write VBA code in the (unbound!) combo's
AfterUpdate event to open the form's recordsetclone and find the
desired ID:

Private Sub cboClientID_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[ClientID] = " & Me.cboClientID
If rs.NoMatch Then
Msgbox "Client not found", vbOKOnly
Else
Me.Bookmark = rs.Bookmark
End If
End Sub

John W. Vinson[MVP]