Hi All
I have searched online in a number of forums and although I can see how to pass a parameter to a query
a) either you a form value
b) to a SELECT query
non of the above are applicable in my case. I have 10 append queries that dump their reults into a temp table TEMP2 where I extract the values and email to a system inbox.
The append queries all have parameters ie FISCAL YEAR_TY (this year) and FISCAL_YEAR_LY that the user needs to input. At the moment, when the query runs these paremeters popup, the user enters the year and query runs an append to TEMP2. I am trying to pass these parameters directly from VBA as they all remain constant for each query. i.e. FISCAL_YEAR_TY and FISCAL_YR_LY are the same for all queries.
I have tried as below
As can be seen as was also trying to open a recordset to call these queries hoping the querydef parameter would pass but being that these are append queries not select when the following is run
The code bugs out with invalid object.
Aside from having the user input these parameters I know there has to be a way around this. Any help is appreciated
Thanks
I have searched online in a number of forums and although I can see how to pass a parameter to a query
a) either you a form value
b) to a SELECT query
non of the above are applicable in my case. I have 10 append queries that dump their reults into a temp table TEMP2 where I extract the values and email to a system inbox.
The append queries all have parameters ie FISCAL YEAR_TY (this year) and FISCAL_YEAR_LY that the user needs to input. At the moment, when the query runs these paremeters popup, the user enters the year and query runs an append to TEMP2. I am trying to pass these parameters directly from VBA as they all remain constant for each query. i.e. FISCAL_YEAR_TY and FISCAL_YR_LY are the same for all queries.
I have tried as below
Code:
Function export_DSPH()
DoCmd.SetWarnings False
'DoCmd.SetWarnings True
DoCmd.RunSQL "delete * from temp2"
Dim db As Database
Dim qry As QueryDef
Dim rst As dao.Recordset
Set db = CurrentDb
For Each qry In db.QueryDefs
Select Case qry.Type
Case dbQAppend
If qry.Name Like "SPH_*" Then
qry.Parameters(0) = 201104
qry.Parameters(1) = 201204
DoCmd.OpenQuery qry.Name
'Set rst = qry.OpenRecordset()
Else
End If
Case Else
End Select
Next qry
End Function
As can be seen as was also trying to open a recordset to call these queries hoping the querydef parameter would pass but being that these are append queries not select when the following is run
Code:
set rst = currentdb.openrecordset(qry.name)
The code bugs out with invalid object.
Aside from having the user input these parameters I know there has to be a way around this. Any help is appreciated
Thanks