Syntax error

[JoJo6205]

Hi All,

I found code for what I'm trying to do and have modified to fit my tables however I can't get it to work properly and I"m not sure why. What I"m trying to do is export a table into Excel and have the records sort into different tabs within one workbook based on the Supplier name.

The table I want to export is FBKO Supplier and the field is Supplier. The supplier names are in another table called Suppliers only and the related field is VendorName. There are several other fields within FBKO Supplier that need to be included in the export.

Clearly I'm missing something.

The error message is Object 'FBKO_Query_2, Base_SQL & VendorName' already exists.




Option Compare Database
Option Explicit

Sub Export_Suppliers()

Dim Suppliers_only As Recordset


Dim VendorName As String
Dim Base_SQL As String
Dim FBKO_Query_2 As String

Base_SQL = "SELECT * FROM FBKO_Supplier WHERE Supplier = "

Set Suppliers_only = CurrentDb.OpenRecordset("Suppliers only")

Do While Not Suppliers_only.EOF


VendorName = Suppliers_only("VendorName")

FBKO_Query_2 = "get_Suppliers" & VendorName & "_DC_Name"
CurrentDb.CreateQueryDef "FBKO_Query_2, Base_SQL & VendorName"

DoCmd.TransferText TransferType:=acExportDelim, TableName:=FBKO_Query_2, FileName:=VendorName & " FBKO.csv", HasFieldNames:=True

CurrentDb.QueryDefs.Delete FBKO_Query_2
Suppliers_only.MoveNext

Loop

End Sub

[orange]

What exactly is this "Suppliers only"?

I think you would make your learning easier if you
worked through this tutorial
You will learn about tables and relationships.
In addition, I recommend you reference this link by Crystal for Access info, tutorials, samples and references.
Good luck.

[JoJo6205]

"Suppliers only" is the name of a table with the VendorName field that matches the Supplier field in the "FBKO Supplier" table.

The first link doesn't seem to work but the second one does. I'll look through it and see what I can find to help me.

Also, if someone has a better idea to do what I need to do, I'm more than happy to listen.

[John_G]

In this statement:

CurrentDb.CreateQueryDef "FBKO_Query_2, Base_SQL & VendorName"

you have the quotes in the wrong place; in fact you don't need any at all.

With the statement as you have it, you are trying to create a new query with the name highlighted in red, because that whole string is in one set of quotes.

You already have the query name set here : FBKO_Query_2 = "get_Suppliers" & VendorName & "_DC_Name"

What you need is : CurrentDb.CreateQueryDef FBKO_Query_2, Base_SQL & VendorName

nothing in quotes because all the information is already in string variables.

[ssanfu]

In addition to John_G's comments, there are a couple of other changes that should be made.

Since the variable "VendorName" is a string, there needs to be delimiters in the SQL.
And the recordset needs to be closed, then destroyed. If any of the vendor names have quotes (single or double), you will need to handle them or the SQL will error.

Sub Export_Suppliers()

    Dim dB As DAO.Database
    Dim Suppliers_only As DAO.Recordset

    Dim sVendorName As String
    Dim Base_SQL As String
    Dim FBKO_Query_2 As String

    Dim tmpQueryDef As String

    Set dB = CurrentDb

    Base_SQL = "SELECT * FROM FBKO_Supplier WHERE Supplier = "

    Set Suppliers_only = CurrentDb.OpenRecordset("Suppliers only")

    Do While Not Suppliers_only.EOF

        sVendorName = Suppliers_only("VendorName")

        FBKO_Query_2 = "get_Suppliers_" & VendorName & "_DC_Name"
        'Debug.Print FBKO_Query_2

        tmpQueryDef = Base_SQL & "'" & sVendorName & "';"  ' Added text delimiters
        'Debug.Print tmpQueryDef

        dB.CreateQueryDef FBKO_Query_2, tmpQueryDef

        DoCmd.TransferText TransferType:=acExportDelim, TableName:=FBKO_Query_2, FileName:=sVendorName & "FBKO.csv", HasFieldNames:=True

        dB.QueryDefs.Delete FBKO_Query_2

        Suppliers_only.MoveNext

    Loop

    '    clean up
    Suppliers_only.Close
    Set Suppliers_only = Nothing

    Set dB = Nothing
    
End Sub

[JoJo6205]

Hi Steve,32

Below is the code with a couple of changes regarding the updated field and table names. When I run the code, a Run-Time Error of 3012 pops up. I've tried everything I can think of, and reviewed others posts with the same issue, and can't figure it out. The red line is where the error seems to be occurring.

BTW, "Veni, Vidi, Velcro"? lololol


Sub Export_Suppliers()


Dim dB As DAO.Database
Dim SuppliersOnly As DAO.Recordset


Dim sVendorName As String
Dim Base_SQL As String
Dim FBKO_Query_2 As String


Dim tmpQueryDef As String


Set dB = CurrentDb


Base_SQL = "SELECT * FROM FBKO_Supplier WHERE Supplier = "


Set SuppliersOnly = CurrentDb.OpenRecordset("SuppliersOnly")


Do While Not SuppliersOnly.EOF


sVendorName = SuppliersOnly("Vendor Name")


FBKO_Query2 = "get_Suppliers_" & "Vendor Name" & "_DC_Name"
'Debug.Print FBKO_Query_2


tmpQueryDef = Base_SQL & "'" & sVendorName & "';" ' Added text delimiters
'Debug.Print tmpQueryDef


dB.CreateQueryDef FBKO_Query2, tmpQueryDef


DoCmd.TransferText , acExportDelim, "FBKO_Suppliers", sVendorName & "FBKO.xlsx", True
dB.QueryDefs.Delete FBKO_Query2


SuppliersOnly.MoveNext


Loop


' clean up
SuppliersOnly.Close
Set SuppliersOnly = Nothing


Set dB = Nothing

End Sub

[John_G]

It's because the query already exists, probably from an earlier execution of your code.

What you need to do is delete the existing version first, then create the query one. You also need to take take of (i.e. ignore!) the error that occurs if you try to delete a non-existant query:

On error resume next    ' Ignore the error if there is no query to delete
db.querydefs.delete  FBKO_Query2
on error goto 0   ' Turn error trapping back on
dB.CreateQueryDef FBKO_Query2, tmpQueryDef

[JoJo6205]

Thank you for the quick response. Another error is popping up and I've tried to fix it as well but no luck.



On Error Resume Next 'ignore the error if there is not query to delete
dB.QueryDefs.Delete FBKO_Query2
On Error GoTo 0 'Turn error trapping back on

dB.CreateQueryDef FBKO_Query2, tmpQueryDef


DoCmd.TransferText , acExportDelim, "FBKO_Suppliers", sVendorName & "FBKO.xlsx", True
dB.QueryDefs.Delete FBKO_Query2


SuppliersOnly.MoveNext

This message reads 'You cannot import his file.' I've looked up this message online and tried fix it but again, not happening.

[John_G]

You have an extraneous comma after "TransferText":

Try : DoCmd.TransferText acExportDelim, "FBKO_Suppliers", sVendorName & "FBKO.xlsx", True

[JoJo6205]

I'm back ... I'm thinking I should mention I have Access 2013. After reading some other posts, I realized knowing with Access I have is important.


So I updated the line and the report looks like it going to run but I'm getting another error.

'The report name 'FBKO_Supplier' you entered in either the property sheet or macro is misspelled or refers to a report that doesn't exist.'

I did have a misspell and corrected. I created a blank workbook with the file name and received the same error message.




On Error Resume Next 'ignore the error if there is not query to delete
dB.QueryDefs.Delete FBKO_Query2
On Error GoTo 0 'Turn error trapping back on

dB.CreateQueryDef FBKO_Query2, tmpQueryDef


DoCmd.OutputTo acOutputReport, "FBKO_Supplier", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint




dB.QueryDefs.Delete FBKO_Query2


SuppliersOnly.MoveNext

[John_G]

The error may not be in the DoCmd statement.

If you keep some version of the query FBKO_Query2, does the report run on its own, to the screen?

Is your report called "FBKO_Supplier" or "FBKO_Suppliers" ?

You use both, but are they both supposed to refer to the report?

[JoJo6205]

The FBKO_Query2 does not run on it's own yet but I ran it this morning to create the FBKO_Supplier table in Access. I corrected the FBKO_Suppliers previously but didn't copy it into my last post. Below is the complete code:



Sub Export_Suppliers()


Dim dB As DAO.Database
Dim SuppliersOnly As DAO.Recordset


Dim sVendorName As String
Dim Base_SQL As String
Dim FBKO_Query_2 As String


Dim tmpQueryDef As String


Set dB = CurrentDb


Base_SQL = "SELECT * FROM FBKO_Supplier WHERE Supplier = "


Set SuppliersOnly = CurrentDb.OpenRecordset("SuppliersOnly")


Do While Not SuppliersOnly.EOF


sVendorName = SuppliersOnly("Vendor Name")


FBKO_Query2 = "get_Suppliers_" & "Vendor Name" & "_DC_Name"
'Debug.Print FBKO_Query_2


tmpQueryDef = Base_SQL & "'" & sVendorName & "';" ' Added text delimiters
'Debug.Print tmpQueryDef

On Error Resume Next 'ignore the error if there is not query to delete
dB.QueryDefs.Delete FBKO_Query2
On Error GoTo 0 'Turn error trapping back on

dB.CreateQueryDef FBKO_Query2, tmpQueryDef


DoCmd.OutputTo acOutputReport, "FBKO_Supplier", "Excel97-Excel2003Workbook(*.xls)", "", False, "", , acExportQualityPrint




dB.QueryDefs.Delete FBKO_Query2


SuppliersOnly.MoveNext


Loop


' clean up
SuppliersOnly.Close
Set SuppliersOnly = Nothing


Set dB = Nothing

End Sub

[John_G]

The FBKO_Query2 does not run on it's own yet but I ran it this morning to create the FBKO_Supplier table in Access

I'm confused here - the FBKO_Query2 query doesn't create anything - it's a Select query and selects all records for a given supplier in the FBKO_Supplier table.

So then what do you do with it? Is a record source for a report, and is that report also called FBKO_Supplier?

If all of those are true, I don't see why the DoCmd.OutputTo shouldn't work.

If it does not, what is the error message?

[JoJo6205]

I'm confused too. I continued to read this past weekend so I could understand more and realized I simply needed to start over. Thank you for your help, John G.