Hi,

I am using a query to insert records into a history table. The code below loops through my recordset. It worked before, but all of a sudden produces the following error on the rec.MoveNext line.

Run-time error 3071. This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I tried to put the insert statement into a variable but that didn't work. here is the while statement I'm using. Any help would be greatly appreciated.

While Not rec.EOF
        DoCmd.RunSQL "INSERT INTO HISTORY (BLI, BLI_DESCR, AUTH, FREEZE_DATE, APPEND_DATE) VALUES ('" & rec("BLI") & "', '" & rec("BLI_DESCR") & "', '" & rec("AUTH") & "', '" & rec("FREEZE_DATE") & "', DATE())"
        rec.MoveNext
        Count = Count + 1
    Wend

At a wild guess I would look to see if you are posibly violating a primary key in your table or posibly passing null values.

Thanks,
Jesse

You may also want to display your SQL statement after it's been built in your debug window. One of the most common errors is when apostrophes get embedded in your data values. I actually got this function from a Sam's book years ago - it looks something like this:

Public Function adhHandleQuotes(ByVal varValue As Variant, _
                                Optional strDelimiter As String = "'") As Variant

    adhHandleQuotes = _
     strDelimiter & _
     Replace(varValue, strDelimiter, strDelimiter & strDelimiter) & _
     strDelimiter

End Function

As can be seen above, the second parameter (strDelimiter) is optional and defaults to a single quote. Using string concatenation for readability, here's how it would look using the new function:

Dim strSQL As String

While Not rec.EOF
   strSQL = ""
   strSQL = strSQL & "INSERT INTO HISTORY "
   strSQL = strSQL & "(BLI, BLI_DESCR, AUTH, FREEZE_DATE, APPEND_DATE) "
   strSQL = strSQL & "VALUES ( "
   strSQL = strSQL & adhHandleQuotes(rec("BLI")) & ", "
   strSQL = strSQL & adhHandleQuotes(rec("BLI_DESCR")) & ", "
   strSQL = strSQL & adhHandleQuotes(rec("AUTH")) & ", "
   strSQL = strSQL & adhHandleQuotes(rec("FREEZE_DATE"), "'") & ", "
   strSQL = strSQL & adhHandleQuotes(Date(), "'")
   strSQL = strSQL & ")"
   DoCmd.RunSQL strSQL
   rec.MoveNext
   Count = Count + 1
Wend

I included the second function argument for the FREEZE_DATE and APPEND_DATE in case the date delimiter is a single quote instead of a pound sign.

I use the function for ALL of my string-related processing when inserting new or updating existing rows in a table.