Non-trivial problem with Parameter dialog

[TinkerMan]

Hi all

I have a summarised sub-form, based on dates, so I have to use a crosstab query. Because of the varying column names (dates), I'm using VBA to create the query. In addition the underlying query has a parameter that needs to be set. I have the code in a function (below), called from the On_Current as well as the date-combo's after_update (that defines which week to display). This all works fine.

The function:

Public Function populateForm()
  Dim qd As DAO.QueryDef
  Dim rst As DAO.Recordset
  Set dbs = CurrentDb
  Dim sql As String
  sql = getOrderQuery(Me.DatePicker.value, Me.Customer_ID)
  Set qd = dbs.CreateQueryDef("", sql)
  qd.Parameters![start_date] = Me.DatePicker.value
  Set rst = qd.OpenRecordset
  Set Me.orders_subform.Form.Recordset = rst
End Function

My problem started when I added filter buttons. Every time I try to use filters the parameter dialog appears asking for "start_date".

Is there a different way of setting parameters that will cause it to be remembered?

Is there a way to intercept this "refresh/requery" and run my populateForm function instead?

Thanks

 

[boblarson]

I'm not a DAO person (more ADO), but I would assume that the reason it isn't holding a value is that you are using DIM to define the QueryDef and a DIM will always reinitialize a variable when the code is run again. If you want it to remain, you should be able to use STATIC or set it in a public sub in a module (not a form module).

 

[TinkerMan]

Workaround

Thanks boblarson for your insight, but I'm afraid it did not solve my problem. I moved the code to a public module and used static, but as soon as the "Me.FilterOn = True" is executed, the dialog appears.

As many times before (with Access and others) a workaround got rid of the dialog. By renaming the parameter from [start_date] to [Forms]![Orders]![DatePicker], Access obviously knows where to get that value.