Pass parameter to Append Queries (1 Viewer)

[tt1611]

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

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

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

 

[pbaldy]

Two thoughts. First is trying this instead of the OpenQuery line:

qry.Execute

Second is dumping the parameters into 2 form controls and have all the queries get them from there. That would let you run them manually or programmatically.

 

[tt1611]

Wow Pbaldy
qry.execute

Only took me 6 hours. I have chosen to run this along with other code directly in a function so chose the former option of qry.execute as supposed to the form control

Truly awesome. Thanks for your help

 

[pbaldy]

Happy to help!

 

[hillnm]

Paul - I have an append query with parameters. My parameters are coming from a form. One of these parameters is a list box. I have taken the list box selected items and created a string to populate a text box on the form so that the text box can be read as a parameter in the query. However, that is not working. I have declared the parameter on the query.

Any suggestions?

Thanks,
Natalie

 

[pbaldy]

I generally do this type of thing:

http://www.baldyweb.com/multiselect.htm

you can do this if you need it in the query (like for exporting):

http://access.mvps.org/access/forms/frm0007.htm

You can also manipulate the SQL of the query with a QueryDef.

 

[hillnm]

Paul - Thanks for the suggestions. This append query is one of two append queries which appends data to a staging table for reporting. This query has at least 3 parameters which are being feed from the form. For this particular parameter, I set the value of a text box to a string using vba. I can set the value and all works fine but I still can't get the append query to "read" the string which is in the text box for that parameter.

Right now this code is on the OnClick event of a button.

Here is the code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_ch_trans_detail_with_tracking_violation_codes_6a")

For Each varItem In Me!lst_violations.ItemsSelected
strCriteria = strCriteria & ",""" & Me!lst_violations.ItemData(varItem) & """"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "Please select one or more audit reason", vbExclamation, "Make a seleciton"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

Me.txt_viol_list.Value = strCriteria

Set db = Nothing

Any thoughts or suggestions?

Thanks,
Natalie

 

[pbaldy]

Well, the second link addressed it. You're opening a QueryDef but not doing anything with it. You could replace the entire SQL of the query with that.