I am having trouble finding information on making a Search Form where I can search for a record set. From what I have gathered I need to:

1- make a query with the fields that I am looking to search by,
2- make the form based on fields that I am looking for or criteria I want it to include(making sure its unbound)
3- Write code for the Button that actually does the search. (making sure it opens another form to view the results at the end.)


Does that sound right? A more detailed explanation of how to do this would be very much appreciated.

Yes. In the query designer in the criteria lines, add code to take parameters from the look up (presumably combo boxes) on your launch form. Like this:

[Forms]![frmMyForm].[cboMyCombo]

Did that help?


mmcdonal

Yes it helps alot!

So I just want to make sure I do this Correctly.

- I need to place the parameters ie [Forms]![fndQuote].[cboSalesID] in the SalesID Field in the fndQuote Query and the same for the other combo boxes

What if the user doesnt select anything from one of the Combo Boxes?

And how would I show Matching Records? Should I make a DoCmd to open my quote form at the end of the code of the Search Button?

Thanks again!

Okay I "Borrowed" some code but its in 2003 format. I am making a .mdb (2000)so I am not sure how this will work also I dont know why but i am having problems with the " If IsNothing ". I hope this will address the problem of the user not selecting one of the fields. Let me know your thoughts.

Private Sub cmdSearch_Click()
Dim varWhere As Variant

    ' Initialize to Null
    varWhere = Null

    ' If specified a SalesID value
    If Not IsNothing(Me.cboSalesID) Then
        ' .. build the predicate
        varWhere = "[SalesID] LIKE '" & Me.cboSalesID & "*'"
    End If

    ' Do DepartmentID next
    If Not IsNothing(Me.cboDepartmentID) Then
        ' .. build the predicate
        ' Note: taking advantage of Null propogation
        ' so we don't have to test for any previous predicate
        varWhere = (varWhere + " AND ") & "[DepartmentID] LIKE '" & Me.cboDepartmentID & "*'"
    End If

    ' Do CustomerID next
    If Not IsNothing(Me.cboCustomerID) Then
        ' .. build the predicate
        varWhere = (varWhere + " AND ") & "[CustomerID] LIKE '" & Me.cboCustomerID & "*'"
    End If

    ' Do QuoteStatus next
    If Not IsNothing(Me.cboQuoteStatus) Then
        ' .. build the predicate
        varWhere = (varWhere + " AND ") & "[QuoteStatus] LIKE '" & Me.cboQuoteStatus & "*'"
    End If

    ' Finally, do QuoteID By
    If Not IsNothing(Me.cboQuoteNumber) Then
        ' .. build the predicate
        varWhere = (varWhere + " AND ") & "[QuoteID] = " & Me.cboQuoteNumber
    End If

    ' Check to see that we built a filter
    If IsNothing(varWhere) Then
        MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
        Exit Sub
    End If

    ' See if any rows with a quick DLookup
    If IsNothing(DLookup("QuoteID", "tblQuoteNumber", varWhere)) Then
        MsgBox "No Companies meet your criteria.", vbInformation, gstrAppTitle
        Exit Sub
    End If

    ' Open Companies filtered
    ' Note: if form already open, this just applies the filter
    DoCmd.OpenForm "frmQuotes", WhereCondition:=varWhere
    ' Done
    DoCmd.Close acForm, Me.Name

End Sub

First of all:

If Not IsNothing(Me.cboSalesID) Then

Should be:

If Not IsNull(Me.cboSalesID) Or Me.cboSalesID = "" Then

I use check for both Null and "" since Access is a little flakey with empty fields.

Also,

Dim varWhere As Variant

Should be

Dim varWhere As String

Also, this is okay:

 If Not IsNull(Me.cboSalesID) Or Me.cboSalesID = "" Then
        ' .. build the predicate
        varWhere = "[SalesID] LIKE '" & Me.cboSalesID & "*'"
    End If

But this needs to be changed:

' Do CustomerID next
If Not IsNothing(Me.cboCustomerID) Then
     ' .. build the predicate
     varWhere = (varWhere + " AND ") & "[CustomerID] LIKE '" & Me.cboCustomerID & "*'"
End If

To this:

'Do CustomerID next
If Not IsNull(Me.cboCustomerID) Or Me.cboCustomerID = "" Then
   If IsNull(varWhere) Or varWhere = "" Then
     varWhere = "[CustomerID] = " & Me.cboCustomerID 'assuming this is an integer
   Else
     varWhere = varWhere & " AND [CustomerID] = " & Me.cboCustomerID
   End IF
End If


HTH

mmcdonal

Sweet it worked like a charm

Your the Man!

You may want to look up "Flow Control" in a VBA or VBScript reference for more information on this subject. Glad to have helped.

mmcdonal

I have 2 Reference books "MS Access 2003 Inside Out" and the WROX "Access 2003 VBA" reference and neither had the term Flow Control in it. I looked on Google and they had some great descriptions. Is there another name for "Flow Control" so that i can look again inthe reference guides?


BTW I cant thank you enough for answering my questions I wish there was more i could do to show my gratitude.

No, I think the term is Programming Flow Control. Look for the section that describes conditionals and their use, and maybe it is around there.

Just pass it along.

mmcdonal