Search Form

[wisni1rr]

Hello.
I am trying to make a search for for my database. I already have a form created for input of the data. I would like to copy that form and use it as a search form and feed the results of the search into a report that I have already created. It should return all values associated with that field if that particular text box is left blank.

Any and all help/advice would be appriciated!!!

wisni1rr

[NeoPa]

You should understand that a question thread is not a job sepcification form. We help you with your work. We don't provide solutions to your spec.

You may find these help you :

Example Filtering on a Form.
Cascaded Form Filtering.

If not, then we need a proper question from you before we can help you further.

[Mihail]

If the question is "How to duplicate a form in your database" I think that a good idea is:

-Create a copy of your Database (lets name it "CopyOfDB") ;
-Open "CopyOfDB", rename the table you need to duplicate, close "CopyOfDB";
-Open the original DB and import the renamed table from "CopyOfDB";

Of course you can duplicate how many objects you need in "one step"

Append: Or use "Save As" and save the form under different name

Good luck !

[wisni1rr]

I apologize if I have used this thread incorrectly, NeoPa.

Hopefully, this will explain it better. If not, please direct me to the appropriate place to post it or let me know if this is not the website I should be trying to find my answer on.

I have a form that is comprised of vertical columns with text boxes for input and a tabs control to flip pages. These are the only control types on this particular form. What I am trying to do is essentially use each bound text box controls like parameter queries where all spaces that are blank will search all records and if a value is added to a text box it will search only records that contain that specific term.

I hope that helps.

Thank you again for taking the time to read this.

[NeoPa]

You need to look at some of the sticky threads found in most forums (There are two in this one alone) which give you a lot of good advice and direction on how to ask questions so that they can be answered. The Submit a Question page also has a number of clear instructions which cover much of what you need to know.

Your latest post goes some way there, but it's clearly not been checked for grammar or punctuation as I tried to read it and, not already knowing what you were trying to say, could not put it together in any way that made much sense. This is also down to describing your situation in terms that you haven't even covered (so we don't know about) which makes it all but impossible to understand the rest of what you say. I understand that describing things in words is something most people are far from skilled at, but if we're to offer help then that has to start from a position of understanding what you're saying. Face to face is always a lot easier, but it has to be written down to be read so that's the situation. In case it's not clear from this, I see this as necessary advice rather than criticism. You've clearly made an attempt to work within the guidelines so no censure, just explanation (as requested) on what is what.

Clearly you can try other forums if you feel these demands are too stringent, but they all work on fundamentally the same principles, so any questions you ask anywhere will benefit from due care and attention before you commit it to the thread.

If your next post manages to make things clearer then I'll happily look at it again for you and see what I can do to help.

[TheSmileyCoder]

Unless your very familier with VBA I would suggest the following simpler approach:

Use the built in "Filter By Form" function in Access to do the filtering.
Create a button (btn_PrintRepor t) to open the report, with the following VBA code behind it:

Private sub btn_PrintReport_Click()
If Me.FilterOn and Me.Filter & ""<>"" Then
  docmd.OpenReport "ReportName",acViewPreview,,me.filter
else
  docmd.OpenReport "ReportName",acViewPreview
End If
End Sub

Replace ReportName with the name of your report, but leave the "" around the name.

If this solution is not adequate for your needs, come back. I would also suggest that you try searching this forum for other threads on search forms, since there are quite a few threads allready here in the access section.

[wisni1rr]

I think you are on the right track TheSmileyCoder.
I have a form that has text box controls for each field for all my tables in my database. I want the user to enter filter commands into the text box(s) then click a search button and display the filterd results into a report that I've already have designed.
As an example:

County        [textbox]
Sold          [textbox]
Tax Amount    [textbox]

I would like to search multiple values or ranges, such as:

County         [Brady OR Medow]
Sold           [5,000-7,000   ]
Tax Amount     [              ]

I hope this approach is easier to understand. I'm sorry my grammer is poor. I am much better in a verbal conversation, as you have already suspected. I do appriciate your time to view my question and answer it with your advice or solution.

I may be wrong, but am I trying to make a filter by from?

I want my form to open in the search mode and with a button, generate the filtered results into a report that I have already created.

[TheSmileyCoder]

You can certainly make a search form, but it involves a fair amount of VBA coding to setup, and as I mentioned there are several threads about this allready, im sure you can find what you need in one of those if you look for it.

My alternate approach is not exactly what your asking for, but I think it could take you 80% of the way with only 10% of the work involved. Only you can determine if you need the last 20%, and if your willing to do the extra 90% work for it.

My suggested method simply uses the form you allready have, as well as the built in functionality Filter By Form in access, to build the filter string, and passes that string on to your report, only printing the results that match (Allthough I did not explain that all that clearly in my original post).

[NeoPa]

It's true that conversation is a much easier approach to transferring information than explaining from scratch all in one go. Your latest post does give us much more in the way of clues though. Let me comment on that somewhat if I may.

What you want is a little complicated (I'm English so when I say that it means pretty darn complicated), especially for someone with little or no experience. Smiley is pretty well on track when he explains you can do much of this with filtering by form, and this is also true of simple filtering by unbound controls as you have them. Example Filtering on a Form gives an idea of the basics of this, but it doesn't cover ranges or lists for a specific field. They are both possible to handle, but you need to recognise such a specification when entered as well as knowing how to handle such matches.

A simple match in the WHERE clause is handled by [Field] = ???. This is covered in more detail in the linked article. You can also specify a wildcard match using [Field] Like ???. Find Wildcard Characters in an Access Database explains what can be done with these. For a range you can use the syntax [Field] Between ??? And ??? and for a list [Field] In(???,???,???).

In all these cases the types of field determine how the literal values used will need to be used. The first link provides examples of numeric, string and date literals. This shows, for instance, that for your County example you'd want something like :

[County] In('Brady','Medow')

See Quotes (') and Double-Quotes (") - Where and When to use them for a fuller explanation of this.

It seems I already posted one of these links (the most fundamental) earlier in the thread. If you had looked at it then and seen what it could have done for you I doubt we'd still be stuck at this stage of the conversation. We can't do everything for you. You need to start looking at things and make a start for yourself. That way we can continue to help, moving forward with you as you try, and yes - fail, but progressing anyway.

[wisni1rr]

I found a tutorial that does nearly everything I'm looking to do.

'Purpose:   This module illustrates how to create a search form, _
            where the user can enter as many or few criteria as they wish, _
            and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                    we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    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 example. Use quotes around the value in the string.
    If Not IsNull(Me.txtFilterCity) Then
        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
    End If

    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterMainName) Then
        strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If

    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboFilterLevel) Then
        strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
    End If

    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.cboFilterIsCorporate = -1 Then
        strWhere = strWhere & "([IsCorporate] = True) AND "
    ElseIf Me.cboFilterIsCorporate = 0 Then
        strWhere = strWhere & "([IsCorporate] = False) AND "
    End If

    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([EnteredOn] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If

    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([EnteredOn] < " & Format(Me.txtEndDate + 1, conJetDate) & ") 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

Private Sub cmdReset_Click()
    'Purpose: Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control

    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next

    'Remove the form's filter.
    Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    'Me.Filter = "(False)"
    'Me.FilterOn = True
End Sub

Full tutorial: Here

The only thing I need to change is how it displays. I would like to have the filtered results display in a report. Is this a possibility with this approach?

Thanks again for your help guys!

I'm not a complete stranger to VBA. I took a course on it at my community college a few years ago.

[TheSmileyCoder]

Assuming your form and the report have the same fields, you can you use the method I have allready suggested of opening the report with the same filter, as is applied to the form.