execution of this application has stopped due to a run-time error (1 Viewer)

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
I have the code working early this morning, then I started moving code around and somehow stuffed it up and not sure where I went wrong

1620717447803.png


 

Minty

AWF VIP
Local time
Today, 20:57
Joined
Jul 26, 2013
Messages
8,760
That line looks like it should be replacing the end of the one above it (I can't directly tell as the error message is in the way), but you don't appear to be setting those variables to any values?

Maybe just take a step back, and carefully write the code for the process from scratch knowing what you now know?

Declare all your variables, set them to the correct values, run the export.
 

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
I am now at this point

Function ExporttoCSV()

Dim strTabletoExport As String
Dim strExportSpec As String
Dim strFilePathAndName As String
Dim blnHasHeaders As Boolean
Dim strExportFile As String

DoCmd.TransferText acExportDelim, strExportSpec, strTabletoExport, strFilePathAndName, blnHasHeaders

End Function

Private Sub ExcelOrderLineItems_Click()
On Error GoTo ExcelOrderLineItems_Click_Err

Dim strExportFile As String
ExporttoCSV = strExportFile

strExportFile = CurrentProject.Path & "\CS Order Line Items.csv"

DoCmd.TransferText acExportDelim, "Order Line Items", "CS Order line Items Qry", strExportFile, True
1620720090398.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:57
Joined
Sep 21, 2011
Messages
8,781
I thought we had already established you do not have a export spec?

You just appear to be cobbling words together with no idea as to what any mean? :(

That is just a recipe for disaster, and intellisense is meant to prevent that, but does not appear to work for you.? :(

Or have you just copied and pasted that from another post?

LOOK at the link I posted for the command, and read what each parameter is meant to represent, so you can understand what goes where.

I do not know the keyword version and I always use positional paremeters, but I still have to look up what goes where. :)
 

Minty

AWF VIP
Local time
Today, 20:57
Joined
Jul 26, 2013
Messages
8,760
None of that makes any sense.
Your function is simply declaring a load of variables, not actually setting them to anything and therefore when you are running it, it comes as no surprise that access complains there is no Table name provided.

Unless you are going to reuse the export from different places there is no benefit to chucking it in a function.
If you want to structure it better try this;
SQL:
Private Sub ExcelOrderLineItems_Click()

    On Error GoTo ExcelOrderLineItems_Click_Error
   
    Dim strTabletoExport    As String
    Dim strExportSpec       As String
    Dim blnHasHeaders       As Boolean
    Dim strExportFile       As String

    strExportFile = CurrentProject.Path & "\CS Order Line Items.csv"
    strExportSpec = "Order Line Items"
    strTabletoExport = "CS Order line Items Qry"
    blnHasHeaders = True

   
    DoCmd.TransferText acExportDelim, strExportSpec, strTabletoExport, strExportFile, blnHasHeaders
   
    MsgBox "File exported", vbInformation, "Export Complete"
   
   
    On Error GoTo 0
    Exit Sub

ExcelOrderLineItems_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExcelOrderLineItems_Click, line " & Erl & "."

End Sub
 
Last edited:

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
I thought we had already established you do not have a export spec?

You just appear to be cobbling words together with no idea as to what any mean? :(

That is just a recipe for disaster, and intellisense is meant to prevent that, but does not appear to work for you.? :(

Or have you just copied and pasted that from another post?

LOOK at the link I posted for the command, and read what each parameter is meant to represent, so you can understand what goes where.

I do not know the keyword version and I always use positional paremeters, but I still have to look up what goes where. :)
This is what I got out of the post to create the export spec

In my code to export i called for the function
'Function ExporttoCSV()

'Dim strTabletoExport As String
'Dim strExportSpec As String
'Dim strFilePathAndName As String
'Dim blnHasHeaders As Boolean
'Dim strExportFile As String

'DoCmd.TransferText acExportDelim, strExportSpec, strTabletoExport, strFilePathAndName, blnHasHeaders

'End Function

1620721456263.png
 
Last edited:

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
None of that makes any sense.
Your function is simply declareing a load of variables, not actually setting them to anything and therefore when you are running it, it comes as no surprise that access complains there is no Table name provided.

Unless you are going to reuse the export form different places there is no benefit to chucking it in a function.
If you want to structure it better try this;
SQL:
Private Sub ExcelOrderLineItems_Click()

    On Error GoTo ExcelOrderLineItems_Click_Error
   
    Dim strTabletoExport    As String
    Dim strExportSpec       As String
    Dim blnHasHeaders       As Boolean
    Dim strExportFile       As String

    strExportFile = CurrentProject.Path & "\CS Order Line Items.csv"
    strExportSpec = "Order Line Items"
    strTabletoExport = "CS Order line Items Qry"
    blnHasHeaders = True

   
    DoCmd.TransferText acExportDelim, strExportSpec, strTabletoExport, strExportFile, blnHasHeaders
   
    MsgBox "File exported", vbInformation, "Export Complete"
   
   
    On Error GoTo 0
    Exit Sub

ExcelOrderLineItems_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExcelOrderLineItems_Click, line " & Erl & "."

End Sub
The code above still requires the folder to be created first.

1620721921323.png
 

Minty

AWF VIP
Local time
Today, 20:57
Joined
Jul 26, 2013
Messages
8,760
I'll be blunt here, I think you need to read what people are telling you a little more carefully.
That error is about the export specification missing, not the folder for the export.

You need to create the export specification once, manually, and make sure you save it as "Order Line Items".
That's what that link is telling you to do.

You then refer to that in your export code.
 

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
I thought we had already established you do not have a export spec?

You just appear to be cobbling words together with no idea as to what any mean? :(

That is just a recipe for disaster, and intellisense is meant to prevent that, but does not appear to work for you.? :(

Or have you just copied and pasted that from another post?

LOOK at the link I posted for the command, and read what each parameter is meant to represent, so you can understand what goes where.
I do not know the keyword version and I always use positional paremeters, but I still have to look up what goes where.

:)
Yes you are correct, I am have massive issue with coding, somehow I just can not get the intellisence as you call it in my head but when I look at a code when supplied by you guys i say Oh, yes, now I see :):)

The code supplied now make so much more sense, It read easily, but still...
 

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
I'll be blunt here, I think you need to read what people are telling you a little more carefully.
That error is about the export specification missing, not the folder for the export.

You need to create the export specification once, manually, and make sure you save it as "Order Line Items".
That's what that link is telling you to do.

You then refer to that in your export code.
I hear what you are saying, I did do the export specification as per the link, not so?

I have quite a few files that I export, can this not be done via code instead of creating it manually?

is that what you are referring to? Each user needs to be able to export his own version of the files. Do they need to do a once of export specification for each file as well?

i think this is where my confusion comes in and not as much as not reading what you are giving me
 

Minty

AWF VIP
Local time
Today, 20:57
Joined
Jul 26, 2013
Messages
8,760
The export spec is stored in the Database so once created will be distributed with the front end.
It only needs the export spec because you are using a comma-delimited export and need to tell it what delimiter to use.

If you were to simply export to Excel you wouldn't need it.

If you have multiple different files then you will need an export spec for each different export, if the columns change.
As I said it only needs creating once, per export definition.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:57
Joined
Sep 21, 2011
Messages
8,781
Yes you are correct, I am have massive issue with coding, somehow I just can not get the intellisence as you call it in my head but when I look at a code when supplied by you guys i say Oh, yes, now I see :):)

The code supplied now make so much more sense, It read easily, but still...
@Gismo,
Intellisense is the software that guides/prompts you through each parameter, in the required order?, not something you learn.?
So if you follow the prompts AND understand what each parameter is for, so should create a valid line of code for a particular function.?
 

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
The export spec is stored in the Database so once created will be distributed with the front end.
It only needs the export spec because you are using a comma-delimited export and need to tell it what delimiter to use.

If you were to simply export to Excel you wouldn't need it.

If you have multiple different files then you will need an export spec for each different export, if the columns change.
As I said it only needs creating once, per export definition.
Oh, ok, thank you, that makes a hole lot of sense

I will run through all of my queries

What does this mean?
Seem to be an Access fix, correct?


1620725587577.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:57
Joined
Sep 21, 2011
Messages
8,781
That appears to me to say you are using the same character for separating the fields as is used for either the decimal point or the text delimiter.?
If so how is Access meant to know which is which? :(

You need different characters for each.

For instance, I would use
" for text delimiters
. for decimal point
, for field separator.
 

Minty

AWF VIP
Local time
Today, 20:57
Joined
Jul 26, 2013
Messages
8,760
If your local regional decimal separator is a comma, and you try and use a comma as the field delimiter it won't work for obvious reasons.

Pick another separator or export to excel then convert to CSV.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:57
Joined
Sep 12, 2006
Messages
14,520
just leave the export spec blank, and see if you like the ouptut. You don't always need an export spec.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:57
Joined
Sep 21, 2011
Messages
8,781
just leave the export spec blank, and see if you like the ouptut. You don't always need an export spec.
When Dave says leave blank, he means omit the parameter altogether, by just putting another comma.
DO NOT put "" or " " for the spec.
 

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
That appears to me to say you are using the same character for separating the fields as is used for either the decimal point or the text delimiter.?
If so how is Access meant to know which is which? :(

You need different characters for each.

For instance, I would use
" for text delimiters
. for decimal point
, for field separator.
As you indicated, it is exactly the same as what I have, infarct, it is default
except for Decimal, symbol which I now changed

1620726884144.png


It seems to export as expected.

I would really like it to be saved on the desktop instead of the current project path and also would like it to open as soon as it has been exported

Thank you so much for the assistance, i know I usually get all frustrated then I don't listen :), causing frustration on your side
 

Gismo

Registered User.
Local time
Today, 22:57
Joined
Jun 12, 2017
Messages
890
When Dave says leave blank, he means omit the parameter altogether, by just putting another comma.
DO NOT put "" or " " for the spec.
Yes, I actually tried that

Private Sub ExcelOrderLineItems_Click()

On Error GoTo ExcelOrderLineItems_Click_Error

Dim strTabletoExport As String
Dim strExportSpec As String
Dim blnHasHeaders As Boolean
Dim strExportFile As String

strExportFile = CurrentProject.Path & "\CS Order Line Items Qry.csv"
'strExportSpec = "CS Order line Items Export Specification"
strTabletoExport = "CS Order line Items Qry"
blnHasHeaders = True
1620732595171.png
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:57
Joined
Sep 12, 2006
Messages
14,520
@Gismo

can you show us what you are using now
you can't set a string to hold the filespec as a blank string, and then use it/

You need, as @Minty pointed out

DoCmd.TransferText acExportDelim, strExportSpec, strTabletoExport, strExportFile, blnHasHeaders

if you don't have an export spec then
DoCmd.TransferText acExportDelim, , strTabletoExport, strExportFile, blnHasHeaders

so
1. setting strexportspec to "" is not the same as leaving the argument blank
2. the path to your desktop will be something like "C:\users\username\desktop\"
The best thing is to just get it working first, and then work out how to change the save location to the desktop.
 

Users who are viewing this thread

Top Bottom