locked
Problems Passing Combo Box Values to Cross Tab Criteria RRS feed

  • Question

  • Hello!

    I am having a problem passing values from my form with two combo boxes, to my crosstab query criteria fields.  I have set my parameters inside of the query as:

    [Forms]![NspApplic]![bulletinCombo] Text
    [Forms]![NspApplic]![regionCombo] Text


    My button to trigger the OpenQuery is:

    OpenQuery
    QueryName: Crosstab Query
    View Datasheet
    Data Mode: Read Only
    
    Parameters
    Forms!CurrentForm!bulletinCombo = [bulletinCombo]
    Forms!CurrentForm!regionCombo = [regionCombo]

    When I double click the crosstab query without invoking the form, it has two pop up windows asking for the bulletin and the combo.  Typing in the values manually produces the expected results.  Great.

    The combo boxes are a set of text fields that I specified.  They are only options for valid criteria, however when I click the button to open the form I always produce an error stating "A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control."  It then goes into a Macro Error 2753 with the Action Name of SetParameter.

    I have searched all over and every thread is solved by simply setting the parameters in the query itself (which I have already done.), hopefully you guys can help me!

    Wednesday, May 24, 2017 4:12 PM

All replies

  • In this case, you don't want to override the query parameters at all, so you shouldn't be specifying the parameter overrides

    Forms!CurrentForm!bulletinCombo = [bulletinCombo]
    Forms!CurrentForm!regionCombo = [regionCombo]

    in your macro.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, May 24, 2017 4:59 PM
  • Hey Dirk,

    So if you mean leaving the values after the "=" blank, I have tried that with the same results.

    If you mean omitting them all together, they are auto-populated when creating the macro procedure.

    If neither of these, I am sorry I am misunderstanding.

    Wednesday, May 24, 2017 5:57 PM
  • Doing it that way works for me in my test.

    I just noticed that the parameters defined in your query (as you posted them) are:

    [Forms]![NspApplic]![bulletinCombo] Text
    [Forms]![NspApplic]![regionCombo] Text

    but the parameters listed in your macro are:

    Forms!CurrentForm!bulletinCombo 
    Forms!CurrentForm!regionCombo

    Is that really what you have?  What are the actual criteria used in your query?  Those criteria, the parameters in your query, and the parameters shown by the macro should all be the same controls on the same form.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, May 24, 2017 6:11 PM
  • Yeah, that is right.  They are different.  Here is my issue with THAT (So many different issues).

    These cannot be changed inside of the Macro Tools:

    Forms!NspApplic!bulletinCombo =
    Forms!NspApplic!regionCombo = 

    Now I tried to combat this by doing two things:

    1. Changing it in parameters to match what is shown here.  When I did that, the brackets placed themselves when I closed.

    2. Converting this to VBA script and then adding the brackets in myself.  This stuck in the code, but produced the same error. 

    My criteria are three regions, and about 24 bulletins.  In my combo box, those are the only options.

    Also I am sorry, I used the same parameter:

    Forms!NspApplic!bulletinCombo
    for both (ignoring the brackets), I thought I replaced them in both.  I will fix that in the opening post.

    Wednesday, May 24, 2017 7:48 PM
  • I'm afraid I'm not understanding you.  Where does the term "CurrentForm" you used in your query parameters come from?  Are you saying that Access generated that name?

    What is the actual SQL of your query?

    You have a form named "NspApplic", correct?  And that form has controls on it named "bulletinCombo" and "regionCombo", which you intend to use as criteria for your query?

    For what it's worth, I created a simple crosstab query with a form-based criterion, and I had no trouble creating a macro to open that query, using the action OpenQuery, without ever having to specify anything special about the query's parameters.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, May 24, 2017 8:54 PM
  • The "CurrentForm" was supposed to be a pseudonym I was going to use throughout the entire post.  I forgot to continue using it.  Throughout all of my code, NspApplic is the form I am using.  Sorry for the confusion.  Here is my code.

    '------------------------------------------------------------
    ' loadCrosstab_Click
    '
    '------------------------------------------------------------
    Private Sub loadCrosstab_Click()
    On Error GoTo loadCrosstab_Click_Err
    
        DoCmd.SetParameter "Forms!NspApplic!bulletinCombo", Me.bulletinCombo
        DoCmd.SetParameter "Forms!NspApplic!regionCombo", Me.regionCombo
        DoCmd.OpenQuery "NSP Applicability", acViewNormal, acReadOnly
    
    
    loadCrosstab_Click_Exit:
        Exit Sub
    
    loadCrosstab_Click_Err:
        MsgBox Error$
        Resume loadCrosstab_Click_Exit
    
    End Sub


    Thursday, May 25, 2017 6:13 PM
  • Is that code you posted executing from form NspApplic?  If so, then that means the form is open, and you shouldn't need the DoCmd.SetParamter statements at all.  Have you tried it without those statements at all, just:

    Private Sub loadCrosstab_Click()
    On Error GoTo loadCrosstab_Click_Err
    
        DoCmd.OpenQuery "NSP Applicability", acViewNormal, acReadOnly
    
    
    loadCrosstab_Click_Exit:
        Exit Sub
    
    loadCrosstab_Click_Err:
        MsgBox Error$
        Resume loadCrosstab_Click_Exit
    
    End Sub

    ?You seem to have changed from using a macro, as in your first post, to using VBA code.  Any special reason, or are you just trying stuff?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, May 25, 2017 8:00 PM
  • Hey Dirk,

    I decided to go a different route.  Our team decided that Access was actually not the best route to go. Of course!  Thank you for your help.

    Wednesday, June 7, 2017 2:51 PM