Modify stored query in Access 2007

[cowspotter]

I need some help with a query that I'm trying to run on an Access 2007 database. I have a query that returns a list of records (date, user, index number, platform, etc) and that query works fine. My issue is that I want the user to be able to use a form to select either a specific platform or all platforms.
The query is stored already as Last_Update. Here is the SQL:

SELECT Cases.CaseNumber, Cases.CaseSeverity, Cases.CaseEscalation, Cases.CaseOpenDate, Max(CasesAuditRecords.CaseAuditRecordTimeStamp) AS [Last Updated]
FROM Cases INNER JOIN CasesAuditRecords ON Cases.CaseNumber = CasesAuditRecords.CaseNumber
WHERE (((Cases.CaseStatus)="Open"))
GROUP BY Cases.CaseNumber, Cases.CaseSeverity, Cases.CaseEscalation, Cases.CaseOpenDate;

As you can see, there is no platform reference. In its current form it returns data for all platforms. I've been trying to figure out a way to either modify this query in VBA or to just delete it and replace the query with the platform needed specified in the SQL.

I found code (i think through google on this site) that is supposed to modify a query but I can't get it to run and I have a feeling its because I have 2007. I know how to delete the query, but I can't create a new query via VBA.

The reason I'm going about it this way is because my users use Access runtime and they want to be able to sort the data so a report wouldn't work. I need the query to execute and display in the normal query results viewer (datasheet view). Any ideas? Sorry if I'm missing some important details here. Just ask and I'll try to explain something further.

 

[pbaldy]

Does this work for you?

http://www.mvps.org/access/queries/qry0001.htm

 

[cowspotter]

That still doesn't address my problem of integrating the input from the form into the stored query

 

[pbaldy]

Does this work?

WHERE (((Cases.CaseStatus)="Open")) AND ([Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null)

 

[CyberLynx]

One way you can do this is to Create the Query on the fly as you need it, add it to the QeryDefs collection, Run the Query, then Delete the Query from the collection when the Query Window is closed. Here'w how:

 [COLOR="DarkGreen"]  'Microsoft DAO3.x Object Library required!
   
   'Declare Variables[/COLOR]
   Dim qryDef As QueryDef
   Dim StrgSQL As String

[COLOR="DarkGreen"]   'Fill and Set Variables
   'The StrgSQL string variable is to hold your desired 
   'SELECT Query, whatever it may be. Here is a sample:[/COLOR]
   StrgSQL = "SELECT FirstName FROM [Mailing List];"
   Set qryDef = CurrentDb.CreateQueryDef("MyTmpQuery", StrgSQL)

   [COLOR="DarkGreen"]'Refresh the QueryDefs collection[/COLOR]
   CurrentDb.QueryDefs.Refresh
   
   [COLOR="DarkGreen"]'Run the Query[/COLOR]
   DoCmd.OpenQuery "MyTmpQuery"
   
   [COLOR="DarkGreen"]'Carry out a continuous loop allowing event
   'processing Until the Query is closed. Here
   'we continuously check to see if the Query 
   'is actually open.[/COLOR]
   Do Until (SysCmd(SYSCMD_GETOBJECTSTATE, acQuery, "MyTmpQuery") <> 0) = False
     DoEvents
   Loop
   
   [COLOR="DarkGreen"]'Delete the Query when closed[/COLOR]
   DoCmd.DeleteObject acQuery, "MyTmpQuery"

   [COLOR="DarkGreen"]'Free Memory[/COLOR]
   Set qryDef = Nothing

.

 

[cowspotter]

Thanks CyberLynx! That did it. I couldn't add the reference to DAO3.x because it appears you can't have Office 12.0 Object Library as well as DAO3.x Object Library. Does the 12.0 replace DAO? Either way, I didn't need it. Worked fine.

The only thing I changed was because we have multiple users that could try and run this report at the same time, I appended their username to the end of the query name so there wouldn't be name conflicts.

 

[pbaldy]

I must have misunderstood the goal, because I see no reason to create/delete the query when the WHERE clause I posted should accomplish this goal:

"I want the user to be able to use a form to select either a specific platform or all platforms"

 

[datAdrenaline]

There is no need to reference the DAO 3.x library if you are referencing the Access Database Engine objects (which is DAO for Access 2007), which is referenced by default in A2007 ...

... I *think* thats the name ... I don't have A2007 in front of me at the moment ... so it's named something like that ...

 

[datAdrenaline]

Cowspotter ...

>> The only thing I changed was because we have multiple users that could try and run this report at the same time, I appended their username to the end of the query name so there wouldn't be name conflicts. <<

Your database should be "Split" ... EACH user should have their own copy of the Front End ... you are WAY more likely to create database corruption with the technique you imply!

...

Also, when you say "Platform Specific" ... what do you mean? ... And I am with Paul on this ... if you use Linked tables, the method Paul suggested should work fine.

 

[cowspotter]

This is a setup that we are using until we can get a web front end built. I know its not ideal but its not permanent. The tables aren't linked, they exist locally in the database. By "Platform" I mean Windows, Unix, Network, etc. Its just a classification for each case we open.

I didn't test Pauls query. I saw both solutions at the same time when I logged on this morning and chose to go with the VBA solution.

 

[cowspotter]

Alright, I ran into a new problem using that previous method (VBA code) so I went back and used the query code. That worked. Unless I'm missing something, since I'm using a radio button to select the platform (and that has option values associated with it), I am using a hidden text control where the platform name selected is stored and that is the control referenced in the query. Everything seems to be working this way.

 

[pbaldy]

I think that's an appropriate way to use the radio buttons. Glad you have it sorted out.

 

[CyberLynx]

The comment towards the DAO 3.x Library is only indicated in code for those who wish to use the previously presented code in versions of MS-Access where DAO needs to be Referenced.

.

 

[jxaxmxixn]

Here's a modified version of CyberLynx code.
I modified it because my reports were not showing correct data (showing data from the previous query). This will create a unique query every time.

What you want to do here is create a report that will show every possible record you want to be able to show (i mean with no WHERE filter what so ever in the reports RecordSource), and this code will take care of the rest for you (provided you modify it correctly).

[COLOR=Green]'create a UNIQUE temporary query name[/COLOR]
Dim TEMPqueryNAME As String
TEMPqueryNAME = "MyTmpQuery_"
[COLOR=Green]
[/COLOR] [COLOR=Green]'first we'll look for and delete any old queries we have made[/COLOR]
Dim QryDefItem As QueryDef
 For Each QryDefItem In CurrentDb.QueryDefs
   If InStr(1, QryDefItem.Name, TEMPqueryNAME, vbTextCompare) > 0 Then 
       DoCmd.DeleteObject acQuery, QryDefItem.Name
   End If
 Next QryDefItem
[COLOR=Green]
'now we will make the query name double unique (atleast to the second)[/COLOR]
TEMPqueryNAME = TEMPqueryNAME & Format(Now(), "yyyymmdd-hhnnss")
[COLOR=Green]
'Set up our SQL using your reports record source (excluding the ;)[/COLOR]
SQL_OUT = Replace(YOUR_REPORTS_NAME.Report.RecordSource, ";", "") 
SQL_OUT = SQL_OUT & " WHERE " & PUT_YOUR_WHERE_FILTERS_HERE
SQL_OUT = SQL_OUT & ";"
[COLOR=Green]
'for verification and testing of your SQL. print to the debug window[/COLOR]
Debug.Print SQL_OUT

[COLOR=Green]' thanks to CyberLynx on http://www.access-programmers.co.uk
' but i will modify it to my needs of course[/COLOR]

  [COLOR=Green] 'Declare Variables[/COLOR]
   Dim qryDef As QueryDef

    [COLOR=Green]'create the query[/COLOR]
    Set qryDef = CurrentDb.CreateQueryDef(TEMPqueryNAME, SQL_OUT)

   [COLOR=Green] 'Refresh the QueryDefs collection[/COLOR]
    CurrentDb.QueryDefs.Refresh
 [COLOR=Green]
    'Run the Query[/COLOR]
     DoCmd.OpenReport "YOUR_REPORTS_NAME", acViewReport, TEMPqueryNAME
   
      [COLOR=Green] 'Free Memory[/COLOR]
       Set qryDef = Nothing

 

[jxaxmxixn]

I made a modification to your code. Just letting you know so you can check it out for approval...