Clearing contents of report queries after using

[mickie52]

I'm in the process of making a database that uses multiple user defined criteries to create reports. I have pulled those reports into one using subreports. I have several issues:

1. How do I clear the contents so they don't save when the user exits, and the next user gets ready to choose?

2. Not all of my subreports are updating when the main form is opened, and I'm unsure why that is.

 

[GinaWhipp]

Welcome to the Forum!

Not sure I undersatnd, are you letting Users create queries or just using a Form to filter by? Also, by your post it seems as if everyone is sharing one database. Is the database split with each User having their own Front end?

 

[mickie52]

It is 1 database, with a limited amount of users. the Form is used to add the parameter filters to fly queries. If that makes sense. I just need the query to revert to blank so that if a user doesn't need that particular report next time it will make that particular sub-report hidden. (which I did figure out)

 

[GinaWhipp]

Not unless they don't save the query, it cannot *revert back* any other way.

 

[mickie52]

So not saving isn't an option there is no prompt when the query gets closed. I tried setting the listbox default value as null, but that just gave me an error.

I have 11 listboxes with different variables depending on calendar criteria,
Day of Month
Quarter
Yearly
Seasonal
Last Week of the Month
Calendar Month etc. etc.

Each Listbox is set up with a command button like this:

Private Sub Command35_Click()
'Last Week of 4-5-4
Dim db As DAO.Database
    Set db = CurrentDb
 
Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs("Flex4")
 
Dim SDay As String, sFilter As String, sForm As String
Dim sSelect As String, sFrom As String, sWhere As String
Dim sState As String
sFilter = Forms![Packets]![List33]
sForm = "[LAST WEEK OF 4-5-4]"
 
sState = "SELECT " & sForm & ".jobs, " & sForm & ".[sla/ola], " & sForm & ".[special instructions], " & sForm & ".TIMES" & _
    " FROM  " & sForm & _
    " WHERE (" & sForm & ".[DAY OF WEEK])= " & Chr(34) & sFilter & Chr(34) & ";"
   ' " WHERE [CALENDAR MONTH].[Day of Month] = " & Chr(34) & "15" & Chr(34) & ";"
 
 
qdf.SQL = sState
 
DoCmd.OpenQuery ("Flex4")
DoCmd.OpenReport ("Flex4"), acViewPreview, , , acHidden
 
Set qdf = Nothing
 
Set db = Nothing
 
End Sub

Those 11 boxes break down into 6 sub reports. Obviously some of those aren't run on a regular basis, but I need them all to print when necessary on 1 report broken down by subreport when selected by the user. (saving paper, go recycling!!)

Any other suggestions would be greatly appreciated.

 

[GinaWhipp]

Hmm, instead of setting the List Box to NULL which isn't possible, why not try setting it to empty by doing the below...

sState = ""

Not sure that will work either but it's worth a try.

 

[mickie52]

That didn't quite work, but I found that if I use

Forms![Packets]![List10].RowSource = ""

on exit it resets the rowsource to empty.

 

[GinaWhipp]

Great! You found something that works and that's all that counts!

 

[mickie52]

Thanks for the help, you put me on the right path.