[AccessD] It's About Time I Learned This

Does this help, Rocky?  Can you adapt any of it to your uses?  This is
really old, as you can tell from the date, but it allows you to pass the
parameters to a named query through the function.

Charlotte Foust

Public Function RunParamQuery(ByVal strQryNm As String, _
                      ParamArray varParamValues()) As Boolean
  'Created by Charlotte Foust
  '12/9/99
  On Error GoTo Proc_err
  Dim dbs As Database
  Dim wsp As Workspace
  Dim qdf As QueryDef
  Dim prm As Parameter
  Dim blnOK As Boolean
  Dim varPrmType As Variant
  Dim strName As String
  Dim blnPrmVal As Boolean
  Dim blnPrmSet As Boolean
  Dim intLoop As Integer
  Dim intNumParams As Integer
  Dim intQDFType As Integer
  Dim varParamValue As Variant
  
  Const DUPLICATE_KEY_OR_INDEX = 3022
  
  blnOK = True
  Set dbs = CurrentDb()
  Set qdf = dbs.QueryDefs(strQryNm)
  intNumParams = qdf.Parameters.Count
  ' See how many parameters there are to pass
  If intNumParams = UBound(varParamValues) + 1 Then
      blnPrmVal = True
  Else
    blnOK = False
  End If
  If Not blnPrmVal Then
   ' Loop through the parameters and
   ' prompt user for values except for
   ' any passed in the call
    If intNumParams = 0 Then
      intNumParams = 1
    End If
    For intLoop = 0 To intNumParams - 1
      blnPrmSet = False
      Set prm = qdf.Parameters(intLoop)
      strName = prm.Name
      varPrmType = prm.Type
      On Error Resume Next
      prm = varParamValues(intLoop)
      If Err = 0 Then
        blnPrmSet = True
      End If
      On Error GoTo Proc_err
      If Not blnPrmSet Then
          varParamValue = InputBox(strName, "Enter value")
          Select Case varPrmType
            Case vbLong
              varParamValue = CLng(varParamValue)
            Case vbInteger
              varParamValue = CInt(varParamValue)
            Case vbDouble
              varParamValue = CDbl(varParamValue)
            Case vbDate
              varParamValue = CDate(varParamValue)
            Case Else
              varParamValue = varParamValue
          End Select
        prm = varParamValue
      End If
    Next intLoop
  End If

  If blnOK Then
   '<<any special handling goes here>>
   intQDFType = qdf.Type
    Select Case intQDFType
      Case dbQMakeTable, dbQAppend, dbQDelete
        Set wsp = DBEngine(0)
        If intQDFType = dbQMakeTable Then
        ElseIf intQDFType = dbQAppend Then
        ElseIf intQDFType = dbQDelete Then
        End If
        wsp.BeginTrans
          qdf.Execute dbFailOnError
        wsp.CommitTrans
    Debug.Print qdf.RecordsAffected
      Case Else
        If intQDFType = dbQSelect Then
        ElseIf intQDFType = dbQCrosstab Then
        ElseIf intQDFType = dbQSetOperation Then
        ElseIf intQDFType = dbQCompound Then
        End If
        DoCmd.OpenQuery strQryNm
    End Select
  End If

Proc_exit:
  On Error Resume Next
  Set qdf = Nothing
  Set dbs = Nothing
  Set wsp = Nothing
  RunParamQuery = blnOK
  Exit Function
Proc_err:
  Select Case Err.Number
    Case DUPLICATE_KEY_OR_INDEX
      'Ignore this one
    Case Else
      MsgBox "RunParamQuery error #" & Err & "--" & Err.Description
      blnOK = False
  End Select
  Resume Proc_exit
End Function 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, October 08, 2009 7:18 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] It's About Time I Learned This

Stuart:

I'm not sure that buys me anything because you've got the SQL in the
code and you're updating the stored query with that code, modified with
the desired parameter.  

What I've got is SQL statements that run to 15 or 20 lines.  I'm pretty
fast at the copy and paste and merging the parameters into the SQL.
Once the SQL is done, I can just set the RecordSource of the form or
report to that. 

But fiddling with those lines and lines of SQL is what I'm trying to get
around.

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Thursday, October 08, 2009 2:18 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] It's About Time I Learned This

This was covered in the thread "Access 2000 - Filtering SQL Server
records"
on 17 Sep 2009.  We were actaully talking about editing "pass through"
queries, but the prinicple is the same.

Here's what I posted then.

<quote>
To pass a parameter, you need to modify the querydef, so you need to do
something like this before opening the query:

Public Sub SetChequePKID(PKID As Long)
   Dim strSQL As String
   Dim qdf as DAO.QueryDef
   Dim rs as DAO.Recordset
   strSQL = "Select Cast(chequenum as int) from tblCheques where PKID" &
   PKID 
    Set qdf = CurrentDb.QueryDefs("qryGetCheckNumber") 
    qdf.SQL = strSQL 
    qdf.Close
End Sub
</quote>


On 8 Oct 2009 at 13:42, Rocky Smolin wrote:

> Dear List:
> 
> When I have a query that is the source of a form, report or recordset 
> that needs to have criteria of parameters, I always go to the SQL view

> of the query, copy out the code, paste it into the CBF, and add the 
> parameters there - values from a text box or combo box, etc.  Always 
> done
it that way.
> 
> But I think there's a way to do modify a stored query in code using 
> the querydef.  Pretty basic Access stuff, I think, but I've never 
> learned
it.
> Can someone point me to a tutorial or primer or help screen explaining
this?
> 
> MTIA
> 
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>  
>  
> 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


Stuart McLachlan

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com