Linked Table To SQL

[EddieD]

My Data base code works fine with a local table "but"as soon as i Link a table to sql it's not running thru the code the same as it did before i made the move.
I first had a dbSeechanges error. I was able to get thru that.
now i am getting a [Run-Time error '3021' Either BOF or EOF is True, or the Current record has been deleted]
Can anyone help!
Here is my Code! I Will bold where the error accures
Dim connTL As ADODB.Connection
Dim rstTL As ADODB.Recordset
Dim rstTLIP As ADODB.Recordset
Dim boolC As Boolean
Dim lngLot As Long
boolCloseFlag = boolClose
Set connTL = New ADODB.Connection
Set rstTL = New ADODB.Recordset
Set rstTLIP = New ADODB.Recordset

SO = ShopOrderText
TestEquipID = TestEquipIDCombo
item = ItemText
DateV = DateText


sta1StChk = False
sta2StChk = False
sta3StChk = False
sta4StChk = False
sta5StChk = False

sta1EndChk = False
sta2EndChk = False
sta3SndChk = False
sta4EndChk = False
sta5EndChk = False


''Sub Routine Call
Set_Fields

''Sub Routine Call
Set_FieldColumns


FinTime = txtFinishTime
strOperator = OperatorText

If AddRecordPreCheck Then
If Station_Tech_Check Then
'If Station_Tech_Insert = True Then


With connTL

.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=big-sis;Initial Catalog=TesterLogSQL;Integrated Security=SSPI;"

.Open
.BeginTrans
End With

With rstTLIP
Select Case .State
Case 1
.Close
End Select


textLotNumber.SetFocus
lngLot = Val(textLotNumber.Text)
strSQLLot = "SELECT * FROM tbl_TesterLog_RecordInProcess WHERE ID_From_TL = " & lngLot
.Open strSQLLot, connTL, adOpenKeyset, adLockOptimistic
End With

If checkFromInProcessForm = False Then

With rstTL
Select Case .State
Case 1
.Close
End Select
str_TL_Lot = "SELECT * FROM TesterLog WHERE ID = " & lngLot
'.Open "TesterLog", connTL, adOpenKeyset, adLockOptimistic, adCmdTable
.Open str_TL_Lot, connTL, adOpenKeyset, adLockOptimistic



Debug Happens here! [Run-Time error '3021' Either BOF or EOF is True, or the Current record has been deleted]

!DateEntered = DateText
!ShopOrder = ShopOrderText
!Part = ItemText
!TestEquipID = TestEquipID
!Quadboard_1 = Q1
!Quad_1_Serial = Q1Ser
!Quad_1_CCXID = Q1CCXID
!Quadboard_2 = Q2
!Quad_2_Serial = Q2Ser
!Quad_2_CCXID = Q2CCXID
!Quadboard_3 = Q3
!Quad_3_Serial = Q3Ser
!Quad_3_CCXID = Q3CCXID
!Quadboard_4 = Q4
!Quad_4_Serial = Q4Ser
!Quad_4_CCXID = Q4CCXID
!Quadboard_5 = Q5
!Quad_5_Serial = Q5Ser
!Quad_5_CCXID = Q5CCXID
!Quadboard_6 = Q6
!Quad_6_Serial = Q6Ser
!Quad_6_CCXID = Q6CCXID
!Quadboard_7 = Q7
!Quad_7_Serial = Q7Ser
!Quad_7_CCXID = Q7CCXID
!Quadboard_8 = Q8
!Quad_8_Serial = Q8Ser
!Quad_8_CCXID = Q8CCXID
!Adapter_1 = A1
!Adapter_1_Serial = A1Ser
!Adapter_1_CCXID = A1CCXID
!Adapter_2 = A2
!Adapter_2_Serial = A2Ser
!Adapter_2_CCXID = A2CCXID
!Adapter_3 = A3
!Adapter_3_Serial = A3Ser
!Adapter_3_CCXID = A3CCXID
!Adapter_4 = A4
!Adapter_4_Serial = A4Ser
!Adapter_4_CCXID = A4CCXID
!Adapter_5 = A5
!Adapter_5_Serial = A5Ser
!Adapter_5_CCXID = A5CCXID
!Adapter_6 = A6
!Adapter_6_Serial = A6Ser
!Adapter_6_CCXID = A6CCXID
!Adapter_7 = A7
!Adapter_7_Serial = A7Ser
!Adapter_7_CCXID = A7CCXID
!Adapter_8 = A8
!Adapter_8_Serial = A8Ser
!Adapter_8_CCXID = A8CCXID
!ConnectionResistance = Resistance
If checkFromInProcessForm And boolCloseFlag Then
!In_Process_Flag = False
!In_Process_Qty = 0
!QtyPassed = QPassed
ElseIf boolCloseFlag Then
!In_Process_Flag = False
!In_Process_Qty = 0
!QtyPassed = QPassed
ElseIf checkFromInProcessForm Then
!In_Process_Flag = True
!In_Process_Qty = QPassed
Else
!In_Process_Flag = True
!In_Process_Qty = QPassed
End If

!QtyFailed = QFailed
!Point_of_Failure = PointOfFailure
!Operator = strOperator
.MoveFirst
.Update
.Close

End With

connTL.CommitTrans

With rstTL
Select Case .State
Case 1
.Close
End Select

.Open "TesterLog", connTL, adOpenKeyset, adLockOptimistic, adCmdTable
EndCountRecords = .RecordCount
End With

'If EndCountRecords = StartCountRecords + 1 Then
' MsgBox "Record added."
' Lock_Fields True
' checkRecordSaved = True
' Station_Tech_Insert
'Else
' MsgBox "Record NOT added."
' Lock_Fields False
'End If
checkRecordSaved = True
Lock_Fields True

Else
'if not from the in process form then


With rstTL
Select Case .State
Case 1
.Close
End Select

textLotNumber.SetFocus
lngLot = Val(textLotNumber.Text)
strSQLLot = "SELECT * FROM TesterLog WHERE ID = " & lngLot
.Open strSQLLot, connTL, adOpenKeyset, adLockOptimistic


If checkFromInProcessForm And boolCloseFlag Then
!In_Process_Flag = False
!QtyPassed = QPassed
!In_Process_Qty = 0
!QtyPassed = QPassed + Val(textQuantityToDate)
ElseIf Not checkFromInProcessForm And boolCloseFlag Then
!In_Process_Flag = False
!In_Process_Qty = 0
!QtyPassed = QPassed
ElseIf checkFromInProcessForm And Not boolCloseFlag Then
!In_Process_Flag = True
!In_Process_Qty = QPassed + Val(textQuantityToDate)
ElseIf Not checkFromInProcessForm And Not boolCloseFlag Then
!In_Process_Flag = True
!In_Process_Qty = QPassed + Val(textQuantityToDate)
End If
!Operator = strOperator
.Update
'!QtyFailed = QFailed
'!Point_Of_Failure = PointOfFailure
'
.MoveFirst
.Close

End With

connTL.CommitTrans
checkRecordSaved = True
'Station_Tech_Insert
updateTechRecord
With rstTL
Select Case .State
Case 1
.Close
End Select

.Open "TesterLog", connTL, adOpenKeyset, adLockOptimistic, adCmdTable
'EndCountRecords = .RecordCount
End With


'replace this with if record updated, not added
' If EndCountRecords = StartCountRecords + 1 Then
' MsgBox "Lot updated."
' Lock_Fields True

'Else
' MsgBox "Lot NOT updated."
' Lock_Fields False
' End If


End If


SOListBox.SetFocus

rstTL.Close
connTL.Close



End If



End If

Set rstTL = Nothing
Set connTL = Nothing

[MMcCarthy]

Just a few things I've noticed. When and if reposting use the code indicators and indent your code. It was very hard to follow the logic.

 
Dim connTL As ADODB.Connection
Dim rstTL As ADODB.Recordset
Dim rstTLIP As ADODB.Recordset
Dim boolC As Boolean
Dim lngLot As Long
boolCloseFlag = boolClose
Set connTL = New ADODB.Connection
Set rstTL = New ADODB.Recordset
Set rstTLIP = New ADODB.Recordset
 
SO = ShopOrderText
TestEquipID = TestEquipIDCombo
item = ItemText
DateV = DateText
 
sta1StChk = False
sta2StChk = False
sta3StChk = False
sta4StChk = False
sta5StChk = False
 
sta1EndChk = False
sta2EndChk = False
sta3SndChk = False
sta4EndChk = False
sta5EndChk = False
 
''Sub Routine Call
Set_Fields
 
''Sub Routine Call
Set_FieldColumns
 
FinTime = txtFinishTime
strOperator = OperatorText
 
If AddRecordPreCheck Then
If Station_Tech_Check Then
'If Station_Tech_Insert = True Then
 
With connTL
 
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=big-sis;Initial Catalog=TesterLogSQL;Integrated Security=SSPI;"
 
.Open
.BeginTrans
End With
 
With rstTLIP
Select Case .State
Case 1
.Close
End Select
 
textLotNumber.SetFocus
lngLot = Val(textLotNumber.Text)
strSQLLot = "SELECT * FROM tbl_TesterLog_RecordInProcess WHERE ID_From_TL = " & lngLot  ' this is not a text field is it?
 
 Debug.Print strSQLLot ' check the code is what you think it is
 
.Open strSQLLot, connTL, adOpenKeyset, adLockOptimistic
End With
 
 ' close the recordset and reset it before trying to use it again.
 rstTLIP.Close
 Set rstTLIP=Nothing
 
If checkFromInProcessForm = False Then
 
With rstTL
Select Case .State
Case 1
.Close
End Select
str_TL_Lot = "SELECT * FROM TesterLog WHERE ID = " & lngLot
 
 Debug.Print str_TL_Lot  '  check as aforementioned
 
.Open str_TL_Lot, connTL, adOpenKeyset, adLockOptimistic
 
 ' try this as a debug
 rstTL.MoveLast
 rstTL.MoveFirst
 If rstTL.RecordCount = 0 Then
     MsgBox "There are no records returned"
 End If
 
!DateEntered = DateText
!ShopOrder = ShopOrderText
!Part = ItemText
!TestEquipID = TestEquipID
!Quadboard_1 = Q1
!Quad_1_Serial = Q1Ser
!Quad_1_CCXID = Q1CCXID
!Quadboard_2 = Q2
!Quad_2_Serial = Q2Ser
!Quad_2_CCXID = Q2CCXID
!Quadboard_3 = Q3
!Quad_3_Serial = Q3Ser
!Quad_3_CCXID = Q3CCXID
!Quadboard_4 = Q4
!Quad_4_Serial = Q4Ser
!Quad_4_CCXID = Q4CCXID
!Quadboard_5 = Q5
!Quad_5_Serial = Q5Ser
!Quad_5_CCXID = Q5CCXID
!Quadboard_6 = Q6
!Quad_6_Serial = Q6Ser
!Quad_6_CCXID = Q6CCXID
!Quadboard_7 = Q7
!Quad_7_Serial = Q7Ser
!Quad_7_CCXID = Q7CCXID
!Quadboard_8 = Q8
!Quad_8_Serial = Q8Ser
!Quad_8_CCXID = Q8CCXID
!Adapter_1 = A1
!Adapter_1_Serial = A1Ser
!Adapter_1_CCXID = A1CCXID
!Adapter_2 = A2
!Adapter_2_Serial = A2Ser
!Adapter_2_CCXID = A2CCXID
!Adapter_3 = A3
!Adapter_3_Serial = A3Ser
!Adapter_3_CCXID = A3CCXID
!Adapter_4 = A4
!Adapter_4_Serial = A4Ser
!Adapter_4_CCXID = A4CCXID
!Adapter_5 = A5
!Adapter_5_Serial = A5Ser
!Adapter_5_CCXID = A5CCXID
!Adapter_6 = A6
!Adapter_6_Serial = A6Ser
!Adapter_6_CCXID = A6CCXID
!Adapter_7 = A7
!Adapter_7_Serial = A7Ser
!Adapter_7_CCXID = A7CCXID
!Adapter_8 = A8
!Adapter_8_Serial = A8Ser
!Adapter_8_CCXID = A8CCXID
!ConnectionResistance = Resistance
If checkFromInProcessForm And boolCloseFlag Then
!In_Process_Flag = False
!In_Process_Qty = 0
!QtyPassed = QPassed
ElseIf boolCloseFlag Then
!In_Process_Flag = False
!In_Process_Qty = 0
!QtyPassed = QPassed
ElseIf checkFromInProcessForm Then
!In_Process_Flag = True
!In_Process_Qty = QPassed
Else
!In_Process_Flag = True
!In_Process_Qty = QPassed
End If
 
!QtyFailed = QFailed
!Point_of_Failure = PointOfFailure
!Operator = strOperator
.MoveFirst
.Update
.Close
 
End With
 
connTL.CommitTrans
 
 ' close the recordset and reset it before trying to use it again.
 rstTL.Close
 Set rstTL=Nothing
 
With rstTL
Select Case .State
Case 1
.Close
End Select
 
.Open "TesterLog", connTL, adOpenKeyset, adLockOptimistic, adCmdTable
EndCountRecords = .RecordCount
End With
 
checkRecordSaved = True
Lock_Fields True
 
 ' close the recordset and reset it 
 rstTL.Close
 Set rstTL=Nothing
 
Else
'if not from the in process form then
 
With rstTL
Select Case .State
Case 1
.Close
End Select
 
textLotNumber.SetFocus
lngLot = Val(textLotNumber.Text)
strSQLLot = "SELECT * FROM TesterLog WHERE ID = " & lngLot
.Open strSQLLot, connTL, adOpenKeyset, adLockOptimistic
 
If checkFromInProcessForm And boolCloseFlag Then
!In_Process_Flag = False
!QtyPassed = QPassed
!In_Process_Qty = 0
!QtyPassed = QPassed + Val(textQuantityToDate)
ElseIf Not checkFromInProcessForm And boolCloseFlag Then
!In_Process_Flag = False
!In_Process_Qty = 0
!QtyPassed = QPassed
ElseIf checkFromInProcessForm And Not boolCloseFlag Then
!In_Process_Flag = True
!In_Process_Qty = QPassed + Val(textQuantityToDate)
ElseIf Not checkFromInProcessForm And Not boolCloseFlag Then
!In_Process_Flag = True
!In_Process_Qty = QPassed + Val(textQuantityToDate)
End If
!Operator = strOperator
.Update
'!QtyFailed = QFailed
'!Point_Of_Failure = PointOfFailure
'
.MoveFirst
.Close
 
End With
 
connTL.CommitTrans
checkRecordSaved = True
'Station_Tech_Insert
updateTechRecord
With rstTL
Select Case .State
Case 1
.Close
End Select
 
.Open "TesterLog", connTL, adOpenKeyset, adLockOptimistic, adCmdTable
'EndCountRecords = .RecordCount
End With
 
End If
 
SOListBox.SetFocus
 
rstTL.Close
connTL.Close
 
End If
End If
 
Set rstTL = Nothing
Set connTL = Nothing