{SOLVED} INSERT INTO trouble

[ineedahelp]

Does anyone see any problems with my INSERT INTO statement? I know that I have done something wrong, but can't figure it out. Basically, I am trying to create a table called LASTGOODDATA and add records to it with each loop through the program...in a sense writing my debug statement to the table. Any help (and code) would be greatly appreciated!! Thanks!!!


Private Sub cmbFindLastGoodData_Click()
'DoCmd.OpenReport ("rptLASTLSDATA")
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String

On Error GoTo Err_MyProc

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.Date, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
'Do Until rs2.EOF
' Debug.Print rs2!Symbol
' rs2.MoveNext
'Loop
rs1.MoveFirst
Do Until rs2.EOF
Do Until rs1.EOF
If rs2!Symbol = rs1!Symbol Then
If rs1!MarketPrice <> -5.25 Then
Debug.Print rs1!Date, rs1!Symbol, rs1!MarketPrice
INSERT INTO LastGoodData (Date, Symbol, MarketPrice) VALUES (" & rs1!Date & ", " & rs1!Symbol & ", " & rs1!MarketPrice & ")"
'rs1.MoveNext
Exit Do
Else
rs1.MoveNext
End If
Else
rs1.MoveNext
'Debug.Print rs1!Date, rs1!Symbol, rs1!MarketPrice
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

Exit_MyProc:
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
Exit Sub

Err_MyProc:
Resume Exit_MyProc

End Sub

[PEB]

Hi,

Try:

"INSERT INTO LastGoodData (Date, Symbol, MarketPrice) VALUES (#" & rs1!Date & "#, '" & rs1!Symbol & "', " & str(rs1!MarketPrice) & ")"

[MMcCarthy]

I am trying to create a table called LASTGOODDATA and add records to it with each loop through the program...in a sense writing my debug statement to the table. Any help (and code) would be greatly appreciated!! Thanks!!!

INSERT INTO statement will only work with an existing table the syntax for make table is different. However, you can't enter one record at a time or it will recreate the table every time.

Instead create the new table first. Add a command to delete all existing records before starting.

DoCmd.RunSQL "DELETE * FROM LastGoodData;"

You can now use your INSERT INTO statement to add new records.
BTW, you need to surround dates with # and text fields with single quotes. I'm assuming here that Symbol is a text field.

INSERT INTO LastGoodData (Date, Symbol, MarketPrice) VALUES (#" & rs1!Date & "#,'" & rs1!Symbol & "'," & rs1!MarketPrice & ");"

[ineedahelp]

thank you very much for the help!! It is working and more importantly I am understanding the code!! I have a code question though...I did get a message that said "object variable or with block variable not set" on the line that says "rs1.close". Have I used these statements correctly? I will include my code again:

Private Sub cmbFindLastGoodData_Click()
'DoCmd.OpenReport ("rptLASTLSDATA")
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String
Dim strINSERT As String

On Error GoTo Exit_MyProc

DoCmd.RunSQL "DELETE * FROM LastGoodData;"

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
rs1.MoveFirst
Do Until rs2.EOF
Do Until rs1.EOF
If rs2!Symbol = rs1!Symbol Then
If rs1!MarketPrice <> -5.25 Then
Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
Debug.Print strINSERT
db.Execute strINSERT, dbFailOnError
'INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (" & rs1!LocateDate & ", " & rs1!Symbol & ", " & rs1!MarketPrice & ")"
'Print .output; rs1!LocateDate, rs1!Symbol, rs1!MarketPrice;
'rs1.MoveNext
Exit Do
Else
rs1.MoveNext
End If
Else
rs1.MoveNext
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop
MsgBox ("Table updated successfully")
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

Exit_MyProc:
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
rs1.Close
rs2.Close

'Exit Sub

'Err_MyProc:
'Resume Exit_MyProc

End Sub

thank you for any additional help!!1

[MMcCarthy]

thank you very much for the help!! It is working and more importantly I am understanding the code!! I have a code question though...I did get a message that said "object variable or with block variable not set" on the line that says "rs1.close". Have I used these statements correctly? I will include my code again:

You don't need to close the recordsets and database twice.

Exit_MyProc: is not a statement that only happens on Exit it will happen anyway. It is just like a bookmark in the procedure to tell the code where to jump to.

Also you close the recordsets first and set the variables to nothing after.

 
 
Private Sub cmbFindLastGoodData_Click()
'DoCmd.OpenReport ("rptLASTLSDATA")
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String
Dim strINSERT As String
 
On Error GoTo Exit_MyProc
 
DoCmd.RunSQL "DELETE * FROM LastGoodData;"
 
strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"
 
strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"
 
Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")
 
rs2.MoveFirst
rs1.MoveFirst
Do Until rs2.EOF
  Do Until rs1.EOF
    If rs2!Symbol = rs1!Symbol Then
      If rs1!MarketPrice <> -5.25 Then
        Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
        strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
        Debug.Print strINSERT
        db.Execute strINSERT, dbFailOnError
        'INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (" & rs1!LocateDate & ", " & rs1!Symbol & ", " & rs1!MarketPrice & ")"
        'Print .output; rs1!LocateDate, rs1!Symbol, rs1!MarketPrice;
        'rs1.MoveNext
        Exit Do
      Else
        rs1.MoveNext
      End If
    Else
      rs1.MoveNext
    End If
  Loop
    rs2.MoveNext
    If Not rs2.EOF Then
      Debug.Print rs2!Symbol
    End If
Loop
 
MsgBox ("Table updated successfully")
 
Exit_MyProc:
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
 
'Exit Sub
 
'Err_MyProc:
'Resume Exit_MyProc
 
End Sub
 
 

[ineedahelp]

Thank you for the clarification on .close and bookmarks. For some reason...although my new data is being inserted correctly, my msgbox at the bottom of my code is not being executed. Is an error occuring towards the end of my looping to prevent this msgbox from executing? I can't seem to find any problems with the program. Thanks...I have again included NEW version of code.

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String
Dim strINSERT As String

On Error GoTo Exit_MyProc

DoCmd.RunSQL "DELETE * FROM LastGoodData;"

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
rs1.MoveFirst
Do Until rs2.EOF
Do Until rs1.EOF
If rs2!Symbol = rs1!Symbol Then
If rs1!MarketPrice <> -5.25 Then
Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
Debug.Print strINSERT
db.Execute strINSERT, dbFailOnError
Exit Do
Else 'only for -5.25 data
rs1.MoveNext
If rs1.EOF Then
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
Debug.Print strINSERT
db.Execute strINSERT, dbFailOnError
Exit Do
End If
End If
Else
rs1.MoveNext
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop
MsgBox ("Table updated successfully")

Exit_MyProc:
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

[MMcCarthy]

Take the brackets away. Brackets on any function implies that a return value is expected. In this case the default is vbOK. It should have given an error.

MsgBox "Table updated successfully"
Mary

Thank you for the clarification on .close and bookmarks. For some reason...although my new data is being inserted correctly, my msgbox at the bottom of my code is not being executed. Is an error occuring towards the end of my looping to prevent this msgbox from executing? I can't seem to find any problems with the program. Thanks...I have again included NEW version of code.

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String
Dim strINSERT As String

On Error GoTo Exit_MyProc

DoCmd.RunSQL "DELETE * FROM LastGoodData;"

strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
"FROM DailyPrice, TempSymbol " & _
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"

strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"

Set db = CurrentDb
Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset(strSQLTEMP)
'Set rs2 = db.OpenRecordset("TempSymbol")

rs2.MoveFirst
rs1.MoveFirst
Do Until rs2.EOF
Do Until rs1.EOF
If rs2!Symbol = rs1!Symbol Then
If rs1!MarketPrice <> -5.25 Then
Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
Debug.Print strINSERT
db.Execute strINSERT, dbFailOnError
Exit Do
Else 'only for -5.25 data
rs1.MoveNext
If rs1.EOF Then
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
Debug.Print strINSERT
db.Execute strINSERT, dbFailOnError
Exit Do
End If
End If
Else
rs1.MoveNext
End If
Loop
rs2.MoveNext
If Not rs2.EOF Then
Debug.Print rs2!Symbol
End If
Loop
MsgBox ("Table updated successfully")

Exit_MyProc:
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing

End Sub

[NeoPa]

Or use 'Call'.
I like Call as it enables me to use subroutines OR functions without worrying about the return value - when that's what I need.

[ineedahelp]

I have removed the parenthesis for the msgbox. It still does not display. I have 5 symbols that I loop through. The last symbol is a value of -5.25. I believe there is an error occuring while processing this symbols value that bounces me out to the error trapping and doesn't allow me to display the msgbox. could it be in my trying to db.execute the second time...

[code]
If rs1.EOF Then
MsgBox "rs1 eof"
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
Debug.Print strINSERT
db.Execute strINSERT, dbFailOnError
Exit Do
..... rest of code...

The msgbox "rs1 eof" DOES display! Any help?!!!

[MMcCarthy]

I've changed some of the code and commented out the On Error GoTo line.

This means that the code should stop running and go into debug mode when it hits the error. Click on the debug button and it should highlight the problem line in yellow which will tell us where to look.

 
'On Error GoTo Exit_MyProc
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQLTEMP As String
Dim strINSERT As String
    DoCmd.RunSQL "DELETE * FROM LastGoodData;"
 
    strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
    "FROM DailyPrice, TempSymbol " & _
    "WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
    "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"
 
    strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"
 
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(strSQL)
    Set rs2 = db.OpenRecordset(strSQLTEMP)
 
    rs2.MoveFirst
    rs1.MoveFirst
    Do Until rs2.EOF
        Do Until rs1.EOF
            If rs2!Symbol = rs1!Symbol Then
                If rs1!MarketPrice <> -5.25 Then
                    Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
                    strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
                    Debug.Print strINSERT
                    DoCmd.RunSQL strINSERT
                    rs1.MoveLast
                Else 'only for -5.25 data
                    rs1.MoveNext
                    If rs1.EOF Then
                        strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
                        Debug.Print strINSERT
                        DoCmd.RunSQL strINSERT
                    End If
                End If
            Else
                rs1.MoveNext
            End If
        Loop
        rs2.MoveNext
        If Not rs2.EOF Then
        Debug.Print rs2!Symbol
        End If
    Loop
    MsgBox "Table updated successfully"
 
Exit_MyProc:
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
End Sub
 
 

[ineedahelp]

Thank you so much for your help...it is finally working....your effort is much appreciated!

[MMcCarthy]

Thank you so much for your help...it is finally working....your effort is much appreciated!

You're welcome