asking query parameters to the user

[davikokar]

hallo,

I have an access db where the user can create his own queries. Then he can select one query from a combobox in a form and say: print the result in excel. This works fine if the query doesn't contain any parameter. If the query contains a parameter my code will crash (obviously, it is because I don't ask for the parameter).

The thing is: is there a way to analize a query and if there are parameters, asking them to the user, before I run the query.

This is the code I am using:

The OpenExcel works with the query that the user can choose in the form. The doWork print the result of the query in an Excel sheet.

Private Sub openExcel()
    Dim templateExists As Boolean
    Set m_xlApp = CreateObject("Excel.Application")
    templateExists = FileExists(m_templatesPath & getTemplateNameFromQueryName(m_query) & ".xlt")
    If templateExists = True Then
        Set m_wbk = m_xlApp.Workbooks.Open(m_templatesPath & getTemplateNameFromQueryName(m_query) & ".xlt")
        m_xlApp.Visible = True
        m_canWork = True
    Else
        Dim answer As String
        answer = MsgBox("Template " & m_query & ".xlt does not exists. Should I use the default template?", vbYesNo)
        If answer = vbNo Then
            m_canWork = False
            Exit Sub
        Else
            Dim defaultTemplateExists As Boolean
            defaultTemplateExists = FileExists(m_templatesPath & m_defaultTemplate)
                If defaultTemplateExists = True Then
                Set m_wbk = m_xlApp.Workbooks.Open(m_templatesPath & m_defaultTemplate)
                m_xlApp.Visible = True
                m_canWork = True
            Else
                m_canWork = False
                MsgBox "Default template doesn't exist in location: " & m_templatesPath & m_defaultTemplate
            End If
        End If
    End If
End Sub

Private Sub doWork()
    Dim row As Integer
    Dim col As Integer
    Dim sht As Excel.Worksheet
    Set sht = m_wbk.Worksheets("Data")
    Dim rs As Recordset
    Set rs = m_db.OpenRecordset(m_query)
    row = TOPOFFSET
    Do Until rs.EOF
        row = row + 1
        For col = 1 To rs.Fields.Count
            sht.Cells(row, col) = rs(col - 1)
        Next col
        rs.MoveNext
    Loop
    rs.Close
End Sub

 

[PHV]

Hopefully the parameters are taken from a form.
Replace this:
Dim rs As Recordset
Set rs = m_db.OpenRecordset(m_query)
with this:
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset
Set qdf = m_db.QueryDefs(m_query)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886