Results 1 to 2 of 2

Myserious parameter during record filtering code

  1. #1
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012

    Myserious parameter during record filtering code

    Hello Everyone,

    I'm having a tedious difficulty with a form of mine.

    The General Situation:
    I have a form on which a group of employees track customers who are set to graduate our program in the current (or in this case next) calendar year. Each employee (administrator) in this group has a set of customers who they are responsible for. Since we are talking about a couple hundred records every year and multiple employees handling these customers, I thought it would be nice if they could filter the record source for this form upon opening.

    The Specific Problem:
    I generated some VBA Code for the On Open form even that allows the user to filter the records based on the initials of the employee assigned to those customers. This is a field in the table that is the record source for the form. That part works nicely. An input box pops up, telling them to enter the initials of the administrator they want to retrieve records for or press * for all records. However, after the user enters the initials of the administrator and presses enter, another input box shows up. This unscripted input box has the title "Enter Parameter Value" and the text is just the initials the user previously entered. One must enter the initials again in order for the correct record source to show up.

    In fact this gets a little more confusing if you close and reopen the form. If you do this you are asked for the parameter value both before and after the filter selection inputbox appears. Below is the code I am using for the record source filtering:

    Private Sub Form_Open(Cancel As Integer)
        Dim strCA As String, strSQL As String, strWHERE As String
        On Error GoTo ErrHandler
        strSQL = "SELECT * FROM GandHTracker"
        strCA = InputBox("Enter the CA for the paricipants you wish to view" & vbCr & vbCr & _
                "VP, LM, KR, SH, or JS" & vbCr & "Or press * to view all participants", "Show All/Filter")
        If strCA = "" Then
            Cancel = True
        ElseIf strCA = "*" Then
            Me.RecordSource = strSQL
            strWHERE = " WHERE CA Like " & strCA & ""
            Debug.Print strSQL
            Debug.Print strWHERE
            Me.RecordSource = strSQL & strWHERE
        End If
        Exit Sub
        MsgBox Err.Number & ": " & Err.Description
    End Sub

  2. #2
    Monterey_Manzer is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Never Mind. I had also used the strCA variable to set the caption for a Form label but I removed that based on aesthetic reasons. This seems to have generated a parameter in my record source I could not see until I removed the code that set the label caption. I am sorry to bother everyone with this simple problem but who knows, someone else might find the code useful even though it's really basic.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering using variable parameter
    By George in forum Access
    Replies: 3
    Last Post: 05-28-2012, 07:24 AM
  2. Filtering Blanks, Report Parameter
    By leamas in forum Reports
    Replies: 1
    Last Post: 05-17-2012, 11:07 AM
  3. filtering of combo box / vba code
    By Kotoca in forum Programming
    Replies: 2
    Last Post: 02-08-2012, 07:15 AM
  4. help with filtering code
    By ninachopper in forum Access
    Replies: 14
    Last Post: 09-09-2010, 05:16 PM
  5. filtering code
    By ninachopper in forum Access
    Replies: 2
    Last Post: 08-06-2010, 09:56 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Tech Forums: Microsoft Office Forums