Assign Temporary Where to a query - don't modify old query 
Author Message
 Assign Temporary Where to a query - don't modify old query

I would like to add a where clause to a query, so that I can reuse the basic
query, depending on the form.  (where - Me!etc. doesn't seem to work...)

So far I have
Private Sub txtUnboundBBL_AfterUpdate()
   Dim  dbs As Database, rst As Recordset
    Dim wksmyworkspace As Workspace, qdfOld As QueryDef, qdfNew As QueryDef
    Set wksmyworkspace = DBEngine.Workspaces(0)
    Set dbs = wksmyworkspace.Databases(0)
     If IsNull(DLookup("[txtunboundBBL]", "tblClientBBL",
"[BBL]=[txtunboundBBL]")) Then
           stMessage = "You will be adding a new record"
     Else
         stMessage = "That BBL was found in the database - now getting data "
         Set qdfOld = dbs.QueryDefs("qryAppendAllTablesToTemp")
        ' I get error about "characters found after end of SQL, so, I think,
        ' I must get rid of the final ";"
        Set qdfNew = qdfOld
        ' this SHOULD strip off the final semi-colon
        qdfNew.SQL = Left(qdfOld.SQL, Len(qdfOld.SQL) - 1)
        qdfNew.SQL = qdfNew.SQL & "  where
[tblclientbbl].[BBL]=Me![txtunboundBBL];"
' I finally get up to this line
        qdfNew.Execute (DB_FAILONERROR)
And guess what?  It seems that the code is actually modifying the underlying
sql statement, because lo and behold the where is in there twice!  So now the
question becomes, can I create a temporary instance of the query's sql with the
where condition for this form, the whole point being (I think) that I will
possibly re-use this query with a different form and different where!
So, next I try creating a brand new query which will have a new name each time:

  stNewQuery = "qryMyTemporaryQuery" & "'" & Format(Now(),
"YYYY-MM-DD-HH-MM-SS") & "'"
   Set qdfNew = dbs.CreateQueryDef(stNewQuery)
    qdfNew.SQL = Left(qdfOld.SQL, Len(qdfOld.SQL) - 1)
    qdfNew.SQL = qdfNew.SQL & " where [tblclientbbl].[BBL]=Me![txtunboundBBL];"
but guess what: it still seems to be modifying qdfOld.  Do you have any ideas?



Thu, 26 Oct 2000 03:00:00 GMT  
 Assign Temporary Where to a query - don't modify old query

First I will point out errors in your code:

Quote:
>Private Sub txtUnboundBBL_AfterUpdate()
>   Dim  dbs As Database, rst As Recordset
>    Dim wksmyworkspace As Workspace, qdfOld As QueryDef, qdfNew As QueryDef
>    Set wksmyworkspace = DBEngine.Workspaces(0)
>    Set dbs = wksmyworkspace.Databases(0)

Since you don't seem to be using transactions, if you are using Access 95 or
97 you can replace the previous two lines with:
    Set dbs = CurrentDb

Quote:
>     If IsNull(DLookup("[txtunboundBBL]", "tblClientBBL",
>"[BBL]=[txtunboundBBL]")) Then

The value in txtunboundBBL will not be evaluated if it is within the quotes.
Try:
DLookup("[txtunboundBBL]", "tblClientBBL",
"[BBL]= " & [txtunboundBBL])
It is also unusual that your field is named identically to your control on
the form.  This can cause some confusion and should probably be avoided.

Quote:
>           stMessage = "You will be adding a new record"

I don't see what the point is of this so I will leave it alone.  You might
want to strip the extraneous stuff that doesn't have anything to do with
your question out of your code before posting next time.  Just a suggestion.

Quote:
>     Else
>         stMessage = "That BBL was found in the database - now getting data
"
>         Set qdfOld = dbs.QueryDefs("qryAppendAllTablesToTemp")
>        ' I get error about "characters found after end of SQL, so, I
think,
>        ' I must get rid of the final ";"
>        Set qdfNew = qdfOld

This statement does not actually make qdfNew a new copy of qdfOld.  It will
just set qdfNew to a pointer to the same QueryDef as qdfOld.  That is why
qdfOld is being changed.

Quote:
>        ' this SHOULD strip off the final semi-colon
>        qdfNew.SQL = Left(qdfOld.SQL, Len(qdfOld.SQL) - 1)
>        qdfNew.SQL = qdfNew.SQL & "  where
>[tblclientbbl].[BBL]=Me![txtunboundBBL];"

Again Me![txtunboundBBL will not be evaluated within the quotes

Quote:
>' I finally get up to this line
>        qdfNew.Execute (DB_FAILONERROR)
>And guess what?  It seems that the code is actually modifying the
underlying
>sql statement, because lo and behold the where is in there twice!  So now
the
>question becomes, can I create a temporary instance of the query's sql with
the
>where condition for this form, the whole point being (I think) that I will
>possibly re-use this query with a different form and different where!
>So, next I try creating a brand new query which will have a new name each
time:

>  stNewQuery = "qryMyTemporaryQuery" & "'" & Format(Now(),
>"YYYY-MM-DD-HH-MM-SS") & "'"
>   Set qdfNew = dbs.CreateQueryDef(stNewQuery)
>    qdfNew.SQL = Left(qdfOld.SQL, Len(qdfOld.SQL) - 1)
>    qdfNew.SQL = qdfNew.SQL & " where

[tblclientbbl].[BBL]=Me![txtunboundBBL];"
Again the expression within the quotes.

Quote:
>but guess what: it still seems to be modifying qdfOld.  Do you have any
ideas?

Although you can create queries in code, overall it looks like you are just
trying to to what a parameter query does, so I would suggest going through
the step-by-step example of how to create one in the Access 97 help file.
Look up parameter queries in the help file and choose the topic "Create a
parameter query that prompts for criteria each time it is run".  After you
do that you can run a parameter query in code like so:

Private Sub txtUnboundBBL_AfterUpdate()
    Dim dbs As Database
    Dim qdf As QueryDef

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryAppendAllTablesToTemp")
    qdf.Parameters("ThisBBL") = txtunboundBBL    'gets value from the text
box on the form, not the table field that I suggested you change
    qdf.Execute
End Sub

Alden



Fri, 27 Oct 2000 03:00:00 GMT  
 Assign Temporary Where to a query - don't modify old query

Dim TempSQL As String, QDF As QueryDef
Set QDF = CurrrentDb.QueryDefs("MyQuery")
TempSQL = QDF.SQL
QDF.Close ' Not sure if this is necessary
Set QDF = Nothing
' Modify the TempSQL string to the SQL string that you want.
' ...
' ...
' ...
' Execute the SQL
CurrentDb.Execute TempSQL.

I think some of the code needs some tweaks in order for it to work, but
that's the idea.

--
James Johnston


Quote:
>I would like to add a where clause to a query, so that I can reuse the
basic
>query, depending on the form.  (where - Me!etc. doesn't seem to work...)

>So far I have
>Private Sub txtUnboundBBL_AfterUpdate()
>   Dim  dbs As Database, rst As Recordset
>    Dim wksmyworkspace As Workspace, qdfOld As QueryDef, qdfNew As QueryDef
>    Set wksmyworkspace = DBEngine.Workspaces(0)
>    Set dbs = wksmyworkspace.Databases(0)
>     If IsNull(DLookup("[txtunboundBBL]", "tblClientBBL",
>"[BBL]=[txtunboundBBL]")) Then
>           stMessage = "You will be adding a new record"
>     Else
>         stMessage = "That BBL was found in the database - now getting data
"
>         Set qdfOld = dbs.QueryDefs("qryAppendAllTablesToTemp")
>        ' I get error about "characters found after end of SQL, so, I
think,
>        ' I must get rid of the final ";"
>        Set qdfNew = qdfOld
>        ' this SHOULD strip off the final semi-colon
>        qdfNew.SQL = Left(qdfOld.SQL, Len(qdfOld.SQL) - 1)
>        qdfNew.SQL = qdfNew.SQL & "  where
>[tblclientbbl].[BBL]=Me![txtunboundBBL];"
>' I finally get up to this line
>        qdfNew.Execute (DB_FAILONERROR)
>And guess what?  It seems that the code is actually modifying the
underlying
>sql statement, because lo and behold the where is in there twice!  So now
the
>question becomes, can I create a temporary instance of the query's sql with
the
>where condition for this form, the whole point being (I think) that I will
>possibly re-use this query with a different form and different where!
>So, next I try creating a brand new query which will have a new name each
time:

>  stNewQuery = "qryMyTemporaryQuery" & "'" & Format(Now(),
>"YYYY-MM-DD-HH-MM-SS") & "'"
>   Set qdfNew = dbs.CreateQueryDef(stNewQuery)
>    qdfNew.SQL = Left(qdfOld.SQL, Len(qdfOld.SQL) - 1)
>    qdfNew.SQL = qdfNew.SQL & " where

[tblclientbbl].[BBL]=Me![txtunboundBBL];"

Quote:
>but guess what: it still seems to be modifying qdfOld.  Do you have any
ideas?

begin 666 James Johnston.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DIO:&YS=&]N.TIA;65S#0I&

M;T!B<F%Z;W-P;W)T+F-C+G1X+G5S#0I%34%)3#M)3E1%4DY%5#IJ:F]H;G-T

%05)$#0H`
`
end
end


Sat, 28 Oct 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VBA code don't see some query

2. I don't get the customers I want to have (with my query)

3. Queries don't multitask

4. Don't see all my Access 8.0 Queries in CR6.0

5. Queries in Access don't work in ADO

6. Access 2000 parameter query values don't change

7. HELP: Parametrized union queries don't work with Jet 4.0

8. Selecting from a Parameterised query in a CreateQuerydef'd query

9. SQL query works, Crystal query doesn't

10. how can I build 2 queries in 1 query(VB SQL query)

11. Checking for Temporary Query...

12. Executing a Temporary SQL Pass through query

 

 
Powered by phpBB® Forum Software