Search Facility in Forms

[Groundrush]

What am I doing wrong?

Hi Mile-O-Phile

Could you please tell me what I am doing wrong
I have based your example on one of my tables and have changed the table name to "Sub-Contractor Orders" and the field name to "Order Raised By"

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 Sub-Contractor Orders WHERE [Order Raised By] 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

The screen goes blank when I click on the search button

I know it will be something really stupid.

Thanks for your help

 

[Mile-O]

Re: What am I doing wrong?

.RowSource = "SELECT * FROM Sub-Contractor Orders WHERE [Order Raised By]

At a quick glance, put square brackets around Sub-Contractor Orders.

 

[Groundrush]

You should be a Doctor

found the problem just by a quick glance


thanks.

 

[Mile-O]

Yep, just grabbed the code and said "Cough please!"

 

[Groundrush]

Never mind the coughing, what about this Itch?


By the way do you know why the List box displays the results in a different order after pressing the search comand button?

Regards

 

[mingbin]

mile,

i would like to check with u abt your example. I was trying to adapt it such that in the form interface i can choose exactly which field i want to search in via a option list, then key in the filter criteria and search. I tried using the If...Then...Else thinggy, is that correct?

I typed the following:

If (College = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [College] Like ' " & _
.Requery
Else

If (Team = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Team] Like ' " & _
.Requery

End Sub

and it return me "Compile Error:Expected Function or Variable" for the requery part.

Sorry if i say any silly stuff here cos i'm an absolte rookie her...thanx.....

 

[Mile-O]

By the way do you know why the List box displays the results in a different order after pressing the search comand button?

Check the RowSource at the start (when the form loads) and then compare it to your SQL statement: are the fields in the same order? Are you selecting more fields?

 

[Mile-O]

Mingbin,

what are College and Team on your form? Are they checkboxes?

If they are options in an option group you will have to use their Option Value to determin which is selected.


Also, if you are going to say:

With List
.<something>

then you need to close this with

End With

 

[mingbin]

Yap...they are checkboxes and there are actually 11 of them bt i pasted the codes for 2 just to illustrate the gist of what i did.

My fields are in tha same order as the table and I have ended each part with the "End With", but i'm still getting the same error msg.

When u mentioned option values are they the numerals of each field which we assigned in the wizard? If they are, I have entered them as follows in "If" condition but it still gives me an error at the requery part...did i write it wrongly?

If (1 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Sno] Like ' " & _
.Requery
End With
Else

If (2 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Number] Like ' " & _
.Requery
End With
Else

 

[Mile-O]

Can you post a sample of what you have thus far?

 

[mingbin]

This is the whole thing i have right now.....

Private Sub Reset_Click()
Filter = vbNullString
List.RowSource = ""
End Sub


Private Sub Search_Click()
If Filter = vbNullString Then
MsgBox "You have not entered any Filter Criteria.", vbExclamation, "Invalid Search"
End If

If (1 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Sno] Like ' " & _
.Requery
End With
Else

If (2 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Number] Like ' " & _
.Requery
End With
Else

If (3 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [First Name] Like ' " & _
.Requery
End With
Else

If (4 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Last Name] Like ' " & _
.Requery
End With
Else

If (5 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Position] Like ' " & _
.Requery
End With
Else

If (6 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Height] Like ' " & _
.Requery
End With
Else

If (7 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Weight] Like ' " & _
.Requery
End With
Else

If (8 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Birthdate] Like ' " & _
.Requery
Else

If (9 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Experience] Like ' " & _
.Requery
End With
Else

If (10 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [College] Like ' " & _
.Requery
End With
Else

If (11 = True) Then
With List
.RowSource = "SELECT * FROM [Roster] WHERE [Team] Like ' " & _
.Requery
End With

End Sub

 

[Groundrush]

I am using a auto No as my criteria to show the record in the form
when I click on the 1st results box

I seem to get the first record in the table rather than the one that I clicked on.

any ideas?

 

[Mile-O]

mingbin, what is this?

If (1 = True) Then

If you are referring to the possible values of an option group then you use the syntax

If YourOptionGroup = 1 Then

Also you can reduce your code by using a Select Case Statement

i.e.

With List
   Select Case YourOptionGroup
      Case Is = 1
         .RowSource = "SELECT * FROM [Roster] WHERE [Sno] Like ' " & _ 
      Case Is = 2
         .RowSource = "SELECT * FROM [Roster] WHERE [Number] Like ' " & _
      Case Is = 3
         etc, etc.
      Case Else
         <action to take if any other value>
   End Select
   .Requery
End With

 

[mingbin]

Hi Mile, I tried out your method and have the following now:


Private Sub Reset_Click()
Filter = vbNullString
List.RowSource = ""
End Sub


Private Sub Search_Click()
If IsNull(Me.Filter) Then
MsgBox "You have not entered any Filter Criteria.", vbExclamation, "Title"
Exit Sub
End If

With List
Select Case Options
Case Is = 1
.RowSource = "SELECT * FROM [Roster] WHERE [Sno] Like '*" & Me.Filter & "';"
Case Is = 2
.RowSource = "SELECT * FROM [Roster] WHERE [Number] Like '*" & Me.Filter & "';"
Case Is = 3
.RowSource = "SELECT * FROM [Roster] WHERE [First Name] Like '*" & Me.Filter & "';"
Case Is = 4
.RowSource = "SELECT * FROM [Roster] WHERE [Last Name] Like '*" & Me.Filter & "';"
Case Is = 5
.RowSource = "SELECT * FROM [Roster] WHERE [Position] Like '*" & Me.Filter & "';"
Case Is = 6
.RowSource = "SELECT * FROM [Roster] WHERE [Height] Like '*" & Me.Filter & "';"
Case Is = 7
.RowSource = "SELECT * FROM [Roster] WHERE [Weight] Like '*" & Me.Filter & "';"
Case Is = 8
.RowSource = "SELECT * FROM [Roster] WHERE [Birthdate] Like '*" & Me.Filter & "';"
Case Is = 9
.RowSource = "SELECT * FROM [Roster] WHERE [Experience] Like '*" & Me.Filter & "';"
Case Is = 10
.RowSource = "SELECT * FROM [Roster] WHERE [College] Like '*" & Me.Filter & "';"
Case Is = 11
.RowSource = "SELECT * FROM [Roster] WHERE [Team] Like '*" & Me.Filter & "';"
End Select
.Requery
End With
End Sub


Private Sub List_AfterUpdate()
DoCmd.OpenTable "Roster", acNormal, acEdit
End Sub


Private Sub List_Click()
Sno = List.Column(0)
Number = List.Column(1)
First Name = List.Column(2)
Last Name = List.Column(3)
Position = List.Column(4)
Height = List.Column(5)
Weight = List.Column(6)
Birthdate = List.Column(7)
Experience = List.Column(8)
College = List.Column(9)
Team = List.Column(10)
End Sub


I'm not getting any errors when i try to run the search but in my List box where the results are supposed to be shown just keeps showing all the data so i dunno whether it's cos it's not searching at all or just that the results dun get displayed......any ideas...?

 

[Mile-O]

Can you post an example?

 

[mingbin]

Here it is.....it's in xp...

 

[Only1Abhi]

Hi ppl.
Just had a little problem on this.

I have a form called frmSearch with an option group with options:

CustomerID
Forename
Surname
Gender
HomeTelNo
WorkTelNo
MobileNo

I also have in the form:
A search box - to enter the search criteria
2 buttons - (Search button & the clear button)
ListBox - to display the search results
Label - To say how many results there are.
CheckBox - to allow exact matches.

When I select an option, then I can enter something in the SearchBox and this should show results in the listbox (called SearchResults).

I also have a check box called ChkExactMatch.
However, I am not getting exact match results. Here is the code I entered in the Search Button's On_Click Command.

what could be the problem?
Many thanks in advance for the help.
Regards,
Abhi



Private Sub SearchButton_Click()
If SearchBox = vbNullString Then
MsgBox "You have not entered any filter criteria.", vbExclamation, "Invalid Search"
Exit Sub
End If
With SearchResults
Select Case Options
Case Is = 1
.RowSource = "SELECT * FROM tblCustomer WHERE [CustomerID] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 2
.RowSource = "SELECT * FROM tblCustomer WHERE [Forename] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 3
.RowSource = "SELECT * FROM tblCustomer WHERE [Surname] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 4
.RowSource = "SELECT * FROM tblCustomer WHERE [Gender] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = -1, 0, SearchResults.ListCount - 1)

Case Is = 5
.RowSource = "SELECT * FROM tblCustomer WHERE [HomeTelNo] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = 0, 0, SearchResults.ListCount - 1)

Case Is = 6
.RowSource = "SELECT * FROM tblCustomer WHERE [WorkTelNo] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = 0, 0, SearchResults.ListCount - 1)

Case Is = 7
.RowSource = "SELECT * FROM tblCustomer WHERE [MobileNo] Like '" & _
IIf(ChkExactMatch = True, Me.SearchBox & "';", "*" & Me.SearchBox & "*';")
.Requery
ResultsNo.Caption = "Filter Results: " & IIf(SearchResults.ListCount - 1 = 0, 0, SearchResults.ListCount - 1)
End Select
End With
End Sub

 

[Only1Abhi]

can anyone please help?
I really need this!
Many thanks in advance.
Best regards
Abhi

 

[Only1Abhi]

hi.
here's my DB file in 97 version.

 

[macsta]

Only Got 97

I know its a lot to ask Mile-O-Phile as I have seen you are super busy but could you convert your example of the search database into 97 so that I can incoperate it into my Staff list database



thanks in advance

MAC

ps. looking at this forum for about 3 hours now and I think Mile-O-Phile you really know your sh*t