Clear crosstab query results?

[MikeN]

I have a cross-tab query designed with multiple tables and is controlled from a multi-select listbox criteria. Every time it is run, it appears to 'append and save' the results to the datasheet.

Is there a way to 'clear' the results before running the query so the user has a fresh query that only displays the specific results they are looking for, instead of every result ever run? I can't even manually delete the results at this point. All of my properties are the default ones from when a query is designed..if that makes any difference.

[ranman256]

if 'datasheet' you mean Table, then run a delete query on the table before the append.

[June7]

What do you mean by "controlled from a multi-select listbox criteria"? Is purpose of this textbox to enter data into record? The table is included in a query that restricts records by INNER JOIN?

As ranman suggests, first delete records. OR apply filter criteria - which gets tricky when CROSSTAB is involved.

[MikeN]

Yes, I misspoke and meant table. Below is the code that I am using to run the query. I have a multi-select listbox that is used to pass criteria into the query (https://www.accessforums.net/access/...ery-56594.html) based on dates selected in the listbox. The query is working as I hoped it would - which will eventually just get exported to Excel. The only reason I am not exporting at this point yet is to be able to see (and adjust) it on-the-fly.

I added the "Delete From" code to try to delete the "old" query results but I get Run-time error '3086' Could not delete from specified tables.

Docmd.RunSQL "DELETE * FROM BeltQuery"

Private Sub qryBeltSurvey_Click()
' ------CrossTab Query Layout-------
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    Dim frm As Form, ctl As Control




        Set db = CurrentDb()
        Set qdf = db.QueryDefs("BeltQuery")
        Set frm = Forms!frm_QueryBuilder
        Set ctl = frm!listDates
    
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "You must make a selection from the dates list"
    Exit Sub
    End If
    
    DoCmd.RunSQL "DELETE * FROM BeltQuery" 'delete old records from previous query
    
    For Each varItem In ctl.ItemsSelected
        strCriteria = strCriteria & "tbl_trip.TripDate = #" & ctl.ItemData(varItem) & "# OR"
    Next varItem
    strCriteria = Left(strCriteria, Len(strCriteria) - 3)


     strSQL = "TRANSFORM Count(tbl_BeltSurveyData.TotalCount) AS CountOfTotalCount " & vbCrLf & _"SELECT tbl_trip.TripDate, tbl_trip.Instructor, tbl_trip.SampleMethod, tbl_trip.SampleSite, tbl_BeltSurvey.Observers, tbl_BeltSurvey.TransectNum, tbl_BeltSurvey.Notes " & vbCrLf & _
"FROM tbl_trip INNER JOIN (tbl_BeltSurvey INNER JOIN (tbl_beltSurveySpecies INNER JOIN tbl_BeltSurveyData ON tbl_beltSurveySpecies.BeltSurveySpeciesID = tbl_BeltSurveyData.BeltSurveySpecies) ON tbl_BeltSurvey.BeltSurveyID = tbl_BeltSurveyData.BeltSurveyID) ON tbl_trip.TripID = tbl_BeltSurvey.TripID " & vbCrLf & _
"GROUP BY tbl_trip.TripDate, tbl_trip.Instructor, tbl_trip.SampleMethod, tbl_trip.SampleSite, tbl_BeltSurvey.Observers, tbl_BeltSurvey.TransectNum, tbl_BeltSurvey.Notes " & vbCrLf & _
"PIVOT tbl_beltSurveySpecies.Taxa;"




    qdf.SQL = strSQL
    DoCmd.OpenQuery "BeltQuery"
    
    Set db = Nothing
    Set qdf = Nothing
End Sub

[June7]

Delete from the one table that is getting values from the combobox, not the query.

[MikeN]

Delete from the one table that is getting values from the combobox, not the query.

I'm not quite sure what you mean by deleting from the table that has the values. The rowsource for the listbox is a 'select'

SELECT tbl_trip.TripID, tbl_trip.TripDate, tbl_trip.Instructor FROM tbl_Group INNER JOIN tbl_trip ON tbl_Group.SchoolPK = tbl_trip.Affiliation GROUP BY tbl_trip.TripID, tbl_trip.TripDate, tbl_trip.Instructor HAVING (((tbl_trip.Instructor)=[Forms]![frm_QueryBuilder]![cmbInstructor]));

and does not have a table (other than the master tables) associated with it.

[June7]

Sorry, listbox instead of combobox.

You said the query 'is controlled from a multi-select listbox criteria' - items selected from listbox are saved into a table? Which table? Delete records from that table before user listbox selections are saved.

[MikeN]

Sorry, listbox instead of combobox.

You said the query 'is controlled from a multi-select listbox criteria' - items selected from listbox are saved into a table? Which table? Delete records from that table before user listbox selections are saved.

I figured you meant listbox - no worries! The listbox contains dates that are only passed into the query as criteria, but no data are not stored in tables during the process. I'm wondering if I am doing this 'process' all wrong, or the complicated way, now that you're suggesting stuff that I haven't done. I used examples of "passing multi-select listbox to query criteria" that I found online to develop the on-click code, so it was not an original idea of mine. The query is working correctly but it just prevents deleting the results from the query table.

[June7]

I am lost. Then what do you mean by "appears to 'append and save'"?

What action would be doing this 'append'?

I really don't understand why records would need to be deleted from a query. Delete records from tables. However, if the listbox selections are not saved anywhere then there is no need to delete anything.

If you want to provide db for analysis, follow instructions at bottom of my post.

[MikeN]

I am lost. Then what do you mean by "appears to 'append and save'"?

What action would be doing this 'append'?

I really don't understand why records would need to be deleted from a query. Delete records from tables. However, if the listbox selections are not saved anywhere then there is no need to delete anything.

If you want to provide db for analysis, follow instructions at bottom of my post.

Every time I run the query it adds the results to the query table, but never gets rid of them. In other words, its not giving a fresh table when the query gets re-run with different dates - it just appends the newer results to the query table but it still has the old results saved too. I only want the 'newest' results available based on the dates that the user selected, not results from every time query was run. For example, if user A queries the data from 12/1/2015 then user B queries data from 12/9/2015 at a later date, the query table is populated with both 12/1 and 12/9 results when I only want user B to see the data from 12/9 because that's the only date they selected. I'll try to create a diet version of my database and attach it for analysis.

[June7]

Run what query? SELECT queries are not 'run', they are opened or used as RecordSource for form/report.

Only action queries (DELETE, INSERT, UPDATE) are 'run'. So unless you are 'running' an INSERT action or the listbox selected items are saved into a table, your issue description makes no sense. At this point, really need to observe the process directly. This means providing the db for analysis.

[MikeN]

Run what query? SELECT queries are not 'run', they are opened or used as RecordSource for form/report.

Only action queries (DELETE, INSERT, UPDATE) are 'run'. So unless you are 'running' an INSERT action or the listbox selected items are saved into a table, your issue description makes no sense. At this point, really need to observe the process directly. This means providing the db for analysis.

I attached a version that has a bunch of the unnecessary tables/forms removed. I realized that my listbox might not be working correctly to pass the date criteria like I thought it was, so this may be the problem. It seems like it might be skipping over all the 'For Each' code and going straight into the Docmb.OpenQuery which is why it looked like it was appending data that didn't meet the date criteria selected from the listbox, when actually it was querying all the data as if it didn't have a criteria. Can you take a look at the on-click code on the frm_QueryBuilder to see if I did something wrong there too? I'm sure this is a mess to someone who knows the best way to design so please let me know if you have any questions.

Thanks again for all the pointers and help. Sorry if i was not even addressing the true problem this whole time! It took deleting some things to maybe realize this!

[June7]

First thing I encountered is the listbox loop code is building a string that uses # delimiter with numbers, not dates. # is a date/time delimiter, numbers do not use delimiters. The code is pulling TripID from the listbox not TripDate. The result is like:

"tbl_trip.TripDate=#6#"

If you want to base the criteria on TripDate and not the TripID then modify the listbox properties so the value of the combobox is the displayed date, not the associated TripID. TripID does not have to be the first field in the RowSource. Or change code so the resulting string is:

"tbl_trip.TripID=6"

Also, since the listbox is set for multi-select, code must build an IN() string. Review http://allenbrowne.com/ser-50.html

That's as far is I went with debugging.

Suggest you learn how to debug code. Review link at bottom of my post.

[MikeN]

First thing I encountered is the listbox loop code is building a string that uses # delimiter with numbers, not dates. # is a date/time delimiter, numbers do not use delimiters. The code is pulling TripID from the listbox not TripDate. The result is like:

"tbl_trip.TripDate=#6#"

If you want to base the criteria on TripDate and not the TripID then modify the listbox properties so the value of the combobox is the displayed date, not the associated TripID. TripID does not have to be the first field in the RowSource. Or change code so the resulting string is:

"tbl_trip.TripID=6"

Also, since the listbox is set for multi-select, code must build an IN() string. Review http://allenbrowne.com/ser-50.html

That's as far is I went with debugging.

Suggest you learn how to debug code. Review link at bottom of my post.

Thank you for the help June7! I was trying many different types of examples to use the multi-select listbox and thought I had it with this one.

I was using debug.print earlier on and did see the #number# as the result, but did not recognize that as an issue - my novice-ness made me think that was just some underlying value that was associated to the record. But now I see exactly what you're talking about with calling the wrong identifier (e.g., tripID vs TripDate) relative to the debug results! I will continue doing research/learning with your suggestions! Thanks again for the help! And sorry again for sending you on a wild goose chase earlier on!

[June7]

Don't see it as wild goose chase. We've all stumbled down the same path. Just glad could give you some guidance.

Also recommend you use TripID if you want to allow multiple listbox selections.