How to show all records if fields is left blank in an unbound form?

[TanvirShahjahan]

Hi, for my course i am required to build a system for a small business, i have chosen a car dealership. I have made a search all stock form, so that a customer can fill it in to find whatever car it requires, e.g. automatic and petrol. I used an unbound form, but it only works if all fields are filled in. also the mileage and price need to be exact? how can i fix this

[jforbes]

Here is a real world example:

Private Sub doRefresh()
    
    Dim sSQL As String
    
    Dim bDrawingBOM As Boolean
    Dim bQuoteBOM As Boolean
    Dim bQuoteLC As Boolean
    
    Dim sSearchText As String
    Dim sSupplier As String
    Dim sComponent As String
    Dim sQuote As String
    Dim sDrawing As String
        
    sSQL = ""
    bDrawingBOM = Nz(Me.chkDrawingBOM.Value, True)
    bQuoteBOM = Nz(Me.chkQuoteBOM.Value, True)
    bQuoteLC = Nz(Me.chkQuoteLC.Value, True)
    sSearchText = Nz(Me.txtSearchText.Value, "")
    sSupplier = Nz(Me.cboSupplier.Value, "")
    sComponent = Nz(Me.cboComponent.Value, "")
    sQuote = Nz(Me.cboQuote.Value, "")
    sDrawing = Nz(Me.cboDrawing.Value, "")
    
    ' Type Criteria
    If Not (bDrawingBOM And bQuoteBOM And bQuoteLC) Then
        If bDrawingBOM Then sSQL = sSQL & " OR (DocType='Drawing' AND LineType='BOM')"
        If bQuoteBOM Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='BOM')"
        If bQuoteLC Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='LC')"
        sSQL = sSQL & " AND (" & Right(sSQL, Len(sSQL) - 4) & ") "
    End If
    
    ' Text and ComboBoxes
    If Len(sSearchText) > 0 Then
        sSQL = sSQL & " AND ("
        sSQL = sSQL & "       DocNumber LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & "    OR Description LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & "    OR Supplier LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & "    OR SupplierPartNumber LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & " )"
    End If
    If Len(sSupplier) > 0 Then sSQL = sSQL & " AND Supplier LIKE '*" & sSupplier & "*'"
    If Len(sComponent) > 0 Then sSQL = sSQL & " AND Component LIKE '*" & sComponent & "*'"
    If Len(sQuote) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sQuote & "*'"
    If Len(sDrawing) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sDrawing & "*'"
    
    ' Build the SQL String
    If Len(sSQL) > 5 Then
        sSQL = "SELECT * FROM LineItem WHERE " & Right(sSQL, Len(sSQL) - 5)
    Else
        sSQL = "SELECT * FROM LineItem "
    End If    
    If gDevEnvironment Then Debug.Print sSQL    
    Me.LineItemDatasheet.Form.RecordSource = sSQL
    
End Sub

It will create an output like this:

SELECT * FROM LineItem WHERE ((DocType='Drawing' AND LineType='BOM') OR (DocType='Quote' AND LineType='LC'))  AND (       DocNumber LIKE '*test*'    OR Description LIKE '*test*'    OR Supplier LIKE '*test*'    OR SupplierPartNumber LIKE '*test*' ) AND Supplier LIKE '*THERMAL*'
Or
SELECT * FROM LineItem WHERE (       DocNumber LIKE '*test*'    OR Description LIKE '*test*'    OR Supplier LIKE '*test*'    OR SupplierPartNumber LIKE '*test*' ) AND DocNumber LIKE '*Quotevalue*'

Instead of setting the RecordSource, you could set the Form's Filter property. Just an option.

You can use Between to specify a mileage range.

[TanvirShahjahan]

Hi sorry, I'm only in 6th form, my coding is not very good. here is a screen shot of what I've done/Users/Tanvir/Desktop/Screen Shot 2015-01-27 at 19.19.47.png

[TanvirShahjahan]

this is the type of code i used [forms]![Search Stock Form]![Model] sorry the screenshot did not attach

[jforbes]

The idea is to build up a RecordSource or Filter based on what the user has filled out on the Form. By building it up based on what is supplied, the fields that don't have anything supplied for them are ignored and left out of the Where Clause for the SQL. There is another way to do which produces a somewhat static SQL but it is much more confusing to update, read, and explain.

Since when doing something like this, the fields are referenced more than once in Code, it is easier and possibly faster to load them into Variables. So in the following code, there are variables declared to hold the values of the Fields on the Form along with a String to use to build the SQL. Once the variables are declared, they are filled in with the values from the Form. The Nz() function will make sure we don't stuff a Null value into any of the Variables:

    Dim sSQL As String
    
    Dim bDrawingBOM As Boolean
    Dim bQuoteBOM As Boolean
    Dim bQuoteLC As Boolean
    
    Dim sSearchText As String
    Dim sSupplier As String
    Dim sComponent As String
    Dim sQuote As String
    Dim sDrawing As String
        
    sSQL = ""
    bDrawingBOM = Nz(Me.chkDrawingBOM.Value, True)
    bQuoteBOM = Nz(Me.chkQuoteBOM.Value, True)
    bQuoteLC = Nz(Me.chkQuoteLC.Value, True)
    sSearchText = Nz(Me.txtSearchText.Value, "")
    sSupplier = Nz(Me.cboSupplier.Value, "")
    sComponent = Nz(Me.cboComponent.Value, "")
    sQuote = Nz(Me.cboQuote.Value, "")
    sDrawing = Nz(Me.cboDrawing.Value, "")
...

Next, the code inspects the user supplied values and if there is something supplied, it adds the SQL to the SQL variable:

    ' Type Criteria
    If Not (bDrawingBOM And bQuoteBOM And bQuoteLC) Then
        If bDrawingBOM Then sSQL = sSQL & " OR (DocType='Drawing' AND LineType='BOM')"
        If bQuoteBOM Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='BOM')"
        If bQuoteLC Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='LC')"
        sSQL = " AND (" & Right(sSQL, Len(sSQL) - 4) & ") "
    End If
    
    ' Text and ComboBoxes
    If Len(sSearchText) > 0 Then
        sSQL = sSQL & " AND ("
        sSQL = sSQL & "       DocNumber LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & "    OR Description LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & "    OR Supplier LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & "    OR SupplierPartNumber LIKE '*" & sSearchText & "*'"
        sSQL = sSQL & " )"
    End If
    If Len(sSupplier) > 0 Then sSQL = sSQL & " AND Supplier LIKE '*" & sSupplier & "*'"
    If Len(sComponent) > 0 Then sSQL = sSQL & " AND Component LIKE '*" & sComponent & "*'"
    If Len(sQuote) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sQuote & "*'"
    If Len(sDrawing) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sDrawing & "*'"

When building the SQL String, the code prepends an " AND " to the SQL Variable no matter what. It will be clipped off right before the SQL Variable is used. It is just to include it at all times and assume it is there than to test for it over and over. This is where the prepended " AND " is removed:

    ' Build the SQL String
    If Len(sSQL) > 5 Then
        sSQL = "SELECT * FROM LineItem WHERE " & Right(sSQL, Len(sSQL) - 5)
    Else
        sSQL = "SELECT * FROM LineItem "
    End If

Lastly the SQL is used. Here a subForm named LineItemDatashe et has it's RecordSource set to the SQL Variable:

    Me.LineItemDatasheet.Form.RecordSource = sSQL

[zmbd]

TanvirShahjahan

According to the terms of use of the site, we do not normally answer homework/course-work questions.

jforbes has been very kind and given you a few very good answers/suggestions.

Should you have any further difficulty, we are going to need to see your work - quite simply, it's not fair to yourself nor to your classmates to have an expert(s) such as JF or myself do your assignment.