Help with Search Form

[MN]

Hi,
I want to create a search form with lastname and first name. When the user
type in Lastname and Firstname then click button Search it will looking in
table Customer.
(Table Customer have field CustomerID, LName, and Fname)
If found records then:
- Open a form let user select which one to be EDIT, when they click on that
record it will open a nother window and let user edit that record.
If found no record then:
- Open a new form let the user enter new data (with carry over lastname and
firstname from previous Search screen)
How can I do this ? thank you in advance.
MN

 

[Jeanette Cunningham]

Hi MN,
here are links to 2 sample search forms
http://allenbrowne.com/AppFindAsUType.html

http://allenbrowne.com/ser-62.html



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[MN]

Thank you for your reply Jeanette.
Allen's examples are different with my boss want! :-(
All she want is open the form to search and click on record to open all
record match then click on that record to open another form to edit it !!!
I am just a beginner of Access so ...?
Regards. MN

 

[Jeanette Cunningham]

That is exactly what allen's second example does.
The search boxes allow your boss to pick which records she wants to show on
the search form.
You can put code so that when you boss double clicks a record on the search
form, it opens that record to edit it.

If you want to make it simpler, you can reduce the number of search controls
at the top of the form. I agree it is a big ask for an access beginner,
there is a steep learning curve for access, 100 times steeper than for Word
or Excel.

Post back with more questions as you tackle this search form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[MN]

Again Thanks Jeanette,
I did download 2nd example of Ellen, tried to run it ...
Sorry, it was not look a like. Here is my code could you revise please:
Private Sub CmdSearch_Click()
Dim strSQL As String
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("clientdata", dbOpenDynaset, dbSeeChanges)
strSQL = "SELECT * FROM ClientData " _
& " WHERE (cln_lname=[Forms]![FrmClientSearch]![Lname])" _
& " AND (cln_Fname=[Forms]![FrmClientSearch]![Fname]) " _
& " into table TBLtemp ; "

'Debug.Print strSQL
Set Rs = CurrentDb.OpenRecordset("TBLtemp")
If Rs.RecordCount() > 0 Then
DoCmd.OpenForm "FrmClientdata", , , lname =
[Forms]![FrmClientSearch]![lname] 'and with other info from TBLtemp - how can
I do it ??
Else
MsgBox "New record"
End If

End Sub

 

[Jeanette Cunningham]

I assume the search button is on your search form, if that is correct, you
can simplify the code-->
I have added strWhere to build the where condition for opening
frmClientData.

Private Sub CmdSearch_Click()
Dim strSQL As String
Dim Rs As DAO.Recordset
Dim strWhere as String

strWhere = "lname= """ & Me.Lname & """ " _
& " AND Fname= """ & Me.Fname & """"


Set Rs = CurrentDb.OpenRecordset("clientdata", dbOpenDynaset, dbSeeChanges)
strSQL = "SELECT * FROM ClientData " _
& " WHERE cln_lname= """ & Me.Lname & """ " _
& " AND cln_Fname= """ & Me.Fname & """ " _
& " into table TBLtemp ; "

'Debug.Print strSQL
Set Rs = CurrentDb.OpenRecordset("TBLtemp")
If Rs.RecordCount() > 0 Then
DoCmd.OpenForm "FrmClientdata", , , strWhere
Else
MsgBox "New record"
End If

End Sub



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Again Thanks Jeanette,
I did download 2nd example of Ellen, tried to run it ...
Sorry, it was not look a like. Here is my code could you revise please:
Private Sub CmdSearch_Click()
Dim strSQL As String
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("clientdata", dbOpenDynaset,
dbSeeChanges)
strSQL = "SELECT * FROM ClientData " _
& " WHERE (cln_lname=[Forms]![FrmClientSearch]![Lname])" _
& " AND (cln_Fname=[Forms]![FrmClientSearch]![Fname]) " _
& " into table TBLtemp ; "

'Debug.Print strSQL
Set Rs = CurrentDb.OpenRecordset("TBLtemp")
If Rs.RecordCount() > 0 Then
DoCmd.OpenForm "FrmClientdata", , , lname =
[Forms]![FrmClientSearch]![lname] 'and with other info from TBLtemp - how
can
I do it ??
Else
MsgBox "New record"
End If

End Sub

That is exactly what allen's second example does.
The search boxes allow your boss to pick which records she wants to show
on
the search form.
You can put code so that when you boss double clicks a record on the
search
form, it opens that record to edit it.

If you want to make it simpler, you can reduce the number of search
controls
at the top of the form. I agree it is a big ask for an access beginner,
there is a steep learning curve for access, 100 times steeper than for
Word
or Excel.

Post back with more questions as you tackle this search form.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[MN]

Thank you for your comment.
Yes, this is a code for Serach buttton. But I always got a message "New
Record" , although in a table have same lastname and firstname but it didn't
find out?
Something wrong in my code?
Regards.
MN

 

[Jeanette Cunningham]

I am trying to understand your form setup.
What is the record source for your search form?
Is it based on a query?
Is the search button on the search form?
I'm not sure why you are using a recordset to search on the search form.

What is the record source for frm client data?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia