auto query parameters

[s0rtd]

i wonder if someone can help with this little piece of code??
heres the code so far:

WeekStart = Date 
WeekEnd = WeekStart + 4

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb 'opens a new DAO database connection
    
    Set qd = dbs.QueryDefs!qry_Time_Andy 'sets query definitions, parameters and such
        qd.Parameters("[Forms]![frm_weekstart]![txtWeekStart]") = Trim(Str(WeekStart))
        qd.Parameters("[Forms]![frm_weekstart]![txtWeekend]") = Trim(Str(WeekEnd))

my trouble is with these lines:

 qd.Parameters("[Forms]![frm_weekstart]![txtWeekStart]") = Trim(Str(WeekStart))

i used to these parameters being dragged from a text box on a form like so:

qd.Parameters("[Forms]![frm_weekstart]![txtWeekStart]") = [Forms]![frm_weekstart]![txtWeekStart]

, but have since decided that i would rather they were automatically generated. (something like the code i have at the start of this post)

i have tried it without the trim and string conversion too, but still it asks me to enter the parameters.

even if i hard-code it like so:

 qd.Parameters("[Forms]![frm_weekstart]![txtWeekStart]") = "17/09/2003"

i still get that little box popping up asking me to 'enter parameter values'

i have had a look here: http://www.accessvba.com/showthread.php?s=&threadid=2640

but i still cant get it to work

is it possible to do this ??

cheers
Jurgen

 

[RichMorrison]

Jurgen,

I do not have a direct answer to your question. I suggest 2 alternative approaches:

1) Make and save a query. Put functions to get values in "criteria" cell of the query. Make a module of "property" functions to set and get values from user-entered values from the form. Do this in code behind the form.

2) If you are constructing the record source for a form, build a SQL string with keywords and user-entered values from the form. Assign the full SQL string to the RecordSource property of the form. Do this in code behind the form.

I use both of these techniques and find them easier than building querydefs.

HTH,
RichM