Combo Box search

[Guest]

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

You are describing normal behaviour for a combo.
It is possible to make a bound combo work correctly, but it is much easier
to leave it unbound. It doesn't really matter that it does not stay in sync
with the current record because it is not bound and has no relation to it.
It is really only for searching; however, I do understand that some users can
be confused by this. There a a number of ways to handle it.

One way I have seen is to put the combo in the header section of the form
and make the default of the header not visible. Then have a Search command
button that exposes the combo and hide the header in the After Update and Not
in List events when the search is complete.

If you want the combo to stay in sync with the form's current record, you
can put this in the Current event of the form:

If Me.NewRecord Then
Me.cboMyCombo = Null
Else
Me.cboMyCombo = Me.txtFieldToMatch
End If

 

[Guest]

Klatuu,

Yes i understand that this is the usual behavoir but as the system is not
going to be used by me i need this to be as user friendly as possible without
any potential mistakes!

My current code reads:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] & "'"
If Not rs.EOF Then
End Sub


Private Sub Form_Current()
If Me.NewRecord Then
Me.cbo [Combo24] = Null
Else
Me.cbo [Combo24] = Me.txtFieldToMatch
End If
End Sub

Where are my errors?

Matt

 

[Guest]

Use the NoMatch property instead of EOF

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] & "'"
If Not rs.NoMatch Then

txtFieldToMatch is only an example, you need to be using the name of the
text box that is bound to the field that the combo searches on.

Me.cbo [Combo24] = Me.txtFieldToMatch

Klatuu,

Yes i understand that this is the usual behavoir but as the system is not
going to be used by me i need this to be as user friendly as possible without
any potential mistakes!

My current code reads:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] & "'"
If Not rs.EOF Then
End Sub


Private Sub Form_Current()
If Me.NewRecord Then
Me.cbo [Combo24] = Null
Else
Me.cbo [Combo24] = Me.txtFieldToMatch
End If
End Sub

Where are my errors?

Matt

You are describing normal behaviour for a combo.
It is possible to make a bound combo work correctly, but it is much easier
to leave it unbound. It doesn't really matter that it does not stay in sync
with the current record because it is not bound and has no relation to it.
It is really only for searching; however, I do understand that some users can
be confused by this. There a a number of ways to handle it.

One way I have seen is to put the combo in the header section of the form
and make the default of the header not visible. Then have a Search command
button that exposes the combo and hide the header in the After Update and Not
in List events when the search is complete.

If you want the combo to stay in sync with the form's current record, you
can put this in the Current event of the form:

If Me.NewRecord Then
Me.cboMyCombo = Null
Else
Me.cboMyCombo = Me.txtFieldToMatch
End If