Query the Query

[andy g]

Hi,

Could you please show me how to query the results of a query

I have some query code:
If lstDataSource.Value = "Main Database" Then
STRSQL = "UPDATE tblMailingList SET SelectForView=-1,
SelectForPreView=-1 Where " & gcriteria & ";"
DoCmd.RunSQL STRSQL
ElseIf lstDataSource.Value = "Last Search" Then

End If

where gcriteria = lstSelectFrom.Value & " LIKE '" & txtSearchParameter &
"*'"

This returns the results exactly as i need.

My question is, what do I need to place in the code for lstDataSource.Value
= "Last Search" which will filter the results of the previous query from the
main database

Any Ideas most welcome, Thanks

 

[Guest]

Since your first query does an update there is no "result set" stored, so you
would need to requery using the same criteria.
Two approaches:
1) in addition to the update query have a make-table query using the same
criteria - you could call the table something like tblLastQuery and keep
overwriting it so you save a copy of the last result set. Then your second
query would use this table instead of tblMailingList.
2) You could save the prior query criteria in a separate (string) variable
to re-use for the second query. Not sure if you want to repeat the update
but if that is your intent it would be similar to this:
If lstDataSource.Value = "Main Database" Then
STRSQL = "UPDATE tblMailingList SET SelectForView=-1,
SelectForPreView=-1 Where " & gcriteria & ";"
lastcriteria = gcriteria
DoCmd.RunSQL STRSQL
ElseIf lstDataSource.Value = "Last Search" Then
STRSQL = "UPDATE tblMailingList SET SelectForView=-1,
SelectForPreView=-1 Where " & gcriteria & " And " & lastcriteria & ";"
lastcriteria = gcriteria & " And " & lastcriteria
End If

 

[andy g]

Hi K,

The first approach would suit the best, any chance you could guide me in the
correct format as I have (obviously lol) never created one before.

Many Thanks

Andy