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 sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search form with multuple fields

Status
Not open for further replies.

GelC

Technical User
Oct 3, 2006
93
US
Hi all,
I'm trying to create a search form to search either one of the following fields

text fields (allow people to enter as many as posiible)
SongNumber
Title
Author
Keywords

drop down button which pull up data from tables
Area
Type
Year

I wish to have the above search fields in FormHeader section, when user click search button the results will display in Detail section.

Many thanks for all kind soul

Bee



 
So I am assuming that you have a subform in the detail section of the form. All you need to do is put code in the AfterUpdate of each of the fields that you have in your footer. That AfterUpdate will build a where criteria for you, and you will assign the subform to have that recordsource.

As an example say your 3 dropdowns leave you with criteria of Song="Happy"

Then you will update the recordsource of the subform to

Select * from TableName where Song='Happy'

To do this, you can refer to

Me.controls("subformname").form.recordsource="Select * from TableName where Song='Happy'"

Just remember that in order to create the recordsource you will want to check if any of the dropdowns are null, you will not want to include them in the criteria.

Please let me know if I can provide more assistance.
 
Thanks for your reply.
I've try this code (got from the internet) but not all of them work. I meant I can only perform record search on cboArea and/or txtyear, I could not do any thing with others criteria. Please help me!!!
Code:
Private Sub cmdSearch_Click()

Dim strWhere As String
Dim lngLen As Long

    If Not IsNull(Me.txtSongNumber) Then
        strWhere = strWhere & "([SongNumber] = Like ""*" & Me.txtSongNumber & "*"") AND "
    End If
    
    If Not IsNull(Me.txtTitle) Then
        strWhere = strWhere & "([Title] = Like ""*" & Me.txtTitle & "*"") AND "
    End If

    If Not IsNull(Me.txtAuthor) Then
        strWhere = strWhere & "([Author] = Like ""*" & Me.txtAuthor & "*"") AND "
    End If

    If Not IsNull(Me.txtKeywords) Then
        strWhere = strWhere & "([Keywords] = Like ""*" & Me.txtAuthor & "*"") AND "
    End If
    
    If Not IsNull(Me.txtYear) Then
        strWhere = strWhere & "([Year] = """ & Me.txtYear & """) AND "
    End If

    If Not IsNull(Me.cboArea) Then
        strWhere = strWhere & "([Area] = """ & Me.cboArea & """) AND "
    End If

    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "Please enter at least a criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
    
End Sub

Bee
 
I think I got the idiot typos
 
I see that you are using a filter in the end. One thing that you can do to speed it up is to have the form have no recordsource at all, but then when they choose the filters, you set the recordsource to "Select * from YourTable Where " & strWhere. This would be faster than the filters, because the filters force you to show all of the data and then reduce it, versus the recordsource which will only show a bit of the data.

Otherwise your code looks really good, so I am not sure what issue you are having.

Please let me know if you need more assistance.
 
I have the exact same scenario where I am building the where clause based on the users' selection and then applying it as the subform's recordsource when the user clicks the Find button.

The problem I'm having is when I click the "Find" button the subform goes blank; however, I soon realized that if I used my mouse scroll wheel that the records display one record at a time in the order that they are in the table as if it hides the records that don't match the search criteria. So for example if my search results are records 10 and 20 I have to scroll down until record 10 displays and then if I keep scrolling record 20 eventually displays. However if the results were records 10 and 11 then they would both appear once I scrolled to them. I have the subform's Default View property set to Continuous Forms. Help says I'm running Access 2003 (11.6566.8107) SP2

Any suggestions?
 
Hmadyson,
I'm back to this thread with your response.
The problem that I have is because of the FILTER, I think it's exact like what you had comments.
I'm not so sure what to do with this
you set the recordsource to "Select * from YourTable Where " & strWhere
Could you please tell me how it should be done?
Thanks

Bee
 
Hi all,
Also in this form, I have a Reset button, which does not wor k as I expected.
When the result return with records, the reset button work perfectly.
As the result returns no record, I click "Reset" then the form return with all records, I click "Reset" again then it finally clear all fields.
Please help me get rid of this problem. Many thanks
Code:
Private Sub cmdReset_Click()
    Dim ctl As Control
    
    For Each ctl In Me.Section(acDetail).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        End Select
    Next
        
    'Remove the form's filter.
    Me.FilterOn = False
End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top