Searching multiple fields that aren't a given for input

[WithoutPause]

Let's say I have two fields named txtFirstName and txtLastName. I would like to do a search on these fields, but doing a search for both isn't required. I could search for the first name, last name, or both. The results need to be displayed back into the search fields that the user entered data into. FYI, I'm using more than two fields but I figured if I got the templates for dynamically creating a query I could figure out the rest.

[June7]

Here is one method:
http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...mtoreport.html

[WithoutPause]

Does the "*" aspect work for any type of field? I have some fields that couldn't be left with a default value of "" as they are check boxes, numeric, etc. Also, once the data is returned how do I process the data to display in the proper fields on the form?

[June7]

Yes/No fields are problemmatic. I don't use dynamic parameters in queries so not an issue for me.

I use VBA code to construct criteria strings. http://allenbrowne.com/ser-62.html


The data displays in controls bound to fields of the form RecordSource. Just as with any bound form.

[WithoutPause]

Once I add in the query code, how do I get the results into the form?

[June7]

Which method are you trying?

You don't 'get the results into the form'. The records are already in the form, the methods suggested just filter the records already there.

[WithoutPause]

I went the detailed route where I am using If statements to add to the query code. I can add it below. I'm using unbound fields as I didn't want the data to be changed in the database. If I do bound each of the fields, how do I get it to where any update to that field doesn't update the database as well?

Private Sub cmdSearch_Click()
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.


    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field
    If Not IsNull(Me.H01) Then
        strWhere = strWhere & "([H01] Like ""*" & Me.H01 & """) AND "
    End If
    
    'Yes/No field and combo
    If Me.H02 = -1 Then
        strWhere = strWhere & "([H02] = True) AND "
    ElseIf Me.H02 = 0 Then
        strWhere = strWhere & "([H02] = False) AND "
    End If
    
    'Yes/No field and combo
    If Me.H03 = -1 Then
        strWhere = strWhere & "([H03] = True) AND "
    ElseIf Me.H03 = 0 Then
        strWhere = strWhere & "([H03] = False) AND "
    End If
    
    'Yes/No field and combo
    If Me.H04 = -1 Then
        strWhere = strWhere & "([H04] = True) AND "
    ElseIf Me.H04 = 0 Then
        strWhere = strWhere & "([H04] = False) AND "
    End If
    
    'Number field
    If Not IsNull(Me.H05) Then
        strWhere = strWhere & "(H05] = " & Me.H05 & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H06) Then
        strWhere = strWhere & "([H06] Like ""*" & Me.H06 & """) AND "
    End If
    
    'Text field
    If Not IsNull(Me.H07) Then
        strWhere = strWhere & "([H07] Like ""*" & Me.H07 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H08) Then
        strWhere = strWhere & "([H08] >= " & Format(Me.H08, conJetDate) & ") AND "
    End If
    
    'Number field
    If Not IsNull(Me.H09) Then
        strWhere = strWhere & "([H09] = " & Me.H09 & ") AND "
    End If
    
    'Number field
    If Not IsNull(Me.H10) Then
        strWhere = strWhere & "([H10] = " & Me.H10 & ") AND "
    End If
    
    'Date field
    If Not IsNull(Me.H11) Then
        strWhere = strWhere & "([H11] >= " & Format(Me.H11, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H12) Then
        strWhere = strWhere & "([H12] Like ""*" & Me.H12 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H13) Then
        strWhere = strWhere & "([H13] >= " & Format(Me.H13, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H14) Then
        strWhere = strWhere & "([H14] Like ""*" & Me.H14 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H15) Then
        strWhere = strWhere & "([H15] >= " & Format(Me.H16, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H16) Then
        strWhere = strWhere & "([H16] Like ""*" & Me.H16 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H17) Then
        strWhere = strWhere & "([H17] >= " & Format(Me.H17, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H18) Then
        strWhere = strWhere & "([H18] Like ""*" & Me.H18 & """) AND "
    End If
    
    'Number field
    If Not IsNull(Me.H19) Then
        strWhere = strWhere & "([H19] = " & Me.H19 & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H20) Then
        strWhere = strWhere & "([H20] Like ""*" & Me.H20 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H21) Then
        strWhere = strWhere & "([H21] >= " & Format(Me.H21, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H22) Then
        strWhere = strWhere & "([H22] Like ""*" & Me.H22 & """) AND "
    End If
    
    'Text field
    If Not IsNull(Me.H23) Then
        strWhere = strWhere & "([H23] Like ""*" & Me.H23 & """) AND "
    End If
    
    'Text field
    If Not IsNull(Me.H24) Then
        strWhere = strWhere & "([H24] Like ""*" & Me.H24 & """) AND "
    End If
    
    'Yes/No field and combo
    If Me.H25 = -1 Then
        strWhere = strWhere & "([H25] = True) AND "
    ElseIf Me.H25 = 0 Then
        strWhere = strWhere & "([H25] = False) AND "
    End If
    
    'Yes/No field and combo
    If Me.H26 = -1 Then
        strWhere = strWhere & "([H26] = True) AND "
    ElseIf Me.H26 = 0 Then
        strWhere = strWhere & "([H26] = False) AND "
    End If
    
    'Text field
    If Not IsNull(Me.H27) Then
        strWhere = strWhere & "([H27] Like ""*" & Me.H27 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H28) Then
        strWhere = strWhere & "([H28] >= " & Format(Me.H28, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H29) Then
        strWhere = strWhere & "([H29] Like ""*" & Me.H29 & """) AND "
    End If
    
    'Date field
    If Not IsNull(Me.H30) Then
        strWhere = strWhere & "([H30] >= " & Format(Me.H30, conJetDate) & ") AND "
    End If
    
    'Text field
    If Not IsNull(Me.H31) Then
        strWhere = strWhere & "([H31] Like ""*" & Me.H31 & """) AND "
    End If
    
    'Text field
    If Not IsNull(Me.H32) Then
        strWhere = strWhere & "([H32] Like ""*" & Me.H32 & """) AND "
    End If
    
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere


        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If


End Sub

[June7]

The search criteria must be entered into unbound controls. Other controls are bound to the data for display.

[WithoutPause]

Can't I just place the results back into the original unbound fields? That's what the original video were doing.

[June7]

The videos are NOT showing results placed back into unbound fields. The videos demonstrate entering criteria in unbound comboboxes that are used as parameters in query. Form or report are bound to query. Textboxes are bound to fields.

Allen Browne's method is just setting the Filter and FilterOn properties of a form that is bound to a table or query.

[WithoutPause]

Those are not combo boxes that he creates in the second video. He even calls them text boxes. How do I get the initial part of what he is trying to do, but the results into the original unbound fields? I tried the previously quoted SQL and it failed.

[June7]

Textbox, combobox, or listbox - the technique is the same - reference controls on form for criteria.

The options presented are:

1. parameterized query as the RecordSource for form or report - 1st and 3rd DataPig videos, 2nd video is an unbound form with code that just opens parameterized query object

2. form RecordSource is table or non-parameterized query - VBA constructs filter string and sets Filter and FilterOn properties of form
This constructed string can also be used in the WHERE CONDITION argument to pass filter criteria to a form or report as it opens
DoCmd.OpenForm "form name", , , strWHERE


Create a form in Single or Continuous view, put the unbound controls for criteria input in the form header section, bind other controls to fields of form RecordSource in the detail section, build button click event VBA procedure as appropriate for one of the 2 options.

[WithoutPause]

So let me gets this straight, I can't generate sql code, run it, parse the returned data, and then put that data back into the fields I used as input?

[Dal Jeanis]

You COULD do that, but it would be very unstandard. Usually, you put your filter items on a form header and return the results in the detail, or put the filter items on a main form and return the results on a subform.

Generally, you use a general query for the form/subform, and use VBA and the values of the filter text boxes/comboboxes to set a filter that limits the queries to the desired items. Any manipulation is done on the detail form or subform, depending on your chosen architecture.

(The way you could do that is have a command button that would execute the query and update all those filter text boxes. I doubt if it would ever be as effective an user interface as the standard method.)

[June7]

Could, with a lot more VBA code. But Why Bother? That doesn't retrieve the record(s). Edits to the data would not pass directly to table. Would require more VBA to save the edited data. That's managing data with UNBOUND form. Adding a new record adds more complication.

Why do you object to unbound controls for criteria input and then other controls bound to fields for displaying the records?