Runtime Error 3027

[jeran042]

Here is the problem,

I have a form "frm: Report Creator" with a subform that is linked to a query. Basically the parent form acts like is a report creator. Where a user can input data into any field to pull results to the subform. On the parent form, I also have a field (txtFilter) that pulls the filter property from the subform, and that is where I am storing my WHERE clause. And all this works as expected.

My problem is, I want the ability to export the results from from the subform to excel. Here is the code that I am using:

Private Sub cmdOutputXLSX_Click()

Dim strSQL      As String
Dim strQry      As String
Dim myPath      As String
Dim sFileName   As String



Set DB = CurrentDb
Set Qdf = DB.CreateQueryDef(strQry, strSQL)
myPath = "C:\Users\jrenald\Desktop\Budget process information"
sFileName = " Custom Report " & Format(Date, "_mmddyy") & ".pdf"

strSQL = "SELECT * FROM COPY_TBL_LEDGER_DETAIL WHERE me.txtFilter"
strQry = "TempSELECTName"
    

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
   strQry, myPath + sFileName, True
 
'DoCmd.DeleteObject acQuery, strQry

End Sub

I have verified that I have full permission to write to the folder on my desktop, and the table has an Auto number field with the index set to "Yes (No Duplicates)" (I have read that this can cause the run time error)

I am stuck on how to proceed, and would appreciate any direction

Also, if there is a better way to achieve this, I will be willing to try it,

Very much appreciated,

 

[plog]

strSQL = "SELECT * FROM COPY_TBL_LEDGER_DETAIL WHERE me.txtFilter"

AVariable ="escape from the string like this."

"When you build a string and want to use a variable's value inside it, you need to " & AVariable

 

[jeran042]

Ok, I have updated the sql string to:

strSQL = "SELECT * FROM COPY_TBL_LEDGER_DETAIL WHERE & me.txtFilter"

But I still get the same runtime error of 3027?

 

[ByteMyzer]

That should be:

strSQL = "SELECT * FROM COPY_TBL_LEDGER_DETAIL WHERE " & Me.txtFilter & ";"

 

[plog]

That's still not it. Look over my example again.

However, I'm not convinced that will fix the issue, or that its the code you posted that is causing it. 3027 is about not being able to update a dataset. You aren't doing that in your code anywhere (except the commented out portion).

Are you sure its that code causing it?

 

[jeran042]

I'm fairly sure, as all other commands function as expected, and this is the first time I have come across this error.

Thank you for lesson is using variables in SQL strings. The debug.print shows a more accurate result of:

SELECT * FROM COPY_TBL_LEDGER_DETAIL WHERE ([ACCOUNT_NUMBER] = 58503);

Compared to:

SELECT * FROM COPY_TBL_LEDGER_DETAIL WHERE & me.txtFilter

 

[plog]

Everytime I look at the code I see something new wrong with it and further can't understand the ultimate aim.

Set Qdf = DB.CreateQueryDef(strQry, strSQL)

at that point in the code neither strQry nor strSQL contains any values.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
   strQry, myPath + sFileName, True

You go through all this trouble of building strSQL, but then you never use it. You use an existing query named 'TempSELECTName'. Why? And does that query exist?

'DoCmd.DeleteObject acQuery, strQry

I know this is commented out, but this smells like the error. Why are you deleting your query? What happens when someone else want sto use it?

Again, super lost in all this code. Did someone else write it? Can you go back to them for help?

 

[arnelgp]

Look at your variable, mypath. Add a backslash at the end.

 

[jeran042]

Everytime I look at the code I see something new wrong with it and further can't understand the ultimate aim.

Set Qdf = DB.CreateQueryDef(strQry, strSQL)

at that point in the code neither strQry nor strSQL contains any values.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
   strQry, myPath + sFileName, True

You go through all this trouble of building strSQL, but then you never use it. You use an existing query named 'TempSELECTName'. Why? And does that query exist?

'DoCmd.DeleteObject acQuery, strQry

I know this is commented out, but this smells like the error. Why are you deleting your query? What happens when someone else want sto use it?

Again, super lost in all this code. Did someone else write it? Can you go back to them for help?

You are correct, someone did give me this code, I suspect he only meant it to be an example of what needed to be done, and expected me to fill in the blanks. Which is what I am trying to do.

Good point about "Set Qdf" at that point neither do have any variables. I moved it to after strSQL and strQry. and it looks like now it is picking up both of these results.

How can I pass the results of strSQL to the ""TempSELECTName" ? And from there can I add it to the "DoCmd.TransferSpreadsheet"?

 

[Accessanitor]

I haven't looked at all the code, but the query should be something like this for it to work:

Private Sub cmdOutputXLSX_Click()

Dim strSQL      As String
Dim strQry      As String
Dim myPath      As String
Dim sFileName   As String

'Don't know what strQry is about, but hopefully fixing for strSQL:

myPath = "C:\Users\jrenald\Desktop\Budget process information"
sFileName = " Custom Report " & Format(Date, "_mmddyy") & ".pdf"

strSQL = "SELECT * FROM [COPY_TBL_LEDGER_DETAIL] WHERE ([ACCOUNT_NUMBER] = " & chr(39) & me.txtFilter  Chr(39) & ");"

strQry = "TempSELECTName"
 
Set DB = CurrentDb
Set Qdf = DB.CreateQueryDef(strQry, strSQL) 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
   strQry, myPath + sFileName, True
 
'DoCmd.DeleteObject acQuery, strQry

End Sub

The syntax where you refer to the variable is where the problem might be?

strSQL = "SELECT * FROM [COPY_TBL_LEDGER_DETAIL] WHERE ([ACCOUNT_NUMBER] = " & chr(39) & me.txtFilter Chr(39) & ");"

 

[jeran042]

Thank you very much for your post, it is very similar to what I ended up working out, which is:

Private Sub cmdEXCEL_Click()

    Dim whereClause As String
    Dim myPath      As String
    Dim sFileName   As String
    
    myPath = "C:\Users\X\Desktop\Budget process information\"
    sFileName = " Custom Report " & Format(Date, "_mmddyy") & ".xlsx"

    ' Generate our WHERE clause based on form values
    whereClause = Me.txtFilter
    
    
    Dim query As String
    query = "SELECT * FROM TBL_LEDGER_DETAIL WHERE " & Me.txtFilter & ";"
    
    Dim filename As String
    filename = myPath + sFileName

                
    ' Placeholder query already in the database
    Dim queryName As String
    queryName = "qryCustom_Report"
        
    ' Update the placeholder with the created query
    CurrentDb.QueryDefs(queryName).SQL = query

    ' Run the export
    DoCmd.OutputTo acOutputQuery, queryName, acFormatXLSX, filename, True
    
    End Sub