Search Facility in Forms (1 Viewer)

[macsta]

crap me being a idiot!!!

my apologies I posted the above thread in the wrong page ( Not to bright sometimes head is full of crap) I wanted to post it in the
search box thread
Mac

 

[Only1Abhi]

and that is where u have posted it my friend


anyway, what is your problem?
I think I maybe able to help u!

 

[Mile-O]

Mac, this should be it converted to '97 for you.

 

[Only1Abhi]

thanks again Mile for solving my problem!

Best Regards,
Abhi


PROBLEM: Solved
TASK: Post Abandoned
REQUEST: None

 

[Necron]

Hi,

My Prob.

I have been trying to place a filter/search facility into one of my forms based on my tblcustomerdetails. Basically i set in my table/query was
Customer ID, Surname, Name, Tel No, Work No Cell No ( i have a number of other fields within my tblcustomerdetails). I have my filter/search set to surname but once i type in a surname and click search it goes from showing me a list of customer details with those field state above, to showing me the other fields in that table only.

How do i get around this problem? All I want is to be able to type in a specifc surname and it show me that that persons details only showing those fields stated above...

The coding i have in place is the same as that of page of this thread....
______________________________________________
Private Sub cmdSearch_Click()

If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Filter Example"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM tblCustomerDetails WHERE [Surname] Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
.Requery
End With

lblResults.Caption = "Filter Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)


End Sub
_______________________________________

Any help would be great and appreciated.

Necron (Newbie - Soz!!!)

 

[KWL]

Mile...

your example is a great reference... thank you....

 

[vankir]

search 2 different fields

Hi Everybody

I am trying to search do a search on the whole table looking for any occurances of a word in all the fields.Most of the examples just concentrate on a specific field.

For example:

I want to search for a record thats name field is
piet and surname field is black. So I want to use an AND operator. So the result should bring up a record with piet black in it.

There will be a diferent block to enter the name and surname.

Here is my code so far which just searched the name field.

Private Sub cmdSearch_Click()

If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Backup"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM backupTags where [Your name] Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
.Requery
End With


lblResults.Caption = "Filter Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)


I would greatly appreciate any help or even an example.

Thanks in advance.

Vankir

 

[jpsabino]

Hi Mile-o-Phile,

Could You tell me what I have to do to extend the search to other fields? What kind of changes do I have to make to the code? I suppose that I´ve got to use boolean operators (AND, OR), but could You give me an example upon you own code:

.RowSource = "SELECT * FROM tblCustomers WHERE [Surname] Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
.Requery

Thank you very much
Best regards
João Sabino

 

[John Big Booty]

jpsabino Welcome to the forum.

Perhaps the sample posted here will give you some pointers.

 

[jpsabino]

I´m sorry, but the post that you´ve sent to me refers to a different search form code. Since Mile-O-Phile was the author of the search form that I would like to adapt to my needs, I hoped that himself could satisfy my question, which is very simple and straigthforward, I suppose: Mile-O-Phile says, and I quote, that "The search filter here only works on the Surname field but it can be adapted to search all relevant fields." I only would like to know how can the code be changed so that the search can be extended to other fields. I´m reporting to this code :

Option Compare Database
Private Sub cmdClose_Click()
DoCmd.Close acForm, "frmFilterExample", acSaveNo
End Sub
Private Sub cmdHelp_Click()
MsgBox "Filter Example" & vbCrLf & "by Mile-O-Phile, 2003." & vbCrLf & vbCrLf & _
"You can use the search criteria to filter with a wildcard i.e. Smith will return all people named Smith, Smithson, Sixsmith, etc." & vbCrLf & _
"To limit the search, you can check the exact match checkbox." & vbCrLf & _
"The search filter here only works on the Surname field but it can be adapted to search all relevant fields." & vbCrLf & vbCrLf & _
"Hope this helps, Mile-O-Phile."


End Sub
Private Sub cmdSearch_Click()
If IsNull(Me.txtFilter) Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Filter Example"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM AUTOR WHERE [nome_autor] Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
End With
lblResults.Caption = "Filter Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)

End Sub
Private Sub Form_Load()
lblResults.Caption = "Filter Results: " & IIf(lstResults.ListCount - 1 = -1, 0, lstResults.ListCount - 1)

End Sub
Private Sub lstResults_Click()
MsgBox "You can use the ID as stated in lstResults.Column(0) to open your form with the specific details that you wish.", vbInformation, "Filter Example"

End Sub

Thank You and thanks the forum
Best regards
João Sabino

 

[jpsabino]

I´ve just solved the question that I put to the forum! Thanks anyway!

Private Sub cmdSearch_Click()
If IsNull(Me.txtFilter) Then
MsgBox "Não introduziu termos para pesquisa", vbExclamation, "Atenção!"
Exit Sub
End If
With lstResults
.RowSource = "SELECT * FROM tblcostumers WHERE ([Surname]& [name]) Like '" & _
IIf(chkExactMatch = True, Me.txtFilter & "';", "*" & Me.txtFilter & "*';")
End With

Best regards
João Sabino