Picking one record out of several search results

  • Thread starter Thread starter Joseph Ellis
  • Start date Start date
J

Joseph Ellis

Hello all,

I'm just now learning a little VBA, and would like to spruce up my
church database form's "search engine". Currently I have a list box
that lists fields in which the user can search, ie "Last Name", "First
Name", etc. Then the user can type the text they are looking for in a
text box, and jump to the first match:

Private Sub tboSearchText_AfterUpdate()
Dim sf As String
'Match user friendly strings to actual field names
If lboSearchField = "Last Name" Then
sf = "lastName"
ElseIf lboSearchField = "First Name" Then
sf = "firstName"
Else
sf = "zip"
End If
DoCmd.GoToControl sf
DoCmd.FindRecord tboSearchText, acAnywhere
'Clear the Search box
Me.tboSearchText.Value = ""
End Sub


What I'd like to do is pop up a continuous form (say, frmResults)
containing all the search results (ie all records with a Last Name of
"Smith"), allow the user to select the desired record, then close that
popup form and have the first form display the selected record.

Does that make sense? I'm open to suggestions if this approach is
horribly inefficient or inelegant.

Thanks,
Joseph
 
Yes, it makes sense. You would open the form using a filter criteria. You
would need the following in the form:

Unique ID field for each person
Last Name
First Name
Middle Name/Initial
Name Prefixes (Mr, Mrs, etc) (This field just helps narrow down the correct
person. Mr/Mrs may be obvious by name, but sometimes Dr, Lt, SSgt, Rev will
help.)
Name Suffixes (Jr, Sr, etc) (This field helps identify that you have the
correct person, i.e. father/son)
Address (You may have more than one John Smith in the church)

If you open the form as a pop-up (window mode argument set to acDialog) then
when you go to close the form, don't. Remove the Close button (minimize and
restore also) and Control Box in the Form's properties. Have a Done and
Cancel button on the pop-up. When you click the Done button, hide the pop-up
(Me.Visible=False). When you click the Cancel button, close the form.
Opening the form with acDialog will pause the calling code until the form is
closed or hidden. In the next line of the calling code, check to see if the
form is open or closed. If it is closed the user chose cancel, exit the
routine. If it is open, get the value of the unique ID field's control and
do a FindFirst on your main form then close the pop-up. The reason for
closing the pop-up once you get the value is becuase if you leave it open
and the want to search again, the acDialog argument won't work properly if
the form is already open.
 
Thanks, Wayne

I look forward to giving this a try tonight. There is one step in
your instructions that I don't think I know how to do: How do I check
to see if the popup form is open or closed?

I think I'll be able to work the rest out. I hope. Maybe. <g>

Again, thanks for your great instructions.

Take care,
Joseph
 
If you have Access XP or newer, you can use the IsLoaded property of the
AllForms collection.

Application.CurrentProject.AllForms(strFormName).IsLoaded

If not, try this function.

Public Function IsOpen(strName As String, Optional varObjectType As Variant)
'Returns True if strName is open, False otherwise.
'Assume the caller wants to know about a form.
If IsMissing(varObjectType) Then varObjectType = acForm
IsOpen = (SysCmd(acSysCmdGetObjectState, varObjectType, strName) <> 0)
End Function
 
Back
Top