Error 3075..aannd Project Deadline Help Plz?

[jrub]

HI All,

Trying to get a a fancy query going though some VBA code.....everything worked, until someone put a pile of text in a comment box in one of the records, which forms parts of the query search.

When I run the below code, I get error 3075: Syntax Error Missing Operator.

Option Compare Database

Private Sub Command5_Click()
On Error GoTo CEYerr

Dim intYearProgress, intEntryID, intRecCount, intProgBarAt, intProgBarTot, intMaxYear As Integer
Dim rs As Object
Dim strSql As String
Dim con As Object
Dim blnLifeAdj As Boolean

'Clear current entries

strSql = "DELETE [Expenditures].* FROM [Expenditures];"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
                
'set up variables to create connection to databse and temp recordset
Set con = Application.CurrentProject.Connection
strSql = "SELECT * FROM [ReserveItems] ORDER BY [ReserveItemID];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSql, con, 1
intProgBarTot = DCount("*", "[ReserveItems]")
intProgBarAt = 0
intMaxYear = DMax("[OriginalYearBuilt] + [LifeExpectancy]", "[ReserveItems]")

intEntryID = 1

If (rs.EOF) Then 'if no data in table
    MsgBox "There is no data in the table.", vbInformation, "Calculate Expenditure Years"
Else
    While Not (rs.EOF) 'whilst not End Of File
      If (rs!LifeExpectancy > 0) And (rs!TotalCost > 0) Then
      '  If (rs!OriginalYearBuilt >= Me.tbFromYear) And (rs!OriginalYearBuilt < intMaxYear) Then ' OYB starts during the specified period for Analysis
           'set variable to check running year
            intYearProgress = rs!OriginalYearBuilt
            While intYearProgress < intMaxYear
            
                'create new entry in Expenditure Years
                strSql = "INSERT INTO [Expenditures] ([ExpenditureID]) VALUES (" & intEntryID & ");"
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSql
                'MsgBox strSQL
                DoCmd.SetWarnings True
                
                strSql = "UPDATE [Expenditures] SET [CEY] = '" & intYearProgress & "' WHERE [ExpenditureID] = " & intEntryID & ";"
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSql
                DoCmd.SetWarnings True
                    
                For intRecordCount = 0 To rs.Fields.Count - 1 'loop through each field and copy
                    strSql = "UPDATE [Expenditures] SET [" & rs.Fields(intRecordCount).Name & "] = '" & rs.Fields(intRecordCount).Value & "' WHERE [ExpenditureID] = " & intEntryID & ";"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSql
                    DoCmd.SetWarnings True
                Next intRecordCount
                
                'update variable for running year
                If (rs!LifeAdjustment > 0) And blnLifeAdj = False Then
                    blnLifeAdj = True
                    intYearProgress = intYearProgress + rs!LifeAdjustment
                End If
                intYearProgress = intYearProgress + rs!LifeExpectancy
                intEntryID = intEntryID + 1
            Wend
            
            blnLifeAdj = False
       
        End If
        rs.MoveNext
        intProgBarAt = intProgBarAt + 1
        Me.lblProgIn.Width = ((intProgBarTot / 100) * intProgBarAt) * 22
        Me.Repaint
    Wend
End If

MsgBox "Analysis complete", vbInformation, "Calculate Expenditure Years"
'DoCmd.OpenTable "Expenditures"
'DoCmd.Close acForm, Me.Name

DoCmd.SetWarnings False
DoCmd.OpenQuery "MakeFundingPlan", acViewNormal, acEdit
DoCmd.SetWarnings True




CEYExit:

    Set rs = Nothing
    Set con = Nothing
    Exit Sub
CEYerr:
    
    MsgBox Err.Number & vbLf & Err.Description
    GoTo CEYExit

End Sub

I think the error occurs below, as I am able to get only one record in the results, with all of the data except for two comments boxes.

For intRecordCount = 0 To rs.Fields.Count - 1 'loop through each field and copy
                    strSql = "UPDATE [Expenditures] SET [" & rs.Fields(intRecordCount).Name & "] = '" & rs.Fields(intRecordCount).Value & "' WHERE [ExpenditureID] = " & intEntryID & ";"
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSql
                    DoCmd.SetWarnings True
                Next intRecordCount

 

[jrub]

Ah, this was the money shot:
Link

That fixed some of the text recorded, but failed again when it came to another comment that used " and ".

So I replaced them with ( and ) until I figure out more.