Parameter QueryDef as Source in strSQL in vba

Margarita · Nov 30, 2011

Hello,
I am working in Access 2003. I have a parameter query called
MonthlySalary_FinalQuery_TASK_TOTALS
I have set this query and its parameters in vba as follows:

PHP:

'set main MonthlySal query
Set qdf_monthsal = CurrentDb.QueryDefs!MonthlySalary_FinalQuery_TASK_TOTALS
'parameters:
qdf_monthsal.Parameters![start date] = Forms!MonthlyReportForm.Reportstartdate
qdf_monthsal.Parameters![end date] = Forms!MonthlyReportForm.Reportstartdate
qdf_monthsal.Parameters![select FTE month] = Forms!MonthlyReportForm.MonthFTE

Now, I need to query this query based on conditions in a loop, which loops throgh another recordset of subtasks and sets the name of the current subtask to variable currentsubtask. I am trying to accomplish this 're-querying' of the original query by setting up another recordset based on a sql string as follows:

PHP:

Dim SQL_TOTALS_reg_subtask
SQL_TOTALS_reg_subtask = "Select * from " & qdf_monthsal & " where ServiceSubcat like '" & currenttask & "';"
Dim rs_subtasks As DAO.Recordset
Set rs_subtasks = CurrentDb.OpenRecordset(SQL_subtasks, dbOpenSnapshot)

When I try to compile, I get a 'Type mismatch' error. I realize that it is probably wrong to use the queryDef which defined the original query as a query in itself, but I am not sure how else to re-query the queryDef. Should I just put the original query in vba as a sql string and apply the currenttask condition right there? I was trying to avoid that since
MonthlySalary_FinalQuery_TASK_TOTALS is a really long sql statement and it would be a pain in the neck to format it with all the "" and &'s in the vba window.
Can anyone suggest an easy way to combine a querydef and sql string in vba? Any help would be greatly appreciated!

MarkK · Nov 30, 2011

I would probably try to do that all in one parameter query, because you can sub-query, like this...

Code:

PARAMETERS [prmFTE] Long, [prmDateStart] Date;
SELECT t1.* FROM 
  (
  SELECT * 
  FROM SomeTable
  WHERE FTEMonth = [prmFTE]
  ) As t1
WHERE t1.DateStart < [prmDateStart]

... which seems to be what you are trying to do.
Then you can instantiate that QueryDef once and push in new parameter values whenever you want, so something like ...

Code:

  dim dbs as dao.recordset
  set dbs = currentdb

  dim qdf as dao.querydef
  set qdf = dbs.querydefs("YourQuery")

  dim i as integer
  dim rst as dao.recordset
  for i = 1 to 12
    qdf.parameters("prmFTE") = i
    qdf.parameters("prmStartDate") = somedate

    set rst = qdf.openrecordset
    with rst
[COLOR="Green"]        'do something with this recordset[/COLOR]
      .close
    end with
  next

Cheers,
Mark

Margarita · Nov 30, 2011

Hi Mark, thank you for replying! But I don't quite understand how the variable 'somedate' will take on the value of the control on my form (this whole code is inside a on-click event of a button in a form), which is what I want- I want the parameters of the original query to take on the value of the controls startdate, enddate, FTEmonth on the form. Then I want to limit that query with the parameters already set with additional criteria, which is what I'm trying to do in the sql string. I'm sorry if I'm being too dense and not understanding your solution. Please explain.
Thank you!

MarkK · Nov 30, 2011

You just replace 'somedate' with the value of your control, so ...

Code:

qdf.parameters("prmStartDate") = Me.MyDateControl

Does that answer your question?
Or double-checking your orig post, maybe ....

Code:

qdf.parameters("prmStartDate") = Forms!MonthlyReportForm.Reportstartdate

Make any sense?
Cheers,

Margarita · Dec 1, 2011

Ah! That does make sense! Thank you! I will definitely try your solution, which seems much more elegant than the interim one I came up with:
I created a copy of the original stores query and put the two extra parameters there. Then, I defined the querydef as before, simply adding on the two extra parameter definitions. I will certainly try out your solution. I will post an update on my progress with the two solutions for anyone looking to this thread who has had a similar problem. Thank you, Mark!

Parameter QueryDef as Source in strSQL in vba

Margarita

Registered User.

MarkK

bit cruncher

Margarita

Registered User.

MarkK

bit cruncher

Margarita

Registered User.

Similar threads

Users who are viewing this thread