Parameter Query Error (Have already Read previous Discussions)

M

Mennochio

New member
Local time
Today, 01:37
Joined
Aug 22, 2007
Messages
5
All,

I am having difficult with some code that is making use of a query that has a parameter in it. I have read the discussions here and think that I have made use of the suggestions to overcome the error. However, even with the code in place I am getting a popup box prompting me to enter the value of the parameter every time I run the code. Please help!

VBA Code:

Code: 
Private Sub cmdBonds_Click()

    Forms![frmExistingAssets]![Label1].Caption = ""
    Forms![frmExistingAssets]![Label2].Caption = ""
    Forms![frmExistingAssets]![Label3].Caption = ""
    Forms![frmExistingAssets]![Label4].Caption = ""
    
    Forms![frmExistingAssets]![Label1].Caption = "Creating all Bond files..."
    DoCmd.SetWarnings False
    DoCmd.Hourglass True
    

    outpath = get_ExportPath
    
    Dim rst As DAO.Recordset
    Dim rec As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim PortName As String
    Dim PortName1 As String
    Dim QryNameBND As String
    Dim Filename As String
    Dim stAppName As String
    
    Application.Echo False
    Set rst = CurrentDb.OpenRecordset("tblPortfolioNames")
    Do Until rst.EOF
    
        PortName = rst!CAMRAPortfolioName
        PortName1 = rst!ProphetPortfolioName
        Me.txtPortfolio = PortName
        QryNameBND = "qryProphetBonds_AB"
        Filename = outpath & "AB_" & PortName1 & ".txt"
       [B]
    
        Set qd = CurrentDb.QueryDefs(QryNameBND)
        qd.Parameters![Name of Portfolio] = PortName [/b]
        Set rec = qd.OpenRecordset 
        If rec.RecordCount < 1 Then
        GoTo Next_Record
        Else
        DoCmd.TransferText acExportDelim, "AB BONDS RPT", QryNameBND, Filename, True
        End If
    
Next_Record:
        rst.MoveNext
    Loop
    
    Set rst = Nothing
    Set rec = Nothing
    Set qd = Nothing
    
        stAppName = outpath & "RPT.BAT"
        Call Shell(stAppName, vbMaximizedFocus)
        
        Wait 6000

    
    Application.Echo True
    Forms![frmExistingAssets]![Label1].Caption = "Bond files done!"
    
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    
End Sub

Here is the SQL version of my Query ( I made it in design view - not in SQL clearly):
Code: 
SELECT "*" AS [$], 1 AS SPCODE, Left(Recon_D1.cusip,3) AS CUSIP_1, Mid(Recon_D1.cusip,4,3) AS CUSIP_2, Right(Recon_D1.cusip,3) AS CUSIP_3, tblProphetBondRatings.Bond_Rating, 1 AS INIT_AS_GRP, RECON_D1.COUPON_RATE AS INCOME_PC, IIf([INT_DIV_FREQ]="ANNUAL",1,IIf([INT_DIV_FREQ]="MONTHLY",12,IIf([INT_DIV_FREQ]="QUARTRLY",4,2))) AS INCOME_FREQ, Year([Maturity_Date]) AS MAX_RED_YEAR, Month([Maturity_Date]) AS RED_MONTH, Day([Maturity_Date]) AS RED_DAY, Sum(RECON_D1.MARKETVALUE) AS INIT_MV, 1 AS INIT_MV_CLEAN, 1 AS INIT_MV_USED, 0 AS INIT_XYLD_PC, Sum(RECON_D1.BOOKVALUE) AS INIT_BV, 1 AS INIT_BV_CLN, Sum(RECON_D1.QUANTITY) AS INIT_NV, 3 AS ABV_METHOD, IIf([atrib_type]="CALLS",1,IIf([atrib_type]="PUTS",2,0)) AS BOND_TYPE, IIf([BOND_TYPE]=1,Year([FirstCallDate]),IIf([BOND_TYPE]=2,Year([FirstPutDate]),0)) AS MIN_RED_YEAR, IIf([BOND_TYPE]=1,Month([FirstCallDate]),IIf([BOND_TYPE]=2,Month([FirstPutDate]),0)) AS MIN_RED_MONTH, IIf([BOND_TYPE]=0,0,2) AS CALL_TYPE, 0 AS CALL_PERIOD, 0 AS AMO_TERM_IND, IIf([BOND_TYPE]=0,0,2) AS CALL_PRM_TYP
FROM (((RECON_D1 LEFT JOIN tblProphetBondRatings ON (RECON_D1.ACTUAL_MOODY = tblProphetBondRatings.MoodyRating) AND (RECON_D1.sec_group = tblProphetBondRatings.SecGroup)) LEFT JOIN SECATTRI ON RECON_D1.cusip = SECATTRI.cusip) LEFT JOIN qryFirstCallDate ON RECON_D1.cusip = qryFirstCallDate.cusip) LEFT JOIN qryFirstPutDate ON RECON_D1.cusip = qryFirstPutDate.cusip
WHERE (((RECON_D1.portfolio)=[Name of Portfolio]))
GROUP BY "*", 1, Left(Recon_D1.cusip,3), Mid(Recon_D1.cusip,4,3), Right(Recon_D1.cusip,3), tblProphetBondRatings.Bond_Rating, 1, RECON_D1.COUPON_RATE, IIf([INT_DIV_FREQ]="ANNUAL",1,IIf([INT_DIV_FREQ]="MONTHLY",12,IIf([INT_DIV_FREQ]="QUARTRLY",4,2))), Year([Maturity_Date]), Month([Maturity_Date]), Day([Maturity_Date]), 1, 1, 0, 1, 3, IIf([atrib_type]="CALLS",1,IIf([atrib_type]="PUTS",2,0)), 0, 0, RECON_D1.sec_group, qryFirstPutDate.FirstPutDate, qryFirstCallDate.FirstCallDate
HAVING (((RECON_D1.sec_group)="BONDS" Or (RECON_D1.sec_group)="SHORT TERM"));
 
Helo,

I'm affraid I'm just leaving work so can't fully answer your question, but I can see what's going wrong.

The 'qd.Parameters![Name of Portfolio] = PortName' is working fine, the point at which it is asking you for the Parameter is:

Code: 
DoCmd.TransferText acExportDelim, "AB BONDS RPT", [b]QryNameBND[/b], Filename, True

As you are trying to output the Query not the QueryDef which you have created.

See if that helps you along, if not I will try an look t this tomorrow.

Cheers,
 
This may be a dumb question, but how would I go about transfering the querydef? Is it the same way?

Also. The original problem was that the parameter was [Forms]![frmExistingAssets].[txtPortfolio] but if I put it that way the query doesn't find the object.
 
Sorry it taken AGES to get back to you.

I have tried since last week to answer this at work and decided to bring it home.

Sonce bringing it home I have thought of a second way in which you can do this (see further on) but I might as well ut my effort to paper, as it were.

This code is just about untested, all bar the text file output, so I'd expect you to run into a few bugs:

Code: 
Private Sub cmdBonds_Click()

Dim db                  As DAO.Database
Dim rstPortfolioName    As DAO.Database
Dim rsOutput            As DAO.Recordset
Dim portname            As String
Dim PortName1           As String
Dim strDelimiter        As String
Dim strParameter        As String
Dim strPath             As String
Dim strPathAndFile      As String
Dim FileNum             As Integer
Dim FullPath            As String
Dim lngFieldCount       As Long
Dim lngCounter          As Long
Dim objField            As DAO.Field
Dim strSelect           As String
Dim strGroup            As String
Dim strAppName          As String

Forms![frmExistingAssets]![Label1].Caption = ""
Forms![frmExistingAssets]![Label2].Caption = ""
Forms![frmExistingAssets]![Label3].Caption = ""
Forms![frmExistingAssets]![Label4].Caption = ""
Forms![frmExistingAssets]![Label1].Caption = "Creating all Bond files..."
DoCmd.SetWarnings False
DoCmd.Hourglass True
    
'Create the SQL string up to the WHERE clause
strSelect = strSelect & "SELECT "
strSelect = strSelect & "* AS [$], "
strSelect = strSelect & "1 AS SPCODE, "
strSelect = strSelect & "Left(Recon_D1.cusip,3) AS CUSIP_1, "
strSelect = strSelect & "Mid(Recon_D1.cusip,4,3) AS CUSIP_2, "
strSelect = strSelect & "Right(Recon_D1.cusip,3) AS CUSIP_3, "
strSelect = strSelect & "tblProphetBondRatings.Bond_Rating, "
strSelect = strSelect & "1 AS INIT_AS_GRP, "
strSelect = strSelect & "RECON_D1.COUPON_RATE AS INCOME_PC, "
strSelect = strSelect & "IIf([INT_DIV_FREQ]='ANNUAL',1, "
strSelect = strSelect & "IIf([INT_DIV_FREQ]='MONTHLY',12, "
strSelect = strSelect & "IIf([INT_DIV_FREQ]='QUARTRLY',4,2))) AS INCOME_FREQ,  "
strSelect = strSelect & "Year([Maturity_Date]) AS MAX_RED_YEAR, "
strSelect = strSelect & "Month([Maturity_Date]) AS RED_MONTH, "
strSelect = strSelect & "Day([Maturity_Date]) AS RED_DAY,  "
strSelect = strSelect & "Sum(RECON_D1.MARKETVALUE) AS INIT_MV, "
strSelect = strSelect & "1 AS INIT_MV_CLEAN, 1 AS INIT_MV_USED, "
strSelect = strSelect & "0 AS INIT_XYLD_PC, "
strSelect = strSelect & "Sum(RECON_D1.BOOKVALUE) AS INIT_BV, "
strSelect = strSelect & "1 AS INIT_BV_CLN, Sum(RECON_D1.QUANTITY) AS INIT_NV, "
strSelect = strSelect & "3 AS ABV_METHOD, "
strSelect = strSelect & "IIf([atrib_type]='CALLS',1, "
strSelect = strSelect & "IIf([atrib_type]='PUTS',2,0)) AS BOND_TYPE, "
strSelect = strSelect & "IIf([BOND_TYPE]=1,Year([FirstCallDate]), "
strSelect = strSelect & "IIf([BOND_TYPE]=2,Year([FirstPutDate]),0)) AS MIN_RED_YEAR, "
strSelect = strSelect & "IIf([BOND_TYPE]=1,Month([FirstCallDate]), "
strSelect = strSelect & "IIf([BOND_TYPE]=2,Month([FirstPutDate]),0)) AS MIN_RED_MONTH, "
strSelect = strSelect & "IIf([BOND_TYPE]=0,0,2) AS CALL_TYPE, "
strSelect = strSelect & "0 AS CALL_PERIOD, "
strSelect = strSelect & "0 AS AMO_TERM_IND, "
strSelect = strSelect & "IIf([BOND_TYPE]=0,0,2) AS CALL_PRM_TYP "
strSelect = strSelect & "FROM (((RECON_D1 LEFT JOIN tblProphetBondRatings "
strSelect = strSelect & "ON (RECON_D1.ACTUAL_MOODY = tblProphetBondRatings.MoodyRating) "
strSelect = strSelect & "AND (RECON_D1.sec_group = tblProphetBondRatings.SecGroup)) "
strSelect = strSelect & "LEFT JOIN SECATTRI ON RECON_D1.cusip = SECATTRI.cusip) "
strSelect = strSelect & "LEFT JOIN qryFirstCallDate ON RECON_D1.cusip = qryFirstCallDate.cusip) "
strSelect = strSelect & "LEFT JOIN qryFirstPutDate ON RECON_D1.cusip = qryFirstPutDate.cusip "

'Create the SQL string after the WHERE clause
strGroup = strGroup & "GROUP BY " * ", 1, "
strGroup = strGroup & "Left(Recon_D1.cusip,3), Mid(Recon_D1.cusip,4,3),  "
strGroup = strGroup & "Right(Recon_D1.cusip,3), "
strGroup = strGroup & "tblProphetBondRatings.Bond_Rating, 1,  "
strGroup = strGroup & "RECON_D1.COUPON_RATE,  "
strGroup = strGroup & "IIf([INT_DIV_FREQ]='ANNUAL',1,"
strGroup = strGroup & "IIf([INT_DIV_FREQ]='MONTHLY',12,"
strGroup = strGroup & "IIf([INT_DIV_FREQ]='QUARTRLY',4,2))), "
strGroup = strGroup & "Year([Maturity_Date]), "
strGroup = strGroup & "Month([Maturity_Date]), "
strGroup = strGroup & "Day([Maturity_Date]), 1, 1, 0, 1, 3, "
strGroup = strGroup & "IIf([atrib_type]='CALLS',1,IIf([atrib_type]='PUTS',2,0)), "
strGroup = strGroup & "0, 0, RECON_D1.sec_group, "
strGroup = strGroup & "qryFirstPutDate.FirstPutDate, "
strGroup = strGroup & "qryFirstCallDate.FirstCallDate "
strGroup = strGroup & "HAVING (((RECON_D1.sec_group)='BONDS' "
strGroup = strGroup & "Or (RECON_D1.sec_group)='SHORT TERM')); "

'Make comma the delimiter, change to suit
strDelimiter = ","
strPath = get_ExportPath

Set db = DBEngine(0)(0)
Set rstPortfolioName = CurrentDb.OpenRecordset("tblPortfolioNames")
With rstPortfolioName
'Loop through the Portfolio Names for the WHERE clause
Do Until .EOF

    portname = !CAMRAPortfolioName
    PortName1 = !ProphetPortfolioName
    Me!txtPortfolio = portname

    'Create the WHERE clause
    strParameter = "WHERE RECON_D1.portfolio = '" & portname & "'"
    Set rsOutput = db.OpenRecordset(strSelect & strParameter & strGroup, _
                        dbOpenForwardOnly, dbReadOnly)
                
    'Create the output path and file
    strPathAndFile = strPath & "AB_" & PortName1 & ".txt"

    intFileNum = FreeFile

    Open strPathAndFile For Output As #intFileNum

    With rsOutput
        lngFieldCount = .Fields.Count - 1

        On Error Resume Next
        .MoveFirst
        Do While Not .EOF
            For lngCounter = 0 To lngFieldCount
                    If lngCounter < lngFieldCount Then
                        Set objField = .Fields(lngCounter)
                        Print #intFileNum, objField.Value & strDelimiter;
                    Else
                        Print #intFileNum, objField.Value
                    End If
            Next
        .MoveNext
        Loop
    End With
Loop

Set rstPortfolioName = Nothing
Set rsOutput = Nothing
Set db = Nothing

strAppName = strPath & "RPT.BAT"
Call Shell(strAppName, vbMaximizedFocus)
       
Wait 6000
Application.Echo True
Forms![frmExistingAssets]![Label1].Caption = "Bond files done!"
DoCmd.SetWarnings True
DoCmd.Hourglass False
    
End Sub

One other reason I've left the above for you to look at, is it is a method which doesn't rely on there being a Query saved to create a Recordset with which you can work.

------------------------------------------------------------------

The second way you can do this is create a function which changes the WHERE value in a function:

You need to create a function like...

Code: 
Public strParameter     As String
Public Function NameOfPortfolio(strParameter As Variant) As String

If IsNull(strParameter) Or Len(strParameter) = 0 Then
    NameOfPortfolio = "Error!"
Else
    NameOfPortfolio = strParameter
End If

End Function

It fairly crude but should do the trick.

Then in your query replace your WHERE clause with:

Code: 
WHERE RECON_D1.portfolio=NameOfPortfolio()

Finally, you can use the following on your button click:

Code: 
Private Sub cmdBonds_Click()

Dim db                  As DAO.Database
Dim rstPortfolioName    As DAO.Database
Dim rsOutput            As DAO.Recordset
Dim portname            As String
Dim PortName1           As String
Dim strDelimiter        As String
Dim strPath             As String
Dim strPathAndFile      As String
Dim FileNum             As Integer
Dim FullPath            As String
Dim lngFieldCount       As Long
Dim lngCounter          As Long
Dim objField            As DAO.Field
Dim strAppName          As String

Forms![frmExistingAssets]![Label1].Caption = ""
Forms![frmExistingAssets]![Label2].Caption = ""
Forms![frmExistingAssets]![Label3].Caption = ""
Forms![frmExistingAssets]![Label4].Caption = ""
Forms![frmExistingAssets]![Label1].Caption = "Creating all Bond files..."
DoCmd.SetWarnings False
DoCmd.Hourglass True

'Make comma the delimiter, change to suit
strDelimiter = ","
strPath = get_ExportPath

Set db = DBEngine(0)(0)
Set rstPortfolioName = CurrentDb.OpenRecordset("tblPortfolioNames")
With rstPortfolioName
'Loop through the Portfolio Names for the WHERE clause
Do Until .EOF

    portname = !CAMRAPortfolioName
    PortName1 = !ProphetPortfolioName
    Me!txtPortfolio = portname

    'Create the WHERE clause
    strParameter = portname
    If NameOfPortfolio() = "Error!" Then GoTo NEXT_RECORD
    
    Set rsOutput = db.OpenRecordset(QryNameBND, _
                        dbOpenForwardOnly, dbReadOnly)
                
    'Create the output path and file
    strPathAndFile = strPath & "AB_" & PortName1 & ".txt"

    intFileNum = FreeFile

    Open strPathAndFile For Output As #intFileNum

    With rsOutput
        lngFieldCount = .Fields.Count - 1

        On Error Resume Next
        .MoveFirst
        Do While Not .EOF
            For lngCounter = 0 To lngFieldCount
                    If lngCounter < lngFieldCount Then
                        Set objField = .Fields(lngCounter)
                        Print #intFileNum, objField.Value & strDelimiter;
                    Else
                        Print #intFileNum, objField.Value
                    End If
            Next
        .MoveNext
        Loop
    End With
NEXT_RECORD:
Loop

Set rstPortfolioName = Nothing
Set rsOutput = Nothing
Set db = Nothing

strAppName = strPath & "RPT.BAT"
Call Shell(strAppName, vbMaximizedFocus)
       
Wait 6000
Application.Echo True
Forms![frmExistingAssets]![Label1].Caption = "Bond files done!"
DoCmd.SetWarnings True
DoCmd.Hourglass False
    
End Sub

Again, I haven't been able to test it, but what is happening is you are passing a value to a Public String and the Query is using this as the criteria.

Have a play with either and let me know how you get on or don't.

Cheers,
 
In your original query replace
WHERE (((RECON_D1.portfolio)=[Name of Portfolio]))

with

WHERE (((RECON_D1.portfolio)=Eval('[Forms]![frmExistingAssets].[txtPortfolio]')))

this should resolve the missing parameter error and avoid having to try to code around it.
I've found that you need to enclose the parameter reference in an EVAL function when using a parameter query as a source for a recordset.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom