Hello Everyone! again... lol
I have the following code:
Code:
Dim Number5 As String Dim Number7 As String
Dim Number9 As String
Dim MiddleNumberstoMatch As Integer
Dim MatchCheck As Integer
Dim i As Integer
Dim SQL5 As String
Dim SQL7 As String
Dim SQL9 As String
Dim RS5 As Recordset
Dim RS7 As Recordset
Dim RS9 As Recordset
SQL5 = "SELECT " + SelectedTable + ".Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM " + SelectedTable + " WHERE Right([Numbers],1)='5' ORDER BY Numbers;"
SQL7 = "SELECT " + SelectedTable + ".Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM " + SelectedTable + " WHERE Right([Numbers],1)='7' ORDER BY Numbers;"
SQL9 = "SELECT " + SelectedTable + ".Numbers, Val(Mid(Numbers,2)) AS JustNumbers, Val(LEFT(JustNumbers, len(JustNumbers)-1)) AS Numbers_to_Match FROM " + SelectedTable + " WHERE Right([Numbers],1)='9' ORDER BY Numbers;"
Set RS5 = CurrentDb.OpenRecordset(SQL5)
Set RS7 = CurrentDb.OpenRecordset(SQL7)
Set RS9 = CurrentDb.OpenRecordset(SQL9)
RS5.OpenRecordset
RS5.MoveFirst
If Not RS5.EOF Then
ResetLabel:
MiddleNumberstoMatch = RS5!Numbers_to_match
Number5 = RS5!Numbers
RS7.MoveFirst
If Not RS7.EOF Then
ResetLabel7:
MatchCheck = RS7!Numbers_to_match
If MatchCheck = MiddleNumberstoMatch Then
Number7 = RS7!Numbers
RS9.MoveFirst
If Not RS9.EOF Then
ResetLabel9:
MatchCheck = RS9!Numbers_to_match
If MatchCheck = MiddleNumberstoMatch Then
Number9 = RS9!Numbers
'Me.txtSwitchNumber = Number5
Me.txtSwitchNumber1 = Number5
Me.txtSwitchNumber2 = Number7
Me.txtSwitchNumber3 = Number9
RS5.Close
RS7.Close
RS9.Close
Else
RS9.MoveNext
GoTo ResetLabel9
End If
Else
RS5.MoveNext
GoTo ResetLabel
End If
Else
RS7.MoveNext
GoTo ResetLabel7
End If
Else
RS5.MoveNext
GoTo ResetLabel
End If
Else
RS5.Close
RS7.Close
RS9.Close
MsgBox "There are no matching records in the recordset that meet the criteria."
End If
So, the above code trips on line: MatchCheck = RS7!Numbers_to_match
There are definitely records that match. Can anyone see why this would pull such an error? I am about to pull out the rest of my hair. Thank you in advance!