Please Help Runtime Errors :[

[jamesgarf]

Hi all, I am having some serious issues. I had a form that worked perfectly, but now I need to change the field names (add and remove some)... I keep getting Runtime Error 3075 stating excess "(" I seriously cannot figure this out. I am really new to access but if you have any thoughts, any and all are greatly appreciated

Private Sub port_2_Click()
'On Error GoTo Err_port_2_Click
Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim xlApp As Object
    Dim wkb As Object
    Dim rng As Object
    Dim strExcelFile As String
    Dim strTable As String
    Dim SQLname As String
    Dim iCol As Integer
    Dim rowsToReturn As Integer
    Dim objSheet As Object
    Dim tblrst As DAO.Recordset
     
    DoCmd.SetWarnings False
    
    Set db = CurrentDb '<<<Connect to this currently open database
    


        DoCmd.RunSQL SQLname
        
        'Update With new Sales data


        SQLname = "INSERT INTO Table1 ( RVP, DM, SalesRep, Rep#, Location, MasterNumber, CustomerName, Job, JobName, Invoice, InvoicePostDate, Sales, Cost, GP$, GM%) " & _
                "SELECT Test.RVP, Test.DM, Test.SalesRep, Test.Rep#, Test.Location, Test.MasterNumber, Test.CustomerName, Test.Job, Test.JobName, Test.Invoice, Test.InvoicePostDate, Test.Sales, Test.GP$, Test.GM% " & _
                "Test;"


        DoCmd.RunSQL SQLname
        
'connect to Salesman table
    Set tblrst = db.OpenRecordset("Salesmen_tbl")
     
    tblrst.MoveFirst '<<<move to the first record of primary table
     
    Do Until tblrst.EOF '<<<Loop commands until end of recordset reached
    
         'SQL statement used to query chosen records in data table
         
        strTable = "SELECT Table1.* FROM Table1 INNER JOIN Salesmen_tbl " _
        & "ON Table1.Rep# = Salesmen_tbl.Sales_numb " _
        & "WHERE (([Salesmen_tbl.Sales_numb]= " & tblrst!sales_numb & "));"       '^<<<You must use a Bang (!) between tblrst & field name to get the field's value
         
'<<<Creates new excel file named for current primary table's field value
        
        strExcelFile = "C:\Users\xxxxxx\Desktop\Testing Folder" & tblrst!sales_numb & "_commission_08_2012_" & tblrst!Salesperson & "_"
        
        Set rst = db.OpenRecordset(strTable) '<<<Open query results from data table for primary table's current field value
         
         'get number of records in recordset
        If rst.EOF = False And rst.BOF = False Then
         
        rst.MoveLast '<<<Required to get total number of records in recordset
        rowsToReturn = rst.RecordCount
        rst.MoveFirst '<<<Move back to start of recordset
         '^ if MoveFirst is not used then only last record will be gathered


         
        
            If rowsToReturn <= rst.RecordCount Then '<<<Do as long as there are records to work
        
             'set reference to Excel to make Excel visible
            Set xlApp = CreateObject("Excel.Application")
            xlApp.Application.Visible = False '<<<Make Excel invisible to user
            
             'set references to workbook and worksheet
            Set wkb = xlApp.Workbooks.Add '<<<Create new workbook
            Set objSheet = xlApp.ActiveWorkbook.Sheets(1) '<<<Add worksheet to workbook
             
             'write column names to the first worksheet row
            For iCol = 0 To rst.Fields.Count - 1
                objSheet.Cells(1, iCol + 1).Value = rst.Fields(iCol).Name
            Next
             
             'specify cell range to receive data
            Set rng = objSheet.Cells(2, 1)
            
             'copy specified number of records to worksheet
            rng.CopyFromRecordset rst, rowsToReturn
            
             'autofit columns to make data fit
            objSheet.Columns.AutoFit
             
             'close the workbook
            wkb.SaveAs FileName:=strExcelFile '<<<Save as employee's name
            wkb.Close '<<<Close workbook because we are done with it
             
             'quit excel and release object variables
            Set objSheet = Nothing
            Set wkb = Nothing
            xlApp.Quit
            Set xlApp = Nothing
        End If
       End If
        tblrst.MoveNext '<<<Move to next Salesman
        
    Loop
     
DoCmd.SetWarnings True


    MsgBox "Finished creating Daily Sales reports!"
     
     'close database connection
    tblrst.Close
    Set tblrst = Nothing
    rst.Close
    Set rst = Nothing
    db.Close
    Set db = Nothing




Exit_port_2_Click:
    Exit Sub


Err_port_2_Click:
    MsgBox Err.Description
    Resume Exit_port_2_Click
    
End Sub

[June7]

Yellow highlight text is hard to read.

Which line triggers error? I don't see any parens that are wrong.

Think need a comma: ", Test;"

You are using special characters and punctuation in names. As this might cause issues, I advise not to but since you are, enclose in [].

[jamesgarf]

Thanks for the reply...The error occurs in the first red text, but the debug takes me to the second red text that I notated

[June7]

Nothing wrong with the parens or brackets but they are not needed. Assume Rep# and Sales_numb are number datatype.

I see nothing wrong with the SQL. Step debug, set breakpoint and Debug.Print the sql string. Does it look right?

You don't pull any fields from Salesmen_tbl. Why join tables? Can't you just filter on Rep# since they are the same values as Sales_numb?