Small Macro problem

[TooManyReports]

SOLVED:Small Macro problem

I am running trying to run this vba , but it errors @ the red print in the code and gives the following error: Runtime error '3075': Synteax error(missing operator) in query expression 'Sale_Area = WARM MIDDLE'.

Warm middle is a name of area in my query.

VBA highlights the Set qdf = db.CreateQueryDef("MyTempQDF", strSQL)

Private Sub Command0_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstAreas As DAO.Recordset

Set rstAreas = CurrentDb.OpenRecordset("Select Sale_Area From tblStoreInfo")

Set db = CurrentDb

For Each qdf In db.QueryDefs
If qdf.Name = "MyTempQDF" Then
db.QueryDefs.Delete (qdf.Name)
End If
Next


Do Until rstAreas.EOF

strSQL = "SELECT Sale_Area, Region, Store, City, State, Latitude, Longitude"
strSQL = strSQL & " FROM tblStoreInfo"
[COLOR=Red]strSQL = strSQL & " WHERE Sale_Area = " & rstAreas![Sale_Area][/COLOR]
Set qdf = db.CreateQueryDef("MyTempQDF", strSQL)
qdf.Close
Call SendTQ2ExcelNameNewSheet("MyTempQDF", "Sale Area" & rstAreas!Sale_Area, "C:\KML\Regions.xlsx")
db.QueryDefs.Delete ("MyTempQDF")
rstAreas.MoveNext

Loop
rstAreas.Close
Set rstAreas = Nothing
Set qdf = Nothing


End Sub

 

[MSAccessRookie]

Two questions:

What is the Data Type of the Field Sale_Area? It needs to be String
I am used to using "+" as opposed to "&" for String Concatenation in VBA. Have you tried that?

-- Rookie

 

[TooManyReports]

The Sale_Area is a text field

 

[nanscombe]

If Sale_Area is a text field then the criteria should be wrapped in single quotes, ie

strSQL = strSQL & " WHERE Sale_Area = '" & rstAreas![Sale_Area] & "'"

 

[TooManyReports]

Thanks that worked.

 

[TooManyReports]

One last thing to fix. I have the same text area in my Call line, VBA keeps thinking it is something other than text. How do I fix it?

 

[nanscombe]

You could try defining a string strSaleArea and using that instead?

And the function Nz(variant [, valueifnull ] ) may not go amiss

If the first pararameter is NULL then the value in the second parameter is used instead, in the following case an Empty string.

dim strSaleArea as string
...

strSaleArea = nz(rstAreas![Sale_Area],"")
...

strSQL = strSQL & " WHERE Sale_Area = '" & strSaleArea & "'"
...

Call SendTQ2ExcelNameNewSheet("MyTempQDF", "Sale Area" & strSaleArea, "C:\KML\Regions.xlsx")
...

 

[nanscombe]

I've taken the liberty of putting together a database as a demonstration of how you could use a form, "OutputDataByArea_Form", to make your life easier.

The recordsource of the form is based on a query which returns a list of Unique Sale_Area, "OutputDataByArea_UniqueAreas", and loops through each of the records in turn.

Because there is a TextBox containing Sale_Area on the form, I can reference that directly from inside the query "OutputDataByArea_Output" to limit the records returned. As this remains the same there is no need to create or delete temporary queries.

I've included a dummy function, "SendTQ2ExcelNameNewSheetDummy", to represents the call to your Excel export function.

Take a look and see whether it's useful to you.

 

[TooManyReports]

Well i added in my function, changed the info in your table. I get an error Too few parameters. Expected 1

 

[nanscombe]

I guess it's quiz time.

A) If you disabled the line calling your function does the listbox seem to populate Ok as it goes through the records?

B) If you manually move through the records on the form and open "OutputDataByArea_Output" do you see the records being returned change?

C) Does you function work if you hard-code the parameters rather than passing them in from the form?

D) Odd question but ... do any of your areas contain a single or double quote?

Bedtime for me I'm afraid. Off to count records rather than sheep.


I'll maybe catch up with you tomorrow.

 

[TooManyReports]

A. I don't need to disable it, it goes though even without it disabled, but when it is disabled I don't get any error.
B. Yes
C. Yes
D.No

Public Function SendTQ2ExcelNameNewSheet(strTQName As String, strSheetName As String, strWorkbookPathAndName As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to name your sheet to
    
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field
    Dim strPath As String
    Dim blnCreatedWkBk As Boolean
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler


    Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
 
' either creates a new workbook if the first time and it doesn't exist
' or it opens the one if it does while iterating through.
If Dir(strWorkbookPathAndName) = "" Then
    Set xlWBk = ApXL.Workbooks.Add
    blnCreatedWkBk = True
Else
    Set xlWBk = ApXL.Workbooks.Open(strWorkbookPathAndName)
    blnCreatedWkBk = False
End If

    ApXL.Visible = True
    
    Set xlWSh = xlWBk.Worksheets.Add
    xlWSh.Name = strSheetName
 
    xlWSh.Range("A1").Select
 
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select

    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select

    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
 
' saves
If blnCreatedWkBk Then
   xlWBk.SaveAs strWorkbookPathAndName
Else
   xlWBk.Save
End If
   xlWBk.Close
ApXL.Quit
 
    rst.Close
    Set rst = Nothing
   Set ApXL = Nothing
    Exit Function

err_handler:
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function
End Function

This is the function that I am using as it came from Bob Larson the Forum Moderator

 

[TooManyReports]

Really could use the last bit of help here. I cannot seem to figure out what is giving that Expected 1 error. The error only seem to happen with the export module added, but I cannot see what is causing. I am re-posting the test access database with the export module added.

 

[nanscombe]

The Query OutputDataByArea_Output can't see the form field Sale_Area whilst the code is executing so ...

1) Create a function called outputDataArea, in a Module (the export one if allowable), and use that to pass through the value from the field instead.

Public Function outputDataArea()
outputDataArea = [Forms]![OutputDataByArea_Form]![Sale_Area]
End Function

2) Change the query to get the value from the function rather than directly from the form.

SELECT tblStoreInfo.Sale_Area, tblStoreInfo.Region, tblStoreInfo.Store, tblStoreInfo.City, 
tblStoreInfo.State, tblStoreInfo.Latitude, tblStoreInfo.Longitude
FROM tblStoreInfo
WHERE tblStoreInfo.Sale_Area=[B][Forms]![OutputDataByArea_Form]![Sale_Area][/B];

to

SELECT tblStoreInfo.Sale_Area, tblStoreInfo.Region, tblStoreInfo.Store, tblStoreInfo.City,
tblStoreInfo.State, tblStoreInfo.Latitude, tblStoreInfo.Longitude
FROM tblStoreInfo
WHERE tblStoreInfo.Sale_Area=[B]outputDataArea[/B]();

 

[TooManyReports]

Thanks so much! That did it.