Querydef Hassles

[CJBIRKIN]

Hello

I've put this statement into the mousedown event of a combobox
Their could be more than one problem but i can't get passed the fact that it tells me the queries already exist.

If this is the case should the querydefs be initalized when the database is loaded and then the rowsource of the combobox be set to the query
i.e
Combo60.RowSource = "QRY_AVGWAIT2"

ideally i want the user to be able to select the period for the datediff part of the query (which i know how to do) but that's not going to work if i can't update the query each time.

Is there a way of clearing the queries each time?

Any help would be nice

Chris


Dim DB As DAO.Database
Dim QDEF As DAO.QueryDef
Dim QDEF2 As DAO.QueryDef
Dim StrSql As String
Dim StrSql2 As String


StrSql = "SELECT Min(TBL_ADHOC_REQUESTS.[DATE RECIEVED]) AS [MinOfDATE RECIEVED], Max(TBL_ADHOC_REQUESTS.[DATE RECIEVED]) AS [MaxOfDATE RECIEVED], Count(TBL_ADHOC_REQUESTS.[REQUEST ID]) AS [CountOfREQUEST ID] FROM TBL_ADHOC_REQUESTS;"
Set DB = CurrentDb
Set QDEF = DB.CreateQueryDef("QRY_AVGWAIT", StrSql)

StrSql2 = "SELECT DateDiff('w',[MinOfDATE RECIEVED],[MaxOfDATE RECIEVED]) AS numofx, [CountOfREQUEST ID]/[numofx] AS [AVG REQUESTS PER X] FROM QRY_AVGWAIT;"

Set QDEF2 = DB.CreateQueryDef("QRY_AVGWAIT2", StrSql2)

Combo60.RowSource = "QRY_AVGWAIT2"

 

[CJBIRKIN]

Hello

Answer to my own question.

DoCmd.DeleteObject acQuery, "QRY_AVGWAIT"
DoCmd.DeleteObject acQuery, "QRY_AVGWAIT2"

Chris