Can't Return back to starting value in rotation

[firecomm911]

I have created a rotation log for selecting wreckers, when i get to the last wrecker it will not return back to the first wrecker in the rotation, I am missing something any HELP?

  'Setup variables for holding unique id place holder
 
    Dim tsLastRecordID As Integer
    Dim cfsLastRecordID As Integer
    Dim wrckLastRecordID As Integer
    Dim wrckFirstRecordID As Integer
    Dim tsLstWreckerID
    Dim cfsLstWreckerID
 
 
    'First Get all the last record's on the table ID
    'Getting Last Record I can check the columnd that contains the last used wrecker id
 
    tsLastRecordID = DMax("ID", "TS")
    csfLastRecordID = DMax("ID", "CFS")
    wrckLastRecordID = DMax("ID", "Wreckers")
 
    'If I don't get wrecker id I need the first one to get its name on the if statement...
    wrckFirstRecordID = DMin("ID", "wreckers")
 
 
    'Used the last ID from previous to get the WreckerID field..
    tsLstWreckerID = DLookup("[WreckerID]", "TS", "[ID] = " & tsLastRecordID)
    cfsLstWreckerID = DLookup("[WreckerID]", "CFS", "[ID] = " & csfLastRecordID)
 
    'Here we going to check what to display on the user screen
    'If there hasnt been a previous wrecker id thenwe starting from the first wrecker..
    If IsNull(tsLstWreckerID) And IsNull(cfsLstWreckerID) Then
 
        asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
    ElseIf IsNull(cfsLstWreckerID) Then
       If tsLstWreckerID = wrckLastRecordID Then
         asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
         Return 'Kills Operations
       End If
 
       tsLstWreckerID = tsLstWreckerID + 1
       asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & tsLstWreckerID)
 
    ElseIf IsNull(tsLstWreckerID) Then
        If cfsLstWreckerID = wrckLastRecordID Then
         asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
         Return 'Kills Operations
       End If
 
       cfsLstWreckerID = cfsLstWreckerID + 1
       asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & cfsLstWreckerID)
 
    ElseIf tsLstWreckerID < cfsLstWreckerID Then
 
        'If the last wrecker equals the last of the list then go back to one..
        If cfsLstWreckerID = wrckLastRecordID Then
           asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
           Return
        End If
 
        cfsLstWreckerID = cfsLstWreckerID + 1
        asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & cfsLstWreckerID)
    ElseIf cfsLstWreckerID < tsLstWreckerID Then
 
        'If the last wrecker equals the last of the list then go back to one..
        If tsLstWreckerID = wrckLastRecordID Then
           asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
           Return
        End If
        tsLstWreckerID = tsLstWreckerID + 1
        asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & tsLstWreckerID)
 
    End If

[twinnyfo]

firecomm911,

I think you may have to provide us a bit more information. Help us to understand 1) exactly what it is that you are trying to do and 2) tell us about your tables and how they are related. Your code appears to be way more complicated than it might have to be, and the constant usage of Domain Aggregate functions is typically not a great approach--although there are times when they serve a useful purpose.

Standing by to hepp, if we can get some more useful information.

[firecomm911]

I have 3 tables that I am using TS, CFS, and Wreckers. Those tables have a relationship between the ID field in wrecker table and the AssignedWrecker field in CFS and TS. What I would like to do is assign a wrecker to either CFS or TS and the next time I open either table it show me next wrecker available to pick up a vehicle to make sure i am fair on giving work to those wreckers.

I hope this helps. sorry for not being to clear

[NeoPa]

It seems like you need a conceptual understanding of the process.

1. Start with a table of wreckers. We would typically suggest a name like [tblWrecker] but [Wreckers] can work if that's what you already have.
2. [Wreckers] would need a PrimaryID field ([WreckerID] possibly.) as well as a separate field to indicate how many times it's been used. We'll name that [Usage] for now.
3. You need to update the [Usage] every time you retrieve an item so I'd suggest that SQL is possibly not the best approach for this. Instead I'll show how a Function can do both tasks in one go (It returns a [WreckerID] (Long)).

4. Public Sub NextWrecker() As Long
       Dim strSQL As String
       Dim dbVar As DAO.Database
    
       Set dbVar = CurrentDb()
       strSQL = Replace("SELECT   *%L" _
                      & "FROM     [Wreckers]%L" _
                      & "ORDER BY [Usage]" _
                      , "%L" vbNewLine)
       With dbVar.OpenRecordset(Name:=strSQL, Type:=dbOpenDynaset)
           NextWrecker = !WreckerID
           Call .Edit
           !Usage = !Usage + 1
           Call .Update
           Call .Close
       End With
   End Function

5. Call this function to return the next in your loop as well as to ensure it's been noted that this one has been used.

I hope that helps clarify the sort of process you should be looking at.