Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

{SOLVED} INSERT INTO trouble

Member
  
Join Date: Sep 2006
Posts: 98
#1: Nov 10 '06
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's Avatar
PEB PEB is offline
Expert
  
Join Date: Aug 2006
Location: Bulgaria
Posts: 1,380
#2: Nov 11 '06

re: {SOLVED} INSERT INTO trouble

Hi,

Try:

"INSERT INTO LastGoodData (Date, Symbol, MarketPrice) VALUES (#" & rs1!Date & "#, '" & rs1!Symbol & "', " & str(rs1!MarketPrice) & ")"
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#3: Nov 12 '06

re: {SOLVED} INSERT INTO trouble

Quote:

Originally Posted by ineedahelp


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 & ");"
Member
  
Join Date: Sep 2006
Posts: 98
#4: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

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
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#5: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

Quote:

Originally Posted by 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:

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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Private Sub cmbFindLastGoodData_Click()
  4. 'DoCmd.OpenReport ("rptLASTLSDATA")
  5. Dim db As Database
  6. Dim rs1 As DAO.Recordset
  7. Dim rs2 As DAO.Recordset
  8. Dim strSQL As String
  9. Dim strSQLTEMP As String
  10. Dim strINSERT As String
  11.  
  12. On Error GoTo Exit_MyProc
  13.  
  14. DoCmd.RunSQL "DELETE * FROM LastGoodData;"
  15.  
  16. strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
  17. "FROM DailyPrice, TempSymbol " & _
  18. "WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
  19. "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"
  20.  
  21. strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"
  22.  
  23. Set db = CurrentDb
  24. Set rs1 = db.OpenRecordset(strSQL)
  25. Set rs2 = db.OpenRecordset(strSQLTEMP)
  26. 'Set rs2 = db.OpenRecordset("TempSymbol")
  27.  
  28. rs2.MoveFirst
  29. rs1.MoveFirst
  30. Do Until rs2.EOF
  31.   Do Until rs1.EOF
  32.     If rs2!Symbol = rs1!Symbol Then
  33.       If rs1!MarketPrice <> -5.25 Then
  34.         Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
  35.         strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
  36.         Debug.Print strINSERT
  37.         db.Execute strINSERT, dbFailOnError
  38.         'INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (" & rs1!LocateDate & ", " & rs1!Symbol & ", " & rs1!MarketPrice & ")"
  39.         'Print .output; rs1!LocateDate, rs1!Symbol, rs1!MarketPrice;
  40.         'rs1.MoveNext
  41.         Exit Do
  42.       Else
  43.         rs1.MoveNext
  44.       End If
  45.     Else
  46.       rs1.MoveNext
  47.     End If
  48.   Loop
  49.     rs2.MoveNext
  50.     If Not rs2.EOF Then
  51.       Debug.Print rs2!Symbol
  52.     End If
  53. Loop
  54.  
  55. MsgBox ("Table updated successfully")
  56.  
  57. Exit_MyProc:
  58. rs1.Close
  59. rs2.Close
  60. Set rs1 = Nothing
  61. Set rs2 = Nothing
  62. Set db = Nothing
  63.  
  64. 'Exit Sub
  65.  
  66. 'Err_MyProc:
  67. 'Resume Exit_MyProc
  68.  
  69. End Sub
  70.  
  71.  
Member
  
Join Date: Sep 2006
Posts: 98
#6: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

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
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#7: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

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

Quote:

Originally Posted by 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
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,737
#8: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

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.
Member
  
Join Date: Sep 2006
Posts: 98
#9: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

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?!!!
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#10: Nov 13 '06

re: {SOLVED} INSERT INTO trouble

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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'On Error GoTo Exit_MyProc
  3. Dim db As Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6. Dim strSQL As String
  7. Dim strSQLTEMP As String
  8. Dim strINSERT As String
  9.     DoCmd.RunSQL "DELETE * FROM LastGoodData;"
  10.  
  11.     strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
  12.     "FROM DailyPrice, TempSymbol " & _
  13.     "WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
  14.     "ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"
  15.  
  16.     strSQLTEMP = "SELECT Symbol FROM TempSymbol ORDER BY Symbol ASC;"
  17.  
  18.     Set db = CurrentDb
  19.     Set rs1 = db.OpenRecordset(strSQL)
  20.     Set rs2 = db.OpenRecordset(strSQLTEMP)
  21.  
  22.     rs2.MoveFirst
  23.     rs1.MoveFirst
  24.     Do Until rs2.EOF
  25.         Do Until rs1.EOF
  26.             If rs2!Symbol = rs1!Symbol Then
  27.                 If rs1!MarketPrice <> -5.25 Then
  28.                     Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
  29.                     strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
  30.                     Debug.Print strINSERT
  31.                     DoCmd.RunSQL strINSERT
  32.                     rs1.MoveLast
  33.                 Else 'only for -5.25 data
  34.                     rs1.MoveNext
  35.                     If rs1.EOF Then
  36.                         strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
  37.                         Debug.Print strINSERT
  38.                         DoCmd.RunSQL strINSERT
  39.                     End If
  40.                 End If
  41.             Else
  42.                 rs1.MoveNext
  43.             End If
  44.         Loop
  45.         rs2.MoveNext
  46.         If Not rs2.EOF Then
  47.         Debug.Print rs2!Symbol
  48.         End If
  49.     Loop
  50.     MsgBox "Table updated successfully"
  51.  
  52. Exit_MyProc:
  53.     rs1.Close
  54.     rs2.Close
  55.     Set rs1 = Nothing
  56.     Set rs2 = Nothing
  57.     Set db = Nothing
  58. End Sub
  59.  
  60.  
Member
  
Join Date: Sep 2006
Posts: 98
#11: Nov 14 '06

re: {SOLVED} INSERT INTO trouble

Thank you so much for your help...it is finally working....your effort is much appreciated!
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#12: Nov 14 '06

re: {SOLVED} INSERT INTO trouble

Quote:

Originally Posted by ineedahelp

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

You're welcome
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved