Originally posted by mmlatt
I don't know how to edit the query with VB.
That query is a parameter query, so i have to change the value of parameter everytime.
So I delete the existing query and create new query like this ...
Function CreateListByStrQueryDef(strStrNo As String)
Dim strStructureNumber As String
Dim strSQL As String
strStructureNumber = strStrNo
strSQL = "SELECT tblMainD.* FROM tblMainD WHERE (((tblMainD.DNo) Like '" & strStructureNumber & "'));"
DoCmd.DeleteObject acQuery, "qByStrNo"
Set qdfTemp = CurrentDb.CreateQueryDef("qByStrNo", strSQL)
If anybody have the easyest way to handel a parameter query or to edit the SQL statement via VB code, let me guide.
thanks in advance
It seems to me that you are trying something that can be done in a more simple way:
1) You can declare a global variable, which you use as the variable for the querie. Prior to calling the query, you just fill the global variable.
2) You can build the qeury in VB-code, thus not using the original qury at all.
3) (this one I found somewhere on the web, but never tried it you can give a variable to the query:
Dim dbs As Database, rst As Recordset, qdfParam As QueryDef
Set dbs = DBEngine(0)(0)
Set qdfParam = dbs.QueryDefs("qryFilterStaffByTitle")
qdfParam![Please enter the required job title here] = "Manager"
Set rst = qdfParam.OpenRecordset()