Search using combo box

[BrookieOU]

I have an unbound combo box on a form that I use to select an employee and
then it will populate the rest of the fields on the form (contact
information). I would like the combo box to reset to blank after I select
the employee and the information shows up. I've seen it done, even seen the
code for it (though it was tied to another form), but can't seem to get it to
work. Currently I am using the wizard that comes with the combo box control
to get it done, but it leaves the name I chose in the box. Does anyone know
how I can fix this?

 

[Dirk Goldgar]

I have an unbound combo box on a form that I use to select an employee and
then it will populate the rest of the fields on the form (contact
information). I would like the combo box to reset to blank after I select
the employee and the information shows up. I've seen it done, even seen
the
code for it (though it was tied to another form), but can't seem to get it
to
work. Currently I am using the wizard that comes with the combo box
control
to get it done, but it leaves the name I chose in the box. Does anyone
know
how I can fix this?

If the wizard built the combo box for you, it probably has code that looks
something like this:

'----- start of wizard-generated code -----
Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
'----- end of wizard-generated code -----

The job can be done better than that, but we won't rewrite it now.

To clear the combo box after the "find" operation, just add a line to the
procedure, setting the combo box to Null. See the following example:

'----- start of revised code -----
Private Sub Combo1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo1], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me![Combo1] = Null

End Sub
'----- end of revised code -----

 

[MikeJohnB]

The code Me.ComboName = "" will set the combo value to "" on completion of
the selection. (See line <<<< below)

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[employess_name] = '" & Me![Combo39] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


Me.Combo39 = "" '<<<<<< Clear the combo after selection


End Sub

regards

 

[BrookieOU]

Dirk,

The code is already like the 2nd one you show (where you say to enter the
line with "Null" in it). That is how the wizard wrote the code.

Mike,

I also tried yours and it doesn't work. It leaves the name of the last
person I selected in the box.

What I'm looking to do is after I select a name and the field populates and
I move the cursor to the first field, I want the combo box to go back to
blank. As it stand, it leaves the last name I selected in the box, even if I
use the bottom navigation arrows to go to the next record.

Thanks for the help so far!

The code Me.ComboName = "" will set the combo value to "" on completion of
the selection. (See line <<<< below)

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[employess_name] = '" & Me![Combo39] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


Me.Combo39 = "" '<<<<<< Clear the combo after selection


End Sub

regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

I have an unbound combo box on a form that I use to select an employee and
then it will populate the rest of the fields on the form (contact
information). I would like the combo box to reset to blank after I select
the employee and the information shows up. I've seen it done, even seen the
code for it (though it was tied to another form), but can't seem to get it to
work. Currently I am using the wizard that comes with the combo box control
to get it done, but it leaves the name I chose in the box. Does anyone know
how I can fix this?

 

[Dirk Goldgar]

Dirk,

The code is already like the 2nd one you show (where you say to enter the
line with "Null" in it). That is how the wizard wrote the code.

Are you saying the combo box's AfterUpdate event procedure *has* the line I
suggested, setting the combo box to Null, and it *still* doesn't clear? If
that is so, then something else is going on, because that code would *have*
to clear the combo box. Please post (by copy/pasting) all the code that
involves the combo box in any way. Post whole procedures, not just
snippets.

 

[BrookieOU]

OK, I figured out how to convert a macro to visual basic. So I converted the
macro that the wizard created and here is the code I got:


Function search()
On Error GoTo search_Err

DoCmd.SearchForRecord , "", acFirst, "[Contact_Name] = " & "'" &
Screen.ActiveControl & "'"


search_Exit:
Exit Function

search_Err:
MsgBox Error$
Resume search_Exit

End Function

I know that is nothing like what you wrote.

 

[BrookieOU]

OK, the wizard did it as an embedded Macro and I can't get to the code, just
the macros screen. When I go to visual basic for that form it does not have
the code for the macro in it. I don't know how I found it last time. Sorry
for the difficulty.

 

[Dirk Goldgar]

OK, I figured out how to convert a macro to visual basic. So I converted
the
macro that the wizard created and here is the code I got:


Function search()
On Error GoTo search_Err

DoCmd.SearchForRecord , "", acFirst, "[Contact_Name] = " & "'" &
Screen.ActiveControl & "'"


search_Exit:
Exit Function

search_Err:
MsgBox Error$
Resume search_Exit

End Function

If you're using an embedded macro, then things are different. You can
either modify the embedded macro to add an extra action that clears the
combo box, or you can replace the embedded macro with VBA code that does
what I originally posted.

Although I habitually use VBA code instead of macros, let's keep things
simple for you and take the approach of modifying the embedded macro. Open
the form in design view, select the combo box and open its property sheet,
go to the Event tab and click on the AfterUpdate line (where, I presume, it
says "[Embedded Macro]".

When you click on that line, a "build" button (caption "...") should appear
at the end of the line. Click on that button to display the macro for
editing.

The macro will have a single action, "SearchForRecord". Click on the next,
blank, line under that and enter a new action: "SetValue" (without the
surrounding quotes). In the arguments for that action displayed for entry
down below in the Action Arguments pane, enter the name of the combo box on
the Item line, and "Null" (without the surrounding quotes) on the Expression
line.

Now close the macro window, and allow it to update the event property.

Save the form, open it in form view, and try the combo box now to see if it
behaves as you would like.