How do I programatically use a dynamic paramatized query as .RST

KDWW
KDWW used Ask the Experts™
on
I previously received help with 'programatically opening an attachment without user input'.  However, prior to that point the user enters parameters into a query to limit the record set.  With danishani's help I was able to get the code below.  But now I realize that the query I'm employing has two parameters that are controlled by two controls on a form that the user fills in (Year & Month).  I thought I could use a snippet of VBA, but it looks like is it difficult crossing methodologies mid-stream.  When I run the code I get a message about the query needing 2 parameters, which I assume are the two I have already set in the query designer: "[FORMS]![DateParmswMonths]![combo89]" and "[FORMS]![DateParmswMonths]![combo89]" I need help deciding which path to take.
Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Const cstrQueryName = "QryHazMart"
    'Const strTable = "HazMart"
    Const strField = "10156Rpt" ' Attachment field in Table1

    Set dbs = CurrentDb
    'Set rst = dbs.OpenRecordset(strTable)
    DoCmd.SetWarnings False
    Set rst = dbs.OpenRecordset(cstrQueryName)
    DoCmd.SetWarnings True


    'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What is the sql code for QryHazMart?

What is the code in OpenFirstAttachmentAsTempFile?

Author

Commented:
I've attached the code for both.
SELECT HazMart.YR, HazMart.Mo, HazMart.[10156Rpt]
FROM HazMart
WHERE (((HazMart.YR)=[FORMS]![DateParmswMonths]![combo89]) AND ((HazMart.Mo)=[FORMS]![DateParmswMonths]![combo93]));



Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String

    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String

    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
        Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
        strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
        If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
    End If

    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.

End Function 'OpenFirstAttachmentAsTempFile

Open in new window

Is the DateParmswMonths form open with dates filled in when this code is being run?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
Correct.  I am using those constrols to select the desired record from the table of the query 'qryHasMart'.
If you run this code with the form open with values entered to you get the same message?
Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Const cstrQueryName = "QryHazMart"
    'Const strTable = "HazMart"
    Const strField = "10156Rpt" ' Attachment field in Table1

    Set dbs = CurrentDb
       Set rst = dbs.OpenRecordset(cstrQueryName)
 

       rst.Close
End Function

Open in new window

Author

Commented:
Yes.
I don't suppose the database file could be attached for me to troubleshoot?

Author

Commented:
I would have no problem with that, however, the db is too big.
I don't need all of the data

Author

Commented:
Ok, I was able to peel off the elements specific to this portion.  The form 'DateParmswMonths' starts the sequence providing the user with year and month to select.   This is to grab the record of the year and month combination. 'Run Report' command button is supposed to run the '10156' form which is supposed to open the attachment in the 'Hazmart' table.  Hopefully this makes sense and that I have supplied you with enough info you need to make an informed decision.

Thank you, in advance, for your help!!
Test.accdb
Intersting, it appears to work correctly and does not prompt me for the parameters.  The only way I can get the prompts is to run the query without the form open.  I can keep messing with it...
I created the error, it wasn't until clicking on the button on the 1056Rpt form that I get the error

Author

Commented:
Yes, the code in the click event of the button was in the load event of the  form.  I took it when trying to troubleshoot the issue.
Try this code out.

I found the solution at http://msdn.microsoft.com/en-us/library/aa160564(v=office.11).aspx
Public Function TestOpenFirstAttachmentAsTempFile()

    

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    
    Const cstrQueryName = "QryHazmart"
    'Const strTable = "HazMart"
    Const strField = "10156Rpt" ' Attachment field in Table1

    Set dbs = CurrentDb
    'Set rst = dbs.OpenRecordset(strTable)
    DoCmd.SetWarnings False
    Set qdf = dbs.QueryDefs(cstrQueryName)
    qdf.Parameters(0) = [Forms]![DateParmswMonths]![Combo89]
    qdf.Parameters(1) = [Forms]![DateParmswMonths]![Combo93]
    Set rst = qdf.OpenRecordset
    DoCmd.SetWarnings True


    'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
End Function

Open in new window

Author

Commented:
Perfec!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial