My query keeps deleting itself?.. (1 Viewer)

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
Hello,
I came across a strange issue while working on my Excel Exports.
I've got a query I'm working on at the moment:

Code:
SELECT Tabule1.TabKlient, Tabule1.TabOsoba, Tabule1.TabItem, Tabule1.TabQty, Tabule1.TabNote, Tabule1.TabAddedBy, Tabule1.TabDate, Tabule1.TabStatus, Tabule1.ID
FROM Tabule1
WHERE (((Tabule1.TabKlient)=[Formuláře]![formExportExcel]![xKlientSelection])) OR ((([Formuláře]![formExportExcel]![xKlientSelection]) Is Null));
and my code, that runs the query being:

Code:
DoCmd.OutputTo acOutputQuery, "dotaz1", acFormatXLSX, , True
So now my excel file is being named after the query name and whenever i try to export into a location where i've exported to before, it gives me a prompt asking if i want to overwrite the file that already exists, on selecting yes, access gives me an error saying that "Query should have at least one destination field " this doesn't happen every time, and i didn't yet manage to find out what the exact criteria for this to occur is...


Whenever this happens, the code in my Query changes to SELECT;
 
Last edited:

Minty

AWF VIP
Local time
Today, 23:22
Joined
Jul 26, 2013
Messages
6,957
That won't normally happen on its own.
What is the full code you are using?
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
That won't normally happen on its own.
What the full code you are using?
There's nothing relevant to this except for what i already shared, this is everything on the form

Code:
Private Sub btnExcelBIG_Click()
DoCmd.SetWarnings False
DoCmd.OutputTo acOutputQuery, "dotaz1", acFormatXLSX, , True
DoCmd.SetWarnings True
Globals.Logging "Excel Export - Big"
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
5,671
Have you even tried to give a filename for the Excel workbook and see if it happens then.?
Can the query not produce any records sometimes?
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
Have you even tried to give a filename for the Excel workbook and see if it happens then.?
Can the query not produce any records sometimes?
I did not, I'm trying to make this work normally as it should, because i don't want to have to manually rewrite the query whenever somebody accidentally doesnt rename the file on creation.
I'm not sure what you mean by the second question, but when the query has the code i posted on start and isn't just "SELECT;" then it works perfectly based on given parameters.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 06:22
Joined
May 7, 2009
Messages
9,273
what is this [Formuláře]?
is this the English for Forms?
your SQL statement is in English, why this not?
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
what is this [Formuláře]?
is this the English for Forms?
your SQL statement is in English, why this not?
that's a czech word for forms, that part has been automatically generated by the query builder
 

arnelgp

error reading drive A:
Local time
Tomorrow, 06:22
Joined
May 7, 2009
Messages
9,273
is it possible to Override it with [Forms]
 

Minty

AWF VIP
Local time
Today, 23:22
Joined
Jul 26, 2013
Messages
6,957
Okay - I'm suspicious of turning the warnings off?
There is no reason you should be getting any warnings ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
5,671
I did not, I'm trying to make this work normally as it should, because i don't want to have to manually rewrite the query whenever somebody accidentally doesnt rename the file on creation.
I'm confused :confused: (easily done I admit), but what difference does it make if the filename is called "dotaz1" or "ExportFileName" ?, it is always going to be the same name?
I'm just wondering if a side effect of that error AND the query name being the same, causes this to happen.?
I'd certainly be trying changes like that to get to the bottom of it.?
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
I'm confused :confused: (easily done I admit), but what difference does it make if the filename is called "dotaz1" or "ExportFileName" ?, it is always going to be the same name?
I'm just wondering if a side effect of that error AND the query name being the same, causes this to happen.?
I'd certainly be trying changes like that to get to the bottom of it.?
How can i pre-define the output file name without giving it an exact location where to export to?
 

Minty

AWF VIP
Local time
Today, 23:22
Joined
Jul 26, 2013
Messages
6,957
For test purposes simply use something like

strPath = Application.CurrentProject.Path
strFileAndPath = strPath & "\MytestFile.xlsx"
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:22
Joined
Sep 21, 2011
Messages
5,671
How can i pre-define the output file name without giving it an exact location where to export to?
Where does it default to now? likely Documents folder of the user?, then use the same folder.?
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
Okay - I'm suspicious of turning the warnings off?
There is no reason you should be getting any warnings ?
Ok so, this form i made (the one i'm currently discussing here) "frmExportExcel" is a pop-up form on our "chalkboard" form (we use a physical board in our office where we write items we invoice to our clients at the end of the month), so this exists to give us options on what to export other than currently pending invoices/entries as it were on the main board form, where i had warnings turned off so i could export all currently pending records to excel, while also changing their yes/no field to yes, so they become "finalised". I just copied that over to not forget it later on when i'll put in the auto update on selected records.

i hope this isn't too confusing, it's kind of hard to translate for me!
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
Okay - I'm suspicious of turning the warnings off?
There is no reason you should be getting any warnings ?
i just commented both warning lines, exported twice and on second attempt i got the same error and my query has been wiped.
 

Minty

AWF VIP
Local time
Today, 23:22
Joined
Jul 26, 2013
Messages
6,957
There is another route try using this.

Code:
Dim strPath As Srtring
Dim strFile As String

strPath = Application.CurrentProject.Path
strFile = "\MytestFile.xlsx"
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "dotaz1", strPath & strFile, True
If your query keeps getting changed after this, then something else is at play.
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
There is another route try using this.

Code:
Dim strPath As Srtring
Dim strFile As String

strPath = Application.CurrentProject.Path
strFile = "\MytestFile.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "dotaz1", strPath & strFile, True
If your query keeps getting changed after this, then something else is at play.
Is there a way to make the name change based on the selections for instance?
Like if i were to make a selection based on all my criteria, it could then reflect that in the name?
Let's say i select active records, for the client "Pepsi co." for the month of July and it would say "Report-Active-Pepsi-July.xlsx"
 

Minty

AWF VIP
Local time
Today, 23:22
Joined
Jul 26, 2013
Messages
6,957
Yes simply add that into the filename spec. I normally add Full date as well so you know when it was run - something like;
Code:
Dim strPath As String
Dim strFile As String
Dim strCustomer As String

strCustomer = Me.cboMyCustomer
strPath = Application.CurrentProject.Path
strFile = "\Report_Active_" & strCustomer & "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "dotaz1", strPath & strFile, True
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
Yes simply add that into the filename spec. I normally add Full date as well so you know when it was run - something like;
Code:
Dim strPath As String
Dim strFile As String
Dim strCustomer As String

strCustomer = Me.cboMyCustomer
strPath = Application.CurrentProject.Path
strFile = "\Report_Active_" & strCustomer & "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "dotaz1", strPath & strFile, True
L O V E L Y, as per usual, I thank you very much!
 

mamradzelvy

Member
Local time
Tomorrow, 00:22
Joined
Apr 14, 2020
Messages
96
Yes simply add that into the filename spec. I normally add Full date as well so you know when it was run - something like;
Code:
Dim strPath As String
Dim strFile As String
Dim strCustomer As String

strCustomer = Me.cboMyCustomer
strPath = Application.CurrentProject.Path
strFile = "\Report_Active_" & strCustomer & "_" & Format(Date(),"yyyy_mm_dd") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "dotaz1", strPath & strFile, True
Hi Minty,
sorry to bother once more, but..
Can i just put in an IF statement for this as a way to handle the "null" selection in the client field, for when im selecting all clients?

Code:
Dim strPath As String
Dim strFile As String
Dim strCustomer As String

strCustomer = Me.xKlientSelection
strPath = Application.CurrentProject.Path
strFile = "\Report_Active_" & strCustomer & "_" & Format(Date, "yyyy_mm_dd") & ".xlsx"
If strPath Is Null Then
 strPath = "AllClients"
 End If
 Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "dotaz1", strPath & strFile, True
End Sub
I tried this, but that's apparently absolute nonsense.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom