By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Problems Inserting information from one table to another

P: 2
Would anyone know what exactly is causing the program to freeze when running this? Any tips on how i might get this to work would be appreciated too.

I am just trying to take information from one table and place it into another based on conditions. It is supposed to read and insert on a per row basis until it reaches EOF.



Private Sub Command2_Click()

Dim qdf As DAO.QueryDef
Dim recR As DAO.Recordset
Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim inv_number As String
Dim paid_amt As String
Dim bill_date As String



S = "SELECT Field1, Field2, Field3, Field4, Field5, Field6 FROM Table3 WHERE Table3.Field1='Bill Pmt -Check' Or Table3.Field1='Bill' Or Table3.Field1='Check'"


Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open S, cnn, adOpenDynamic

While Not rs.EOF

If rs("Field1") = "Bill Pmt -Check" Or rs("Field1") = "Bill" Then
temp1 = rs("Field2")
temp2 = rs("Field4")
temp3 = rs("Field3")

End If

If rs("Field1") = "Bill" Then
inv_number = rs("Field2")
paid_amt = rs("Field6")
bill_date = rs("Field3")
DoCmd.RunSQL "INSERT INTO WPR_labor serv_inv_num, asps_paid_station, asps_paid_station_date, asps_paid_station_date2, asps_paid_station_check VALUES ('" & inv_number & "', '" & paid_amt & "', '" & bill_date & "', '" & temp3 & "');"
End If

Wend
rs.Close
End Sub


Thanks ahead of time....
Mar 14 '07 #1
Share this Question
Share on Google+
2 Replies


ADezii
Expert 5K+
P: 8,627
Would anyone know what exactly is causing the program to freeze when running this? Any tips on how i might get this to work would be appreciated too.

I am just trying to take information from one table and place it into another based on conditions. It is supposed to read and insert on a per row basis until it reaches EOF.



Private Sub Command2_Click()

Dim qdf As DAO.QueryDef
Dim recR As DAO.Recordset
Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim inv_number As String
Dim paid_amt As String
Dim bill_date As String



S = "SELECT Field1, Field2, Field3, Field4, Field5, Field6 FROM Table3 WHERE Table3.Field1='Bill Pmt -Check' Or Table3.Field1='Bill' Or Table3.Field1='Check'"


Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open S, cnn, adOpenDynamic

While Not rs.EOF

If rs("Field1") = "Bill Pmt -Check" Or rs("Field1") = "Bill" Then
temp1 = rs("Field2")
temp2 = rs("Field4")
temp3 = rs("Field3")

End If

If rs("Field1") = "Bill" Then
inv_number = rs("Field2")
paid_amt = rs("Field6")
bill_date = rs("Field3")
DoCmd.RunSQL "INSERT INTO WPR_labor serv_inv_num, asps_paid_station, asps_paid_station_date, asps_paid_station_date2, asps_paid_station_check VALUES ('" & inv_number & "', '" & paid_amt & "', '" & bill_date & "', '" & temp3 & "');"
End If

Wend
rs.Close
End Sub


Thanks ahead of time....
From my limited understanding, Jet does not support the use of Dynamic Cursors. Jet never delivers Dynamic type Recordsets but other data sources used from an Access database may such as an SQL Server Table. Simply replace the following and let me know how you made out.
Expand|Select|Wrap|Line Numbers
  1. rs.Open S, cnn, adOpenDynamic
-------------------------- replace with --------------------------
Expand|Select|Wrap|Line Numbers
  1. rs.Open S, cnn, adOpenKeyset
Mar 14 '07 #2

P: 2
Run-time error '91':

Object variable of With block variable not set


This is the updated code:

Dim temp1 As String
Dim temp2 As String
Dim temp3 As String
Dim inv_number As String
Dim paid_amt As String
Dim bill_date As String



S = "SELECT Field1, Field2, Field3, Field4, Field5, Field6 FROM Table3 WHERE Table3.Field1='Bill Pmt -Check' Or Table3.Field1='Bill' Or Table3.Field1='Check'"


Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = CurrentProject.Connection
rs.Open S, cnn, adOpenKeyset

rs.Open S, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

While Not rs.EOF

If rs("Field1") = "Bill Pmt -Check" Or rs("Field1") = "Bill" Then
temp1 = rs("Field2")
temp2 = rs("Field4")
temp3 = rs("Field3")

End If

If rs("Field1") = "Bill" Then
inv_number = rs("Field2")
paid_amt = rs("Field6")
bill_date = rs("Field3")
DoCmd.RunSQL "INSERT INTO WPR_labor serv_inv_num, asps_paid_station, asps_paid_station_date, asps_paid_station_date2, asps_paid_station_check VALUES ('" & inv_number & "', '" & paid_amt & "', '" & bill_date & "', '" & temp3 & "', '" & temp1 & "');"
End If

Wend
rs.Close
Mar 15 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.