Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    7

    Red face Unanswered: VBA Passing Parameters to Saved Query, then call report

    Good afternoon,

    I have a saved Access 2010 query that I use in a report. The query has one parameter entered in the criteria section:


    [Township]

    There are multiple townships that I need to gather in a list box (i.e. "Bloom" OR "Bremen" OR "West")

    I wrote a VBA procedure to gather the townships from the list box into a string with these values, no problem.

    Question: How do I get the string value with the multiple townships into the Saved Query in Access so my report can print with those parameters?

    I have researched this for days and this is my first post on dBforums. Any help would be greatly appreciated.

    Here is the code I have so far. I took out my lame attempts at passing the form's parameter to the criteria in the query.

    It does reference the query before the report prints, but the query still asks for [Township] to be manually entered by end-user.

    Private Sub Command12_Click()
    Dim varItem As Variant
    Dim strSelected As String


    For Each varItem In Me!TownshipCriteria.ItemsSelected
    If strSelected <> "" Then
    strSelected = strSelected & " OR "
    End If
    strSelected = strSelected & "'" & Me!TownshipCriteria.ItemData(varItem) & "'"
    Next varItem

    Debug.Print strSelected

    DoCmd.OpenReport "rpt-AOandBOR Results", acViewPreview, "qry-ASSESSOR_BOARD-DATA"

    End Sub

    Thank you in advance for any help.

    Fondly,
    Tanya S.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Nothing you've done there gets passed to the report. Personally I'd take the criteria out of the query and use the wherecondition argument:

    Multi-Select Listbox
    Paul

  3. #3
    Join Date
    Apr 2013
    Posts
    7
    Paul,

    Thank you so much for the quick reply!

    I don't want to bother you with syntax, but I haven't done this before.

    Is EmpID IN

    the name of the query criteria variable or the field name?

    Can you give me an example of how to tie TWO criteria variables together with the WHERE Clause?

    Example of field name and variable name:

    Field name is Township
    Criteria variable in saved query is [EnterTownship]
    Field name is Tax Year
    Criteria variable in saved query is [EnterTaxYear]

    Again, thank you so very much for any help.

  4. #4
    Join Date
    Apr 2013
    Posts
    7
    Paul,

    Your suggestion worked perfectly. Sorry I jumped to ask for more syntax. You have saved me oodles of time.

    Thank you again and God Bless

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help! Sorry for the delay, I was out of town over the weekend.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •