Ugh - No Current Record 3021

[AccessPower]

Hello Everyone! again... lol

I have the following 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!

[pbaldy]

You can use this to debug:

BaldyWeb-Immediate window

You don't need the MoveFirst, and it will error on an empty recordset.

[AccessPower]

pbaldy,

Thank you for the assistance. I looked at the immediate window. It seems as though it has iterated through the entire recordset. It is funny though, because it works for a few tables, but not others. It is not pulling up any records. I'm going to have to see if it has something to do with the left join used.

[AccessPower]

Update:

Debug.Print MiddleNumberstoMatch & " - " & MatchCheck

Show that the code is stopping at 002 - 999

This means that the middlenumbertomatch (Or in other words RS5.Numbers_to_match) isn't going to to the next record. This is the point of these loops nested within the loops. I don't know why the loop stops. It really should end up 999 - 999. There is only one record in RS5 that has the middle numbers of 002. Can anyone see why the loop isn't working?

This should function like a brute force mechanism.

[AccessPower]

I also tried this approach. There is quite a bit less code. It is looking for everything between a letter and the last number and making sure there is a sequence of three numbers with endings of 5,7,9

Dim Number5 As String
Dim Number7 As String
Dim Number9 As String


Dim i As Integer
Dim MiddleNumberstoMatch As String


Dim SQL5 As String
Dim SQL7 As String
Dim SQL9 As String


Dim RS5 As DAO.Recordset
Dim RS7 As DAO.Recordset
Dim RS9 As DAO.Recordset


SQL5 = "SELECT " + SelectedTable + ".Numbers, Val(Mid(Numbers,2)) AS JustNumbers, LEFT(JustNumbers, len(JustNumbers)-1) AS Numbers_to_Match FROM " + SelectedTable + " LEFT JOIN qryMPCSwitches5 ON " + SelectedTable + ".Numbers = qryMPCSwitches5.LOC_NUM WHERE Right([Numbers],1)='5';"




Set RS5 = CurrentDb.OpenRecordset(SQL5)
Set RS9 = CurrentDb.OpenRecordset(SQL9)
   
RS5.OpenRecordset
RS5.MoveFirst




ResetLabel:
If Not RS5.EOF Then
    MiddleNumberstoMatch = RS5!Numbers_to_match
    Number5 = RS5!Numbers


    SQL7 = "SELECT " + SelectedTable + ".Numbers, Val(Mid(Numbers,2)) AS JustNumbers, LEFT(JustNumbers, len(JustNumbers)-1) AS Numbers_to_Match FROM " + SelectedTable + " LEFT JOIN qryMPCSwitches5 ON " + SelectedTable + ".Numbers = qryMPCSwitches5.LOC_NUM WHERE Numbers LIKE '*" & MiddleNumberstoMatch & "7';"
    Set RS7 = CurrentDb.OpenRecordset(SQL7)
    Debug.Print SQL7


    If Not RS7.EOF Then
        Number7 = RS7!Numbers
        SQL9 = "SELECT " + SelectedTable + ".Numbers, Val(Mid(Numbers,2)) AS JustNumbers, LEFT(JustNumbers, len(JustNumbers)-1) AS Numbers_to_Match FROM " + SelectedTable + " LEFT JOIN qryMPCSwitches5 ON " + SelectedTable + ".Numbers = qryMPCSwitches5.LOC_NUM WHERE Numbers LIKE '*" & MiddleNumberstoMatch & "9';"
        Set RS9 = CurrentDb.OpenRecordset(SQL9)
        
        If Not RS9.EOF Then
            Number9 = RS9!Numbers
            MsgBox "We found a match!"
        Else
                    RS5.MoveNext
                    GoTo ResetLabel
        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