Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Unanswered: VBA Pass Value To Parameter Query

    Hi all,

    Using Access 2007.

    I am trying to pass a value to a parameter query while loopint through an array.

    However, I keep receiving a prompt to supply a "Location" value.
    Any ideas what I'm doing wrong?

    thx
    w

    Code:
    Option Compare Database
    
    Sub CreateMaps()
        '
        'Purpose:
        'Create map files
        '
        'References:
        '---------------------------------------------
        '
        'Resources:
        '---------------------------------------------
        '
        'Date         Developer       Action
        '---------------------------------------------
        '05/17/2012   ws              Created
        
        'Initialize
         DoCmd.SetWarnings False
         
         Dim db As Database
         Dim rst As Recordset
         Dim rs As Recordset
         Dim qdf As QueryDef
         Dim varArray() As Variant
         Dim lngRecCount As Long
         Dim intCounter As Integer
         Dim strFilePath As String
         
        'qsel_Map is a parameter query to return all fields needed to
        'construct end-user Map files
         Set db = CurrentDb
         Set rst = db.OpenRecordset("Active_Locations")
         Set qdf = db.QueryDefs("qsel_Map")
         strFilePath = "C:\Map\"
        
        'How many records in the table
         lngRecCount = rst.RecordCount
        
        'Fill the array with the Loaction Code
         ReDim varArray(1 To lngRecCount) As Variant
         For intCounter = 1 To lngRecCount
            varArray(intCounter) = rst.Fields("LocationCode")
         Next intCounter
         
        'Delete records from map table
         DoCmd.OpenQuery "qdel_tblMap_CLEAR"
         
        'Loop through the array to pass each loaction to the query
         For lngRecCount = lngRecCount To 1 Step -1
            qdf.Parameters("Location") = varArray(lngRecCount)
            Set rs = qdf.OpenRecordset
            
            'Insert the query results to the map table
             DoCmd.OpenQuery "qapp_tblMap"
            
            DoCmd.TransferSpreadsheet _
            Transfertype:=acExport, _
            Spreadsheettype:=acSpreadsheetTypeExcel9, _
            TableName:="tblMap", _
            FileName:=strFilePath & "Cust_Map_" & varArray(lngRecCount) & ".xls", _
            HasFieldNames:=True
         
         Next lngRecCount
         
        'Tidy up
         Set db = Nothing
         Set rst = Nothing
         Set rs = Nothing
         Set qdf = Nothing
         
         DoCmd.SetWarnings True
    
    End Sub

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    H'mm. I'm not sure about the error message, but I do see a potential error in your VBA.
    Code:
         For intCounter = 1 To lngRecCount
            varArray(intCounter) = rst.Fields("LocationCode")
         Next intCounter
    Two issues. First of all, I don't see a declared numerical variable called "LocationCode."

    Also, your For statement might need tweaking. The Fields property array of a file structure begins with Field(0), not (1). Also, where's the .MoveNext method to go to the next record? You're only addressing one record.

    I'm confused, I admit.

    Sam

  3. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    I'm very much a noobie to Access VBA, more comfortable with Excel VBA, so I could easily have something wrong in the code.

    "LocationCode" is a field in the table Active_Locations referred to in the code here
    Code:
    Set rst = db.OpenRecordset("Active_Locations")
    Please advise if this is not correct

    I added the the rst.MoveNext here
    Code:
    For intCounter = 1 To lngRecCount
         varArray(intCounter) = rst.Fields("LocationCode")
         rst.MoveNext
    Next intCounter
    I added Option Base 1 at the top of the module

    I added a bit of code to test that the array loads properly - it does
    Code:
    For I = LBound(varArray) To UBound(varArray)
         Debug.Print I; varArray(I)
    Next I
    I still receive the prompt to supply the parameter for the query.
    Any other ideas?

    thx
    w

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Unfortunately, I don't have the time at this minute to devote to properly analyze your response. However, two things I think. One, change the Option Base to 0. That way you can address Fields(0). Also, please copy and paste the query's SQL statement so we can have a look at it.

    Sam

    PS Because of the holiday, I probably won't have time to sit and think until Tuesday. I apologize for the wait. SL

  5. #5
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    I tried Option Base 0 with the same result.

    Here is the SQL for the query, "qsel_Map"
    Code:
    SELECT tblMaps.Key AS [Location], tblMaps.Site AS CustomerCode, tblMaps.Description AS CustomerDescription, tblMaps.O_Market AS O_Customer, O_Customer_BU.OApp_BU AS O_BU, O_ParentSeg.O_ParentSeg AS CBT, O_BusinessSeg.[OApp - BusinessSeg] AS CBT_Team, tblCustomerMaster.ACTIVE AS SF_Status
    FROM (((tblMaps LEFT JOIN tblCustomerMaster ON tblMaps.O_Market=tblCustomerMaster.[ACCOUNT*]) LEFT JOIN O_Customer_BU ON tblMaps.O_Market=O_Customer_BU.OApp_Market) LEFT JOIN O_BusinessSeg ON tblMaps.O_Market=O_BusinessSeg.[BU Rollup]) LEFT JOIN O_ParentSeg ON O_BusinessSeg.[OApp - BusinessSeg]=O_ParentSeg.OSeg
    GROUP BY tblMaps.Key, tblMaps.Site, tblMaps.Description, tblMaps.O_Market, O_Customer_BU.OApp_BU, O_ParentSeg.O_ParentSeg, O_BusinessSeg.[OApp - BusinessSeg], tblCustomerMaster.ACTIVE, O_Customer_BU.OApp_BU
    HAVING (((tblMaps.Key)=[Location]) AND ((O_Customer_BU.OApp_BU)<>"All Customers" Or (O_Customer_BU.OApp_BU)<>"All"));
    thx
    w

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    After studying the SQL for a while, it seems to me that the HAVING clause is the culprit. Remove the phrase
    Code:
    ((tblmaps.Key)=[Location]) AND
    from the HAVING clause. Make sure you delete the 2 sets of parentheses. The reason is that since tblmaps.Key is mapped in the SQL as being renamed "Location," Access thinks you now have two objects, a field mapped as "Location" and a parameter (that always shows up in [brackets]) named "Location." I'm also confused by the logic of the phrase: since tblmaps.Key is mapped as Location, it always equals Location, by definition. Why test for it?

    Be aware that you may get other errors after fixing this, if there are logic errors in your VBA.

    Sam
    Last edited by Sam Landy; 05-29-12 at 15:37. Reason: clarification

  7. #7
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    I made a copy of the original SQL and deleted the portion of the HAVING clause as you recommended.

    I ran the query by itself. It works, but it returns all locations, not just 1 location and it does not prompt me to input the location value.

    The VBA also fails, here
    Code:
    qdf.Parameters("Location") = varArray(lngRecCount)
    since we removed "Location" parameter from the query

    thx
    w

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi,

    After a careful analysis, I came up with the following. First of all, put the parameter back in (I know you never removed it; it's OK). However, the following line is NG
    Code:
    For lngRecCount = lngRecCount To 1 Step -1
    You can't use a For statement to test a variable against itself. It always equals itself. Use the variable intCounter, which is not in use.

    Also, because of the nature of the Select query qsel_Map, which needs a parameter passed from VBA, you have to embed the SQL in the VBA, including the parameter's value.

    Add /change as follows:

    Code:
        Dim strSQL As String
        Dim Loc as Long
    
        strSQL = "SELECT tblMaps.Key AS [Location], tblMaps.Site AS CustomerCode, tblMaps.Description AS CustomerDescription, tblMaps.O_Market AS O_Customer, O_Customer_BU.OApp_BU AS O_BU, O_ParentSeg.O_ParentSeg AS CBT, O_BusinessSeg.[OApp - BusinessSeg] AS CBT_Team, tblCustomerMaster.ACTIVE AS SF_Status " _
        & "FROM (((tblMaps LEFT JOIN tblCustomerMaster ON tblMaps.O_Market=tblCustomerMaster.[ACCOUNT*]) LEFT JOIN O_Customer_BU ON tblMaps.O_Market=O_Customer_BU.OApp_Market) LEFT JOIN O_BusinessSeg ON tblMaps.O_Market=O_BusinessSeg.[BU Rollup]) LEFT JOIN O_ParentSeg ON O_BusinessSeg.[OApp - BusinessSeg]=O_ParentSeg.OSeg " _
        & "GROUP BY tblMaps.Key, tblMaps.Site, tblMaps.Description, tblMaps.O_Market, O_Customer_BU.OApp_BU, O_ParentSeg.O_ParentSeg, O_BusinessSeg.[OApp - BusinessSeg], tblCustomerMaster.ACTIVE, O_Customer_BU.OApp_BU " _
        & "HAVING (((tblMaps.Key)=" & [Loc] & ") AND ((O_Customer_BU.OApp_BU)<>'All Customers' Or (O_Customer_BU.OApp_BU)<>'All'));"
        'Loop through the array to pass each location (sic) to the query
        For intCounter= lngRecCount To 1 Step -1
            Loc = varArray(intCounter)
            qdf.SQL = strSQL
            Set rs = qdf.OpenRecordset
            .
            .
            .
        Next intCounter
    BTW, this assumes that the location is numeric. If the location is alpha, or alphanumeric, change the Dim statement
    Code:
    Dim Loc As Long
    to
    Code:
    Dim Loc As String
    Also, you have to add some quotes, as follows:
    The line
    Code:
    & "HAVING (((tblMaps.Key)=" & [Loc] & ")
    has to be
    Code:
    & "HAVING (((tblMaps.Key)='" & [Loc] & "')
    with additional single-quotes around the Loc variable.

    To simplify your needed effort, copy and paste what I did above into your VBA just before the "'Loop" line.

    Good luck,
    Sam
    You should read the Help files regarding queries, parameters, and parameter passing in VBA in order to appreciate everything I did.

  9. #9
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    I changed the For..Next Loop
    Code:
    For intCounter = lngRecCount To 1 Step -1
    ...
    Next
    For readability (for me), I changed the SQL string to this structure instead of line continuation
    Code:
        'SQL String
         strSQL = ""
         strSQL = strSQL & "SELECT tblMAPS.Key AS [LinkLocation], "
         strSQL = strSQL & "tblMAPS.Site_Market AS CustomerCode, "
         ...

    The parmater values are strings, so I changed to strLocation and updated the HAVING clause in the embedded SQL string
    Code:
         strSQL = strSQL & "HAVING (((tblMAPS.Key)='" & [strLocation] & "') "
    I am now receiving this error
    Run-time error '3061':
    Too few parameters. Expected 1.
    Debug points here
    Code:
            'Pass the SQL string to the Query definition
             qdf.SQL = strSQL
    I set a break point on qdf.SQL to check that the strLocation is correct..it is
    Do you see anything else that may be incorrect at this point?
    Full code below

    thx
    w
    Code:
    Option Compare Database
    Option Base 0
    
    Sub CreateMaps()
        '
        'Purpose:
        'Create segment map files
        '
        'References:
        '---------------------------------------------
        '
        'Resources:
        '---------------------------------------------
        '
        'Date         Developer       Action
        '---------------------------------------------
        '05/17/2012     w            Created
        '05/31/2012     w            Added SQL string
        
        'Initialize
         DoCmd.SetWarnings False
         
         Dim db As Database
         Dim rst As Recordset
         Dim rs As Recordset
         Dim qdf As QueryDef
         Dim varArray() As Variant
         Dim lngRecCount As Long
         Dim intCounter As Integer
         Dim strFilePath As String
         Dim strSQL As String
         Dim strLocation As String
         Dim I As Integer
         
         Set db = CurrentDb
         Set rst = db.OpenRecordset("Active_Locations")
         strFilePath = "C:\MapTest\"
        
        'How many records in the table
         lngRecCount = rst.RecordCount
        
        'Fill the array with the Loaction Code
         ReDim varArray(1 To lngRecCount) As Variant
         For intCounter = 1 To lngRecCount
            varArray(intCounter) = rst.Fields("LocationCode")
            rst.MoveNext
         Next intCounter
    
    '     For I = LBound(varArray) To UBound(varArray)
    '        Debug.Print I; varArray(I)
    '     Next I
    
        'SQL String
         strSQL = ""
         strSQL = strSQL & "SELECT tblMaps.Key AS [Primary_Location], "
         strSQL = strSQL & "tblMaps.Site_Market AS CustomerCode, "
         strSQL = strSQL & "tblMaps.Description AS CustomerDescription, "
         strSQL = strSQL & "tblMaps.O_Market AS O_Customer, "
         strSQL = strSQL & "O_Customer_BU.ABC_BU AS O_BU, "
         strSQL = strSQL & "O_Parent_Segment.O_Parent_Segment AS CBT, "
         strSQL = strSQL & "O_Business_Segment.[ABC - Business Segment] AS CBT_Team, "
         strSQL = strSQL & "CustomerMaster.ACTIVE AS CRM_Status "
         strSQL = strSQL & "FROM (((tblMaps LEFT JOIN CustomerMaster "
         strSQL = strSQL & "ON tblMaps.O_Market=CustomerMaster.[ACCOUNT*]) "
         strSQL = strSQL & "LEFT JOIN O_Customer_BU "
         strSQL = strSQL & "ON tblMaps.O_Market=O_Customer_BU.ABC_Market) "
         strSQL = strSQL & "LEFT JOIN O_Business_Segment "
         strSQL = strSQL & "ON tblMaps.O_Market=O_Business_Segment.[BU Rollup]) "
         strSQL = strSQL & "LEFT JOIN O_Parent_Segment "
         strSQL = strSQL & "ON O_Business_Segment.[ABC - Business Segment]=O_Parent_Segment.O_Segment "
         strSQL = strSQL & "GROUP BY tblMaps.Key, tblMaps.Site_Market, tblMaps.Description, tblMaps.O_Market, O_Customer_BU.ABC_BU, O_Parent_Segment.O_Parent_Segment, O_Business_Segment.[ABC - Business Segment], CustomerMaster.ACTIVE, O_Customer_BU.ABC_BU "
         strSQL = strSQL & "HAVING (((tblMaps.Key)='" & [strLocation] & "') "
         strSQL = strSQL & "AND ((O_Customer_BU.ABC_BU)<>""All Customers"" "
         strSQL = strSQL & "OR (O_Customer_BU.ABC_BU)<>""All""));"
         
        'Loop through the array to pass each weblink loaction to the query and output to Excel workbook
         For intCounter = lngRecCount To 1 Step -1
         
            'Delete records from map table
             DoCmd.OpenQuery "qdel_tblMap_CLEAR"
    
            'Pass the array value to the parameter
             strLocation = varArray(intCounter)
             
            'Pass the SQL string to the Query definition
             qdf.SQL = strSQL
             
            'Open the recordset
             Set rs = qdf.OpenRecordset
            
            'Insert the query results to the map table
             DoCmd.OpenQuery "qapp_tblMap"
            
            'Transfer the map table to an Excel workbook
             DoCmd.TransferSpreadsheet _
             Transfertype:=acExport, _
             Spreadsheettype:=acSpreadsheetTypeExcel9, _
             TableName:="tblMap", _
             FileName:=strFilePath & "Cust_Map_" & varArray(intCounter) & ".xls", _
             HasFieldNames:=True
         
         Next intCounter
         
        'Tidy up
         Set db = Nothing
         Set rst = Nothing
         Set rs = Nothing
         Set qdf = Nothing
         
         DoCmd.SetWarnings True
    
    End Sub

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    My mistake. Change the line
    Code:
          strSQL = strSQL & "HAVING (((tblMaps.Key)='" & [strLocation] & "') "
    to
    Code:
          strSQL = strSQL & "HAVING (((tblMaps.Key)='" & strLocation & "') "
    by removing the [brackets] around strLocation. They are what's triggering Access to look for a parameter.

    SL

  11. #11
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    This line appears to be causing an error:
    Code:
    qdf.SQL = strSQL
    Error:
    Run-time error '91':
    Object variable or With block variable not set
    So I tried this:
    Code:
    'Delete the query definition if exists
             For Each qdf In db.QueryDefs
                If qdf.Name = "NewQueryDef" Then
                    db.QueryDefs.Delete "NewQueryDef"
                End If
             Next qdf
    
            'Assign reference to query defintion
             Set qdf = db.CreateQueryDef("NewQueryDef", strSQL)
    Code execution now proceeds, but same problem, I am being prompted to enter a parameter value

    thx
    w

  12. #12
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    It seems you deleted the line
    Code:
    Set qdf = db.QueryDefs("qsel_Map")
    That's why Access said Error 91 Object variable not set. It was looking for the qdf variable. Put the line back where it was.

    SL

  13. #13
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    I did delete the "Set qdf..."
    I guess I assumed I could not or should not call a stored query if the SQL String is embedded in the VBA procedure?

    "qsel_Map" is the name of a select query currently in the db.

    Thx
    w

  14. #14
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    All you're doing when you "Set" an object variable is identifying the variable with the object that has the name in the Set statement. You aren't opening it or calling it at that point.

    Doing it this way, you can open the stored query in the designer at any time and make sure that the SQL statement is correct, including any VBA-added information (in this case, the value of varArray(intCounter)). If it's not, you can easily troubleshoot it, because the SQL statement is there in all its glory, mistakes included.

    By the way, I still think there's an error in the code. But I want you to tell me if/where the code breaks. We'll fix those first, then we'll get to the error, I have no doubt.

    Sam

  15. #15
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sam,

    I added the "qdf" back in, but now I am thinking the recordset and query defintion are not the way to go.

    I did a little Googling and it does not appear to be most efficient or even possible to bulk transfer recordset to a table

    Appears better way is "INSERT INTO"
    But now I receive this error:
    Run-time error '3450':
    Syntax error in query. Incomplete query clause.
    The query was working previously (At least appeared to)
    I added before SELECT:
    Code:
         strSQL = strSQL & "INSERT INTO ""tblMap"" (""Location"", ""CustCode"", ""CustDescription"", ""OCustomer"", ""OBU"", ""BT"", ""BT_Team"", ""Status"")"
    The full revised (simpler, shorter) code is below

    thx
    w

    Code:
    Option Compare Database
    Option Base 0
    
    Sub CreateMaps()
        '
        'Purpose:
        'Create segment map files
        '
        'References:
        '---------------------------------------------
        '
        'Resources:
        '---------------------------------------------
        '
        'Date         Developer       Action
        '---------------------------------------------
        '05/17/2012     w            Created
        '05/31/2012     w            Added SQL string
        
        'Initialize
         DoCmd.SetWarnings False
         
         Dim db As Database
         Dim rst As Recordset
         Dim varArray() As Variant
         Dim intRecCount As Integer
         Dim intCounter As Integer
         Dim strFilePath As String
         Dim strSQL As String
         Dim strLocation As String
         Dim strSQLClearTable as String
         
         Set db = CurrentDb
         Set rst = db.OpenRecordset("Active_Locations")
         strFilePath = "C:\MapTest\"
        
        'How many records in the table
         intRecCount = rst.RecordCount
        
        'Fill the array with the Loaction Code
         ReDim varArray(1 To intRecCount) As Variant
         For intCounter = 1 To intRecCount
            varArray(intCounter) = rst.Fields("LocationCode")
            rst.MoveNext
         Next intCounter
    
        'SQL Strings
         strSQLClearTable = ""
         strSQLClearTable = strSQLClearTable & "DELETE tblMap.* "
         strSQLClearTable = strSQLClearTable & "FROM tblMap;"
    
         strSQL = ""
         strSQL = strSQL & "INSERT INTO ""tblMap"" (""Location"", ""CustCode"", ""CustDescription"", ""OCustomer"", ""OBU"", ""BT"", ""BT_Team"", ""Status"")"
         strSQL = strSQL & "SELECT tblMaps.Key AS [Primary_Location], "
         strSQL = strSQL & "tblMaps.Site_Market AS CustomerCode, "
         strSQL = strSQL & "tblMaps.Description AS CustomerDescription, "
         strSQL = strSQL & "tblMaps.O_Market AS O_Customer, "
         strSQL = strSQL & "O_Customer_BU.ABC_BU AS O_BU, "
         strSQL = strSQL & "O_Parent_Segment.O_Parent_Segment AS CBT, "
         strSQL = strSQL & "O_Business_Segment.[ABC - Business Segment] AS CBT_Team, "
         strSQL = strSQL & "CustomerMaster.ACTIVE AS CRM_Status "
         strSQL = strSQL & "FROM (((tblMaps LEFT JOIN CustomerMaster "
         strSQL = strSQL & "ON tblMaps.O_Market=CustomerMaster.[ACCOUNT*]) "
         strSQL = strSQL & "LEFT JOIN O_Customer_BU "
         strSQL = strSQL & "ON tblMaps.O_Market=O_Customer_BU.ABC_Market) "
         strSQL = strSQL & "LEFT JOIN O_Business_Segment "
         strSQL = strSQL & "ON tblMaps.O_Market=O_Business_Segment.[BU Rollup]) "
         strSQL = strSQL & "LEFT JOIN O_Parent_Segment "
         strSQL = strSQL & "ON O_Business_Segment.[ABC - Business Segment]=O_Parent_Segment.O_Segment "
         strSQL = strSQL & "GROUP BY tblMaps.Key, tblMaps.Site_Market, tblMaps.Description, tblMaps.O_Market, O_Customer_BU.ABC_BU, O_Parent_Segment.O_Parent_Segment, O_Business_Segment.[ABC - Business Segment], CustomerMaster.ACTIVE, O_Customer_BU.ABC_BU "
         strSQL = strSQL & "HAVING (((tblMaps.Key)='" & [strLocation] & "') "
         strSQL = strSQL & "AND ((O_Customer_BU.ABC_BU)<>""All Customers"" "
         strSQL = strSQL & "OR (O_Customer_BU.ABC_BU)<>""All""));"
         
        'Loop through the array to pass each weblink loaction to the query and output to Excel workbook
         For intCounter = intRecCount To 1 Step -1
            
    	'Delete records from map table
             db.Execute (strSQLClearTable)
    
            'Pass the array value to the parameter
             strLocation = varArray(intCounter)
             
            'Append records to the map table
             db.Execute (strSQL)
            
            'Transfer the map table to an Excel workbook
             DoCmd.TransferSpreadsheet _
             Transfertype:=acExport, _
             Spreadsheettype:=acSpreadsheetTypeExcel9, _
             TableName:="tblMap", _
             FileName:=strFilePath & "Cust_Map_" & varArray(intCounter) & ".xls", _
             HasFieldNames:=True
         
         Next intCounter
         
        'Tidy up
         Set db = Nothing
         Set rst = Nothing
         
         DoCmd.SetWarnings True
    End Sub
    Last edited by goss; 06-01-12 at 18:24.

Posting Permissions

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