Problems Inserting information from one table to another

[sad2891]

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....

[ADezii]

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.

rs.Open S, cnn, adOpenDynamic

-------------------------- replace with --------------------------

rs.Open S, cnn, adOpenKeyset

[sad2891]

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