execution of this application has stopped due to a run-time error

[Gismo]

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

 

[Minty]

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]

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

 

[Gasman]

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]

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;

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

 

[Gismo]

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

 

[Gismo]

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;

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.

 

[Minty]

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]

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]

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]

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]

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]

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?

 

[Gasman]

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]

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]

just leave the export spec blank, and see if you like the ouptut. You don't always need an export spec.

 

[Gasman]

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]

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

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]

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

 

[gemma-the-husky]

@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.