Search Help (code)

[Walshie1987]

Hi, i have a text box and when i enter a number (Reqnumber)into it and click search it takes me to that record, now though i am doing another database and want to advance alittle and have two text boxes, i want to enter a first name in one and a surname in the other, and if a record is found which matches both boxes then i want it to be displayed on the screen.

This is the code i have for just one text box:

Private Sub cmdSearch_Click()

'Check Search Box for Blank Value
DoCmd.RunMacro ("gotonew")
If Trim(Me![txtSearch] & " ") = "" Then
MsgBox "Please Enter Req Number!", vbOKOnly, "No Req Number"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

Dim Search As String
Search = txtSearch

DoCmd.ShowAllRecords
DoCmd.GoToControl ("ReqNumber")
DoCmd.FindRecord Me!txtSearch
DoCmd.GoToControl ("ReqNumber")

ReqNumber.SetFocus

'If Req Number found goes to record

If ReqNumber = Search Then
MsgBox "Req Found: " & txtSearch, , "Congratulations!"
ReqDate.SetFocus
txtSearch = ""

'If Req Number not found goes to input sheet

Else
DoCmd.RunMacro ("GoToNew")
UserSelection = MsgBox(" Req Not Found, Add Req?", vbYesNo)
Select Case UserSelection
Case 6
DoCmd.RunMacro ("GoToNew")
ReqNumber = Search
ReqDate.SetFocus
Case 7
DoCmd.RunMacro ("GoToNew")
txtSearch = Null
txtSearch.SetFocus
End Select
End If

End Sub

Can anyone Help?

Thanks

 

[Walshie1987]

I'll try to explain better, I have a form that contains information about people at work eg. department, phonenumber, spend limits etc. and to be able to find them in the database i enter there last name into a txt box (txtsearch) and i am taken to the record.

I now want to be accurate with my searches (incase i have 2 people with the same surname) so i want to have 2 boxes (one for first name, one for surname.

the code i currently have is:

Dim SearchLN As String

SearchLN = txtSearch

DoCmd.ShowAllRecords
DoCmd.GoToControl ("txtLName")
DoCmd.FindRecord Me!txtSearch
DoCmd.GoToControl ("txtLName")

If txtLName = SearchLN Then
MsgBox "User Found: " & txtFName & " " & txtSearch
txtPNo.SetFocus
Else
DoCmd.RunMacro ("gotonew")
userselection = MsgBox("User Not Found, add new User?", vbYesNo)
Select Case userselection
Case 6
txtLName = SearchLN
txtFName.SetFocus
Case 7
txtSearchFN = Null
End Select
End If
End Sub

If anyone can change this i would really appreciate it

Thanks

 

[Walshie1987]

i know the previous post has been deleted, but if could could upload the file, or at least the code that would be great

Thanks
Chris

 

[GKWilly]

OK, I'll try and explain how it all works.

1) I have a form (student details)that is bound to tblstudents and contains the ref, surename and dob of of the students( in my case).

2) I have 2nd popup form (frmextendedsearch) that i use for the search and contains 3 unbound fields (lookforsurname, lookforforename, lokoforDob) and 3 buttons (find students, clear and close)

Behind the search form is the following code:

Option Compare Database ' Use database order for string comparisons.
Option Explicit

Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If

End Sub

Private Sub Clear_Click()

' Clear controls

Dim MySQL As String
Dim Tmp As Variant

MySQL = "SELECT * FROM Customers WHERE False"

' Clear search text boxes.
Me![LookForSurname] = Null
Me![LookForForename] = Null
Me![LookForDOB] = Null


End Sub

Private Sub Close_Click()
On Error GoTo Err_Close_Click


DoCmd.close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Error$
Resume Exit_Close_Click

End Sub

Private Sub FindStudent_Click()
On Error GoTo findStudent_click_error
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of Find Students form.

Dim MySQL As String
Dim MyCriteria As String
Dim MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant

ArgCount = 0

MySQL = "SELECT * FROM tblStudents WHERE "
MyCriteria = ""

AddToWhere [LookForSurname], "[Surname]", MyCriteria, ArgCount
AddToWhere [LookForForename], "[Forename]", MyCriteria, ArgCount
AddToWhere [LookForDOB], "[DoB]", MyCriteria, ArgCount

If MyCriteria = "" Then
MyCriteria = "True"
End If

MyRecordSource = MySQL & MyCriteria

Forms![student details].RecordSource = MyRecordSource

If Forms![student details].RecordsetClone.RecordCount = 0 Then

MsgBox "No records match the criteria you entered.", 48, "No Records Found"

MyRecordSource = "tblStudents"

Forms![student details].RecordSource = MyRecordSource

Else
MsgBox "Records Found.", 48, "Records Found"
End If

findStudent_click_exit:

DoCmd.close acForm, "frmExtendedSearch"

Exit Sub
findStudent_click_error:
MsgBox Error$
Resume findStudent_click_exit
End Sub

Private Sub LookForSurname_AfterUpdate()

End Sub


Replace andy reference of forms/tables to yourown and it should work straight away.

I hope this works for you.

Regards,

Martin

Gone Drinking

 

[Walshie1987]

Thanks alot for that, I'll have a play around and see what i can do!

Cheers
Chris

 

[GKWilly]

I know i included all the code behind the form but all you need is the 'AddtoWhere' sub and the onclick event of the 'FindStudent' button. The 'AddToWhere' that is called in the 'FindStudent' assumes that your table fields are [Surname], [Forename] and [Dob]. You may need to replace these to suit your fieldnames. Let me know how you get on.

Gone Drinking