Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

How can a Pass a Parameter (like a month and year mm-yyyy) to multiple queries in a macro?

I have multiple queries that I run via a Switchboard button for monhtly reporting. I would like to be able to enter the date mm-yyyy only once and have this passed to each query that needs it as that query runs.

I am using the following code to run the queries (this is an example of one)

DoCmd.OpenQuery "KO_QN_EFR_A_Product_Sum_Monthly", acViewNormal, acEdit

Can someone show me/ tell me how to do this? Thank you.
0
Rex85
Asked:
Rex85
  • 7
  • 3
  • 2
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can have your queries refer to a hidden control on the Switchboard form, then enter that value in the hidden textbox.

Or you can run your queries directly like this:

Currentdb.Execute "Your SQL Here WHERE SomeValue = " & Me.Somevalue

"Your SQL Here" would be the SQL needed for your queries ... you can get that by opening your query in Design view, and then click View - SQL.
0
 
Helen FeddemaCommented:
You could also save the formatted date to a custom database property, and then retrieve it for use in the queries.  This has the advantage of not requiring a form to be open when the queries are run.  Here is some syntax for working with custom database properties:
Option Compare Database
Option Explicit

Private dbs As DAO.Database
Private lngDataType As Long
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private varPropertyValue As Variant


Public Sub SetProperty(strName As String, lngType As Long, _
   varValue As Variant)
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler

   'Attempt to set the specified property
   Set dbs = CurrentDb
   Set prps = dbs.Properties
   prps(strName) = varValue

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
    If Err.Number = 3270 Then
      'The property was not found; create it
      Set prp = dbs.CreateProperty(Name:=strName, _
         Type:=lngType, Value:=varValue)
      dbs.Properties.Append prp
      Resume Next
   Else
   MsgBox "Error No: " & Err.Number _
      & " in SetProperty procedure; " _
      & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Public Function GetProperty(strName As String, strDefault As String) _
   As Variant
'Created by Helen Feddema 2-Oct-2006
'Modified by Helen Feddema 2-Oct-2006
'Called from various procedures

On Error GoTo ErrorHandler
   
   'Attempt to get the value of the specified property
   Set dbs = CurrentDb
   GetProperty = dbs.Properties(strName).Value

ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3270 Then
      'The property was not found; use default value
      GetProperty = strDefault
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in GetProperty procedure; " _
         & "Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Function

Public Function ListCustomProps()
'Created by Helen Feddema 3-Oct-2006
'Modified by Helen Feddema 3-Oct-2006
'Lists DB properties created in code (as well as built-in properties)

On Error Resume Next
   
   Set dbs = CurrentDb
   Debug.Print "Database properties:"
   
   For Each prp In dbs.Properties
      Debug.Print vbTab & prp.Name & ": " & prp.Value
   Next prp

End Function

==================================
Usage examples:

Private dbs As DAO.Database
Private lngDataType As Long
Private prp As DAO.Property
Private prps As DAO.Properties
Private strPropertyName As String
Private strValue as String
Private varPropertyValue As Variant

Date
====
    strPropertyName = "PropName"
    strValue = CStr(dteStart)
    lngDataType = dbDate
    Call SetProperty(strPropertyName, lngDataType, _
       strValue)

   GetStartDate = CDate(GetProperty("PropName", ""))

Text
====
   varPropertyValue = CStr(cbo.Value)
   strPropertyName = "PropName"
   lngDataType = dbText
   Call SetProperty(strPropertyName, lngDataType, _
      varPropertyValue )

   strDocsPath = GetProperty("PropName", "")

Long
====
   strPropertyName = "PropName"
   varPropertyValue = CStr(lngID)
   lngDataType = dbLong
   Call SetProperty(strPropertyName, lngDataType, _
     varPropertyValue )

   GetID = CLng(GetProperty("PropName", ""))

Integer
=======
   strPropertyName = "PropName"
   varPropertyValue = CStr(lngID)
   lngDataType = dbInteger
   Call SetProperty(strPropertyName, lngDataType, _
     varPropertyValue )

   GetID = CInt(GetProperty("PropName", ""))

Saving to a custom property from a control's AfterUpdate event
==============================================================
Private Sub txtDate_AfterUpdate()
'Created by Helen Feddema 2-Sep-2009
'Last modified 2-Sep-2009

On Error GoTo ErrorHandler
   
   If IsDate(Me![txtDate].Value) Then
      dteSingle = CDate(Me![txtDate].Value)
      strPropertyName = "SingleDate"
      Call SetProperty(strName:=strPropertyName, _
         lngType:=dbDate, varValue:=dteSingle)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
GRayLCommented:
Rather than open the queries in a recordset view, why not open a separate form (datasheet view) and control the specific records with a Where clause in the DoCmd.OpenForm method.  If the field content for each query is different, we have  a problem.  
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rex85Author Commented:
thank tou for your suggestions. I'm not sure I was clear. essentially, I have a series of queries that prompt me for the month and year as below. I was wondering how I could enter that once, and have the subsequent queries use the information already  entered, so that I only had to do it once.
Parameter-Prompt.jpg
0
 
GRayLCommented:
Try this.  It should work for stored queries.

Public Function RunQueries(yyyymm As String)

Dim i As Integer, arrQryNames As Variant, db As DAO.Database, origSQL As String, newSQL As String
Set db = CurrentDb
arrQryNames = Array("qryName1", "qryName2", "qryName3") 'as many named queries as you like

For i = 0 To UBound(arrQryNames)
  origSQL = db.QueryDefs(arrQryNames(i)).SQL
  newSQL = Replace(origSQL, "[yyyy-mm]", """" & yyyymm & """")
  db.QueryDefs(arrQryNames(i)).SQL = newSQL
  DoCmd.RunSQL arrQryNames(i)
  db.QueryDefs(arrQryNames(i)).SQL = origSQL
  db.QueryDefs.Refresh
Next i

Set db = Nothing

End Function

Call the function from the Immediate Pane - Alt+F11 and type

RunQueries("2009-11")

This would run your set of queries for '2009-11'
0
 
Helen FeddemaCommented:
If the entered year-month combination is used as a query criterion, you can use my code (posted earlier) to save this value to a custom db property, and then use it as a criterion in the query -- just put GetProperty("PropName", "") in the Criteria line of the appropriate field in the query, and remove the query parameter, as it is no longer needed.
0
 
GRayLCommented:
Can you tell me what kind of queries are in the lot?
0
 
Rex85Author Commented:
Here's an Example of one. It creates a two column table for use in feeding a Pareto chart.
SELECT (KO_QN_Data.Field20)+' ('+[KO_QN_Data.Code group]+')' AS Defect, Sum(CLng(TransformTextFields([KO_QN_Data.DefectQty (ext)]))) AS QTY INTO KO_QN_EFR_B_Product_Pareto_Monthly_Table
FROM KO_QN_Data
WHERE (((Format([Created On],"yyyy-mm"))=[yyyy-mm]) AND ((KO_QN_Data.[Short text for code])="Kimball Office Furniture") AND ((GetBucket([Code group]))="Product") AND ((GetNOFGSA([Product Hierarchy]))="Not NOF GSA"))
GROUP BY (KO_QN_Data.Field20)+' ('+[KO_QN_Data.Code group]+')'
ORDER BY 2 DESC , 1;

Open in new window

0
 
GRayLCommented:
In addition, my code assumes that the parameter in each query is written:  [yyyy-mm] - ie. bracketed.  If not bracketed, remove the brackets from the Replace() line.
0
 
GRayLCommented:
Then my code should work well with that.  You just have to enter the query names in the arrQryNames line in place of qryName1, qryName2, etc.
0
 
GRayLCommented:
I see an error:

DoCmd.RunSQL arrQryNames(i)

should be:

DoCmd.OpenQuery arrQryNames(i)
0
 
Rex85Author Commented:
Thank you both. I went a slightly different route, but both of your solutions helped me arrive there. I really do appreciate it. Thank you.

Rex
0
 
GRayLCommented:
Thanks, glad to help.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now