I would suggest you not have a separate form just to do a search. This is
easily handled with an unbound combo box. I typically put mine in the form's
header, but it doesn't really matter. You will probably want to exclude it
from the Tab Stop.
There are two events of a combo box that are used for this purpose. The
After Update event is used to execute the search. The Not In List event is
used when the user enters a value that is not found in the search. This is
so you can either add a new record, open a form to add a new record, or
cancel the input.
The combo box has a Row Source property. It is much like the record source
property of a form. It presents the list of options to the user. In this
case, you will want to find a specific record in the form's record source.
This will usually be the Primary Key field. Often, the Primary Key field is
an Autonumber field or some other value that is probably meaningless to the
user. For this example, we will use a Client table. The primary key is an
autonumber, so the user will need to see the client's name to identify the
correct client record. We will use a query for the combo's row source:
SELECT Client_PK, Client_Name FROM tblClient ORDER BY Client_Name;
This will give us the primary key for the search and the name for the user
to select. It will be in alphabetical order by Client Name.
Now we need to set a few properties in the combo box:
Row Source Type Table/Query
Row Source Use the query above
Column Count 2
Bound Column 2
Column Widths 0";2" (the 0" hides the key value. The user will see only the
name)
Limit To List Yes
Auto Expand Yes
Now to actually find the selected client and make it the current record on
the form, use the combo's After Update event:
Private Sub cboClient_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[Client_PK= " & Me.cboClient.Column(0)
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With
End Sub
If the user enters a value that is not in the combo's row source and the
Limit To List property is set to yes, the Not In List event fires. Here we
ask the use whether
Private Sub Colors_NotInList(NewData As String, Response As Integer)
If MsgBox("Client is not in list. Add it?", vbYesNo) = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute("INSERT INTO tblClient ([Client_Name]) " & _
"VALUES (" & NewData & ");"), dbFailOnError
With Me.RecordsetClone
.FindFirst "[Client_PK] = " & Me.cboClient.Column(0)
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
Else
Response = acDataErrContinue
Me.cboClient.Undo
End If
End Sub
So, what happens in the After Update is that if the user selects no, the
combo is cleared and the user can try again. If the user answers yes, then a
new record is create with the client name already filled in, the Client_PK
field will be populated automatically because it is an autonumber field, and
the form will show the new record ready for the user to add additional data.
--
Dave Hargis, Microsoft Access MVP
Mcrawford said:
I have two forms. On form A I have a command button to open Form B, which is
a search form.
I want to be able to select a record on form B and open it on Form A. How
would I do this? Would I create a command button on form B to return record
to Form A? What code would I use?
I'm new to Access, this is my first project so detailed explainations are
appreciated.
