Combo box as look up and field

[Johnny Bright]

Hi there,

I have a combo box wih the following code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[HoleNumber] = '" & Me![cboFindHole] & "'"
Me.Bookmark = rs.Bookmark

Set rs = Nothing
End Sub

This works great and takes me right to the record I want to see. Currently
the combo is unbound. What I want to do in bind it to my holenumber field so
it serves a dual purpose. The problem comes up when I add a new record with
the combo box taking holenumber as its control source. it tells me i am
creating duplicate records. Is it possble to have a combo box serve this
dual purpose?

Thanks!

 

[fredg]

Hi there,

I have a combo box wih the following code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[HoleNumber] = '" & Me![cboFindHole] & "'"
Me.Bookmark = rs.Bookmark

Set rs = Nothing
End Sub

This works great and takes me right to the record I want to see. Currently
the combo is unbound. What I want to do in bind it to my holenumber field so
it serves a dual purpose. The problem comes up when I add a new record with
the combo box taking holenumber as its control source. it tells me i am
creating duplicate records. Is it possble to have a combo box serve this
dual purpose?

Thanks!

Use 2 combo boxes.
One, unbound, to look up the wanted record. This is best placed in the
Form's Header section.
The other, bound to the table field, to store/display the selected
value.
This is best placed in the Form's Detail section.

 

[Klatuu]

It will not work for dual purposes like that. You are getting the error
because when it is a bound control, you are changing the value of the current
record, then you are trying to create a new record with the same value in the
combo box as the record you just modifed.

Then when you create a new record you have to populate the text box with the
value for the new record.