Search form help

[Afrosheen via AccessMonster.com]

I have a form called subFrmFind which is a sub form of frmrostersub1. There
is a last name on both forms and has no links attached. All you do is enter
the last name of the person you're looking for and the fields are populated
with the information on the person. Last name, first name, and what
department they work in. The user can not change any information. It's just
for display purposes and to make sure you have the correct person. if not,
then you can click on the Next button until you find the person your looking
for. This is the code:

Private Sub txtSearch_AfterUpdate()
Dim lNameRef, stwhere, strSearch, strWhere As String

10 If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
20 MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!
"
30 Me![txtSearch].SetFocus
40 Exit Sub
50 End If
'---------------------------------------------------------------

'Performs the search using value entered into txtSearch and evaluates
this against values in lName

60 DoCmd.GoToControl ("lname")
70 DoCmd.FindRecord txtSearch

80 lNameRef = LName.Text
90 txtSearch.SetFocus
100 strSearch = txtSearch.Text
110 If lNameRef = strSearch Then
'MsgBox "Match Found For: " & strSearch, , "Congratulations!"
LName.SetFocus
140 txtSearch = ""
150 Else
160 Call MsgBox("Match Not Found For: " & strSearch _
& vbCrLf & "" _
& vbCrLf & "Please Try Again.." _
, vbExclamation, Application.Name)

170 txtSearch = ""
180 txtSearch.SetFocus
190 End If

End Sub

What I want to do is after the person is found, I want the main form
[frmrostersub1] to display/lookup/search the information when I hide the form.


I've tried setting the focus on the the main form then
DoCmd.GoToControl ("lname")
DoCmd.FindRecord txtSearch

but that didn't do any thing.

Your help would be most appreciated. Thank you.

 

[Barry A&P]

from your search form can you programatically close the main form then reopen
it and pass some open args to get the record you want. maybe look at how
they do it on some sample databases like Northwind

Barry

 

[Afrosheen via AccessMonster.com]

Hi Barry, thanks for the reply. The computer I'm using doesn't have Northwind
on it.
This is the new code I've modified to try and come up with the solution. The
thing is that it doesn't find the record in the sub form.

Dim strCriteria As String
Dim rs As DAO.Recordset

strCriteria = "[lname] = '" & Me.txtSearch & "'"
Set rs = Me.Parent.RecordsetClone

rs.FindFirst strCriteria
If rs.NoMatch Then
MsgBox "No match found"
Else
Me.Parent.Bookmark = rs.Bookmark
End If

rs.Close
Set rs = Nothing

from your search form can you programatically close the main form then reopen
it and pass some open args to get the record you want. maybe look at how
they do it on some sample databases like Northwind

Barry

I have a form called subFrmFind which is a sub form of frmrostersub1. There
is a last name on both forms and has no links attached. All you do is enter

[quoted text clipped - 51 lines]

Your help would be most appreciated. Thank you.

 

[Barry A&P]

Sorry i am not exactly clear on what you are trying to do, if you are just
trying to find a way to navigate to a record with a last name, the search
form might not be needed. Use the wizard to help with a find record combo..

Make sure the wizard is active
add a combobox to your main form and select the option that says "Find a
record on my form based on the value of a combo box" select the fields you
want to use and it will create code something like this..

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

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

i like to add this to the forms OnCurrent event so the search combo displays
the correct name when the navigation buttons are used..

Private Sub Form_Current()
Me![Combo29] = me!VendorID
End Sub

hope this helps
Barry

Hi Barry, thanks for the reply. The computer I'm using doesn't have Northwind
on it.
This is the new code I've modified to try and come up with the solution. The
thing is that it doesn't find the record in the sub form.

Dim strCriteria As String
Dim rs As DAO.Recordset

strCriteria = "[lname] = '" & Me.txtSearch & "'"
Set rs = Me.Parent.RecordsetClone

rs.FindFirst strCriteria
If rs.NoMatch Then
MsgBox "No match found"
Else
Me.Parent.Bookmark = rs.Bookmark
End If

rs.Close
Set rs = Nothing

from your search form can you programatically close the main form then reopen
it and pass some open args to get the record you want. maybe look at how
they do it on some sample databases like Northwind

Barry

I have a form called subFrmFind which is a sub form of frmrostersub1. There
is a last name on both forms and has no links attached. All you do is enter

[quoted text clipped - 51 lines]

Your help would be most appreciated. Thank you.

--



.