FORM TO DISPLAY SEARCH RESULTS 4

[baybie]

Hi, I wish to create a search form with the following fields
Company Name
Customer Name
Order ID
CHPartner

I would ideally like the user to be able to enter criteria in one, some or all of the fields and then press a button and either view the results in a sub-form or in another form. Is this possible?

Many thanks

 

[dwalker]

You need to start by creating a query that produces all the fileds you've listed above. Then create a new form which uses this query as its record source.

In your form's header create one unbound text box for each field - call them [lookup1], [lookup2], [lookup3] and [lookup4] and label them Company Name, Customer Name, Order ID and CHPartner. Add a button and label it Search.

The code for the search button's On Click event looks someting like:

Me.FilterOn = True

Me.Filter = search1 & " Or " & search2 & " Or " & search3 & " Or " & search3

Then write the following 4 functions:

Function search1()
 If IsNull(lookup1) Or lookup1 = "" Then
  search1 = "[Company Name] = 'xxxx'"
 Else
  search1 = "[Company Name] Like '" & lookup1 & "*'"
 End If
End Function

Function search2()
 If IsNull(lookup2) Or lookup2 = "" Then
  search2 = "[Customer Name] = 'xxxx'"
 Else
  search2 = "[Customer Name] Like '" & lookup2 & "*'"
 End If
End Function

Function search3()
 If IsNull(lookup3) Or lookup3 = "" Then
  search3 = "[Order ID] = 'xxxx'"
 Else
  search3 = "[Order ID] Like '" & lookup3 & "*'"
 End If
End Function

Function search4()
 If IsNull(lookup4) Or lookup4 = "" Then
  search4 = "[CHPartner] = 'xxxx'"
 Else
  search4 = "[CHPartner] Like '" & lookup4 & "*'"
 End If
End Function

If Order ID is a numeric field then replace the xxxx in Search4 with a numeric value which is NOT likely to be a real Order ID - like 9999 or 0000....

Function search3()
 If IsNull(lookup3) Or lookup3 = "" Then
  search3 = "[Order ID] = 9999"
 Else
  search3 = "[Order ID] Like '" & lookup3 & "*'"
 End If
End Function

In the form's detail section add a bound text box for each field from your query. Your user then enters a value in any, some or all search boxes and presses the search button. I haven't tested the code above but I have a similar form which work fine.

Good luck.

 

[baybie]

THANKS dwalker, watch this space to see if i'm successful or not.

 

[herrld]

I tried this code also, and it will find the first record using the search criteria I entered, but after that I get a error message.

Runtime error 2001
You canceled the previous operation

Can you help? Linda

 

[herrld]

got rid of my error.
thanks Linda

 

[herrld]

I have another question for this thread.
Once I enter the criteria and click search, I will get the first record that meets the search criteria.
I would like to have a NEXT button on the form that will go to the next record with the same criteria.

I also would like to clear the criteria from the lookup text boxes as soon as the Search button is clicked. Kind of like a reset the text boxes to blank, even though the filter is still in place.

Thank You!! Linda

 

[dwalker]

Linda

To clear the search criteria add the final 4 lines of code shown below:

Me.FilterOn = True

Me.Filter = search1 & " Or " & search2 & " Or " & search3 & " Or " & search3

lookup1=""
lookup2=""
lookup3=""
lookup4=""

If you want to step through the results one record at a time then you could make your form a Single Form and navigate using the Navigation Buttons. Or, if you'd like to add your own button then add a button control and put the following code into the buttons's On Click event:

On Error GoTo error_trap

DoCmd.GoToRecord , , acNext     'takes you to the next record

error_trap:
If Err = 2105 Then Exit Sub 'this tests to see if you've reached the last record

To view the results as a list then format your form as a Continuous Form with a verticle scroll bar.

Regards

Damien

 

[baybie]

Anyone know of a query that allows you to serach for one or more of the four criteria above?, Preferably not a parameter query...
The problem i find is that a lot of the "Like" quereies e.g "Like [first few letters of CustomerName] & "*"
These never give accurate returns. If i was to search for customers whose names begin with J, it might only return 1 result even though there are about 10.

 

[TroubleGal]

halo there , im doin a surveyform which uses asp, i have 1 question which allow user to select 5 choice . How do i caputure all the 5 selection in my database using Microsoft Access ? Is it by using SELECT sql ?

 

[MarshaPav]

So, back to the Jan 17 thread.. What did you do to get rid of the Run-time error 2001?

 

[waldo7474]

Hi all!!

When I adapted this code to fit in my database I came up with the problem that it would display results that satisfied either of the criteria entered when I only want results that satisfy all criteria entered.

When I use "AND" instead of "OR" I get an empty set even when I only have one criteria that needs to be met and the others are all left blank.

Are there any thoughts as to what I am doing wrong, my code is pasted at the bottom:

Private Sub SearchBtn_Click()

     Me.Filter = search1 & " OR " & search2 & " OR " & search3 & " OR " & search4 & " OR " & search5
     
     Me.FilterOn = True
    
End Sub

Function search1() As String
    If IsNull(PartNumberLookup) Or PartNumberLookup = "" Then
        search1 = "[PartNumber] = 'xxxx'"
    Else
        search1 = "[PartNumber] Like '*" & PartNumberLookup & "*'"
    End If
End Function

Function search2() As String
    If IsNull(DescriptionLookUp) Or DescriptionLookUp = "" Then
        search2 = "[Description] = 'xxxx'"
    Else
        search2 = "[Description] Like '*" & DescriptionLookUp & "*'"
    End If
End Function

Function search3() As String
    If IsNull(FirstUsedOnLookup) Or FirstUsedOnLookup = "" Then
        search3 = "[FirstUsedOn] = 'xxxx'"
    Else
        search3 = "[FirstUsedOn] Like '*" & FirstUsedOnLookup & "*'"
    End If
End Function

Function search4() As String
    If IsNull(OldPartNumberLookup) Or OldPartNumberLookup = "" Then
        search4 = "[OldPartNumber] = 'xxxx'"
    Else
        search4 = "[OldPartNumber] Like '*" & OldPartNumberLookup & "*'"
    End If
End Function

Function search5() As String
    If IsNull(WrongPartNumberLookup) Or WrongPartNumberLookup = "" Then
        search5 = "[WrongPartNumber] = 'xxxx'"
    Else
        search5 = "[WrongPartNumber] Like '*" & WrongPartNumberLookup & "*'"
    End If
End Function

Thanks for any help in advance!!
Wally

 

[herrld]

MarshaPav...
My runtime error was a result of my typing! If I remember correctly, I was missing a quotation mark somewhere.

Linda

 

[MarshaPav]

Linda: Duh! Yes, that was it. Why didn't I see that? Thanks a bunch.
Marsha

 

[herrld]

I'm back again.
When I get search results returned, I can hit enter to bring up the next record that meets the search criteria. That's cool. The problem is when I reach the last record that satisfies the search criteria, Access brings me to NEW RECORD. I don't want that to happen. I would rather it just stop there and give a 'Last Record' message or something like that.

Can I get some assistance please?

Thank you! Linda

 

[waldo7474]

In your form properties, under the data tab, put the "Allow Additions" property to "No". This should solve your problem.

Also, you should be able to use navigational buttons(if you would like) to go from one record to the next that follow this criteria. This would also allow you to go back to a record that you have passed up.

Hope this helps!!
Wally

 

[herrld]

Hi Wally..
I do want to allow users to add new records, I just don't want Access to go there automatically when the user goes past the last record.

I wish I could get the users to use the navigation buttons, but you know how people are, they want to keep hitting that Enter key. Linda

 

[baybie]

I cannot seem to get this thing to work, have been trying for months with no success.
I keep getting "Invalid Outside procedure" when you click on the Search button.
At the beginning i started by creating a query that produces all the fileds listed above in the first post. I used the Like "*" query. The biggest problem with this is that i would like to have blank fields to type in my search criteria instead of the populated fields which that query returns.

 

[baybie]

You wonderful guys thanks a lot, really grateful. Managed to crack this a year after putting in the original post.

 

[SiberBob]

I would like to have the "search form" be the exact same form layout as my original form. Instead of putting my "search fields" in the header section and then my records in the detail section, I wonder if it would work if I duplicate all fields on my original form then unbind the copied fields and rename them from [fieldname] to [searchfieldname], and use vb to set visible=no for bound fields and visible=yes for unbound fields. Then after the search parameters are filled in use the onclick event for a "find" button to reverse the visible property for all fields and set the filter code described above?

Any thoughts on whether this will work or not? I'm gonna try it anyway... I'm mostly just thinking out loud...

 

[addy]

This is the code I use to allow users to search records. It uses two forms, one search form and one search result form (They are called frmSearchData and frmSearchResult in this example but could be anything).

Also, the table where the data is stored in this example is tblMainData

Firstly, you need this Private Sub:

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

Then, you have this code on the on click event of your search button:

Private Sub CommandSearch_Click()

'  Create a WHERE clause using search criteria entered by user and
'  set RecordSource property of frmSearcResult
    
    Dim MySQL As String, MyCriteria As String, MyOrder As String
    Dim ArgCount As Integer

'  Initialize argument count.
    ArgCount = 0
    
'  Initialize SELECT statement.
    MySQL = "SELECT * FROM tblMainData " & " WHERE "
    MyCriteria = ""

'  Order by the contents of cmbSort
    MyOrder = ""

'  Use values entered in text boxes in form header to create criteria for WHERE clause.
    AddToWhere [SearchField1], "[Field1]", MyCriteria, ArgCount
    AddToWhere [SearchField2], "[Field2]", MyCriteria, ArgCount
    AddToWhere [SearchField3], "[Field3]", MyCriteria, ArgCount
    AddToWhere [SearchField4], "[Field4]", MyCriteria, ArgCount
    
'  When no criterion are specifed we return no records and return to frmSearchData.
    If MyCriteria = "" Then
        MyCriteria = "False"
    End If

'  Create SELECT statement.
     MyRecordSource = MySQL & MyCriteria & MyOrder
     Debug.Print MyRecordSource

'  New query? Then set new recordsource
   If MyRecordSource <> gv_RecordSource Then ' New query
     DoCmd.OpenForm "frmSearchResult", , , , , A_NORMAL
     Forms![frmSearchResult].Form.RecordSource = MyRecordSource     ' Set RecordSource property of frmSearchResult
     gv_RecordSource = MyRecordSource
     If Forms![frmSearchResult].Form.RecordsetClone.RecordCount > 0 Then    ' Returned some records?
        Forms![frmSearchResult].Caption = "Current Record Selection"
        DoCmd.OpenForm "frmSearchData", , , , , A_HIDDEN          ' Hide frmSearchData
        Forms![frmSearchResult].Caption = ""
     Else    ' Didn't return any records
     Forms![frmSearchData].Caption = "No Records Found!"
      DoCmd.OpenForm "frmSearchResult", , , , , A_HIDDEN      ' Open the frmSearchResult form hidden
      DoCmd.OpenForm "frmSearchData", , , , , A_NORMAL          ' Hide frmSearchData
     End If
   Else         ' Same old query
      DoCmd.OpenForm "frmSearchResult", , , , , A_NORMAL      ' Open the frmSearchResult form
      Forms![frmSearchResult].Caption = "Current Selection"
      DoCmd.OpenForm "frmSearchData", , , , , A_HIDDEN          ' Hide frmSearchData
   End If



' Now exit the sub
  Exit Sub

RunQueryError:
  MsgBox "Error: " & Err.Description
  Exit Sub

End Sub