Link to home
Create AccountLog in
Avatar of loepem
loepem

asked on

Pop-Up Form to Filter Report

I'm trying to create a pop-up form to filter a report.  The form has 5 drop down combo boxes and then the 3 command buttons.  The five menus are searching for either numbers or letters.  The first menu is looking for either 1 or 2, the second is looking for a three digit number, the third is a letter along with the fourth and the fifth is a couple letters.  

I'm very confused with the syntax and I continue to get errors.  I have been so turned around in the different forums that I'm really not sure what kind of code I need.

Right now I am able to pull up the pop-up menu in the report and it has data for the first 3 options, but for the second two it asks for me to enter a parameter value.  

This is my code right now, and as I'm sure you guessed I am a beginner and very confused!

Private Sub Clear_Click()
    Dim intCounter As Integer
    For intCounter = 1 To 5
        Me("Filter" & intCounter) = ""
    Next
End Sub

Private Sub Close_Click()
    DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptFeedwater" 'Close the Feedwater report.
DoCmd.Restore 'Restore the window size
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptFeedwater", A_PREVIEW 'Open Feedwater report.
DoCmd.Maximize 'Maximize the report window.
End Sub


Private Sub Set_Filter_Click()
Dim strSQL As String, intCounter As Integer

'Build SQL String.
    For intCounter = 1 To 5
        If Me("Filter" & intCounter) <> " " Then
                     
            strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "                           
            End If
    Next
    If strSQL <> "" Then
        ' Strip Last " And ".
        strSQL = Left(strSQL, (Len(strSQL) - 5))
  ' Set the Filter property.
        Reports![rptFeedwater].filter = strSQL
        Reports![rptFeedwater].FilterOn = True
    End If
    End Sub

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

better is to attach your db. check Attach File below
Avatar of loepem
loepem

ASKER

I'm not sure I can do that.  I think my database is considered proprietary information.
can't you create dummy data?

it is not that simple to figure out what you are missing based on what you posted..

without your db, sorry i can't help you..
Avatar of loepem

ASKER

Would it work if I just gave you the skeleton of my db with no information?
at least place some dummy data..
Avatar of loepem

ASKER

ok i'll work on that and get back to you
Avatar of loepem

ASKER

Like I said before, I'm not very good at Access, plus I'm just an intern so I really am not sure what information I am allowed to give out, and I don't think it's any!

Is there any way you could give me the code for writing a pop-up form filter? I'm not using dates or special formats, like i said before just numbers and letters. If that makes a difference?
Avatar of loepem

ASKER

Here is a skeleton of my database. Please let me know what I'm doing wrong, I'm so frustrated!!
db3.mdb
Avatar of loepem

ASKER

That works great! What was wrong?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of loepem

ASKER

ok thanks so much!