I have a form with last name first name and Id number. I like to be able to do a search on any of the 3 fields above. I am not sure where to start to do a search. I am wondering if anyone has a code sample that I can use as guidance to create a search for the 3 fields above. Thank you for any suggestions.<br> <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SEARCHES 1
- Thread starter villica
- Start date
One of the easiest ways is to put a button on your form and use the wizard to create the functionality you need. To get the wizard to start, make sure that the wand button on your toolbar is pressed when you drag the button onto the form.<br><br>You can also use the FindRecord method. By setting the focus to the field you want to search right before calling this method, you will make that field the current field, which is what the method defaults to. Look at the help file for the method for more detail.<br><br>Good luck
-
1
- #3
villica,<br><br>Assuming your form is Bound, say to a table or query, you'd have, in addition to all the Bound textboxes, 3 additional Unbound textboxes, called txtFirst,txtLast,txtID.<br><br>In the Click event of the search button do this:<br>(assume table is called Table1)<br>dim sq as string<br>sq = "" 'init<br>If NOT isnull(Me!txtID) then<br> sq = "AND table1.ID = " & Me!txtID<br>End If<br><br>If NOT isnull(Me!txtID) then<br> sq = "AND table1.FirstName = """ & Me!txtFirst & """"<br>End If<br><br>If NOT isnull(Me!txtID) then<br> sq = "AND table1.Lastname = """ & Me!txtLast & """"<br>End If<br><br>If sq <> "" 'if any criteria<br> sq = "Where " & right$(sq,len(sq)-3) 'strip the first AND<br>End IF<br>sq = "SELECT * FROM Table1 " & sq 'if sq is blank, no problem, select all<br><br>Me.Recordsource = sq 'setting recordsource automatically requeries<br><br>You can mess with the equality operators and add LIKE, etc, also you'll probaly want to do some validation to make sure ID is a number (if it is indeed a number) etc...<br>--Jim<br>
- Thread starter
- #4
Hi Jim thank you for your help. I am wondering if is not too much trouble to help me figure out where the syntax error is.<br>Dim sq As String<br><br>sq = ""<br>If Not IsNull(Me!Idno) Then<br> sq = "and Employee.Idno = " & Me!Id<br>End If<br><br>If sq <> "" Then<br>sq = "Where" & Right$(sq, Len(sq) - 3)<br>End If<br><br>sq = "Select * Employee" & sq<br><br> Me!EmployeeSubform.Form.RecordSource = sq<br> <br> ' If no records match criteria, reset subform's RecordSource property,<br> ' display message, and move focus to Id text box.<br> 'If Me!EmployeeSubform.Form.RecordsetClone.RecordCount = 0 Then<br> ' Me!EmployeeSubform.Form.RecordSource = "SELECT * FROM Employee WHERE False;"<br> ' MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"<br> Me!Idno.SetFocus<br> Else<br> Me!EmployeeSubform!EmployeeName.SetFocus<br> End If<br> <br> <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>
villica,<br>You seem to be missing the FROM clause in the statement. Also, make sure you leave spaces between the concatentaded variables, ie:<br><br>sq = "Select * from employees"<br>sq = sq & "Where idno = " & me!idno '<--ERROR, no leading space (alterntatively a trailing space in the first line)<br>me.recordsource = sq<br><br>The above will fail because the result is:<br>Select * from employeesWhere idno = 5 '<--No space between table name and 'where'<br><br>Also, you had me!id instead of me!idno in the sq clause..<br>--Jim
- Thread starter
- #6
Hi Jim I changed the code as follow<br><br>Dim sq as string<br> sq = "" <br> If NOT isnull(Me!IDno) then<br> sq = "AND Employee.ID = " & Me!IDno<br> End If<br><br> If NOT isnull(Me!Last) then<br> sq = "AND Employee.LastName = """ & Me!Last & """"<br> End If<br><br> If NOT isnull(Me!First) then<br> sq = "AND Employee.Firstname = """ & Me!First & """"<br> End If<br><br><br> If sq <> "" <br> sq = "Where " & right$(sq,len(sq)-3) <br> End IF<br> sq = "SELECT * FROM Employee " & sq <br><br>MsgBox "test"<br> Me!EmployeeSubform.Form.RecordSource = sq<br> MsgBox "test"<br> If Me!EmployeeSubform.Form.RecordsetClone.RecordCount = 0 Then<br> Me!EmployeeSubform.Form.RecordSource = "SELECT * FROM Employee WHERE False;"<br> MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"<br> 'Me!Idno.SetFocus<br> End If<br> <br><br>End Sub<br><br><br>I am getting a runtime error 2001 "cancelled previous operation"<br>When I preesed the button debug it highlights this line and I can't see anything wrong with it. I think one of the most difficult things in access is creating sql statements. I need all the help I can get. <br><br> Me!EmployeeSubform.Form.RecordSource = sq <p>Villica<br><a href=mailto:villica67@hotmail.com>villica67@hotmail.com</a><br><a href= > </a><br>
villica,<br>Check the value of sq in the debug window (type ?sq while that line is highlighted in debug mode). Examine the statement for syntax. Copy the sql from the debug window and put it into a new query in sql view, and see if it runs. Assuming all table and field names are correct, what you have looks fine <i>however</i>... I made a mistake in my example...the statements should read<br>sq = sq & "AND ...etc<br>rather than<br>sq = "And...etc<br>But that is not the problem, the code should still work, you just wouldn't get all the criteria.<br>--Jim
- Thread starter
- #8
- Status