If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with Search Form
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 |
#2
|
|||
|
|||
Help with Search Form
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" wrote in message ... 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 |
#3
|
|||
|
|||
Help with Search Form
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" wrote: 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" wrote in message ... 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 |
#4
|
|||
|
|||
Help with Search Form
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" wrote in message ... 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" wrote: 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" wrote in message ... 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 |
#5
|
|||
|
|||
Help with Search Form
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" wrote: 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 |
#6
|
|||
|
|||
Help with Search Form
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 "MN" wrote in message ... 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" wrote: 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 |
#7
|
|||
|
|||
Help with Search Form
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" wrote: 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 |
#8
|
|||
|
|||
Help with Search Form
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 "MN" wrote in message ... 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|