By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,495 Members | 1,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,495 IT Pros & Developers. It's quick & easy.

Can't Return back to starting value in rotation

P: 3
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?

Expand|Select|Wrap|Line Numbers
  1.   'Setup variables for holding unique id place holder
  2.  
  3.     Dim tsLastRecordID As Integer
  4.     Dim cfsLastRecordID As Integer
  5.     Dim wrckLastRecordID As Integer
  6.     Dim wrckFirstRecordID As Integer
  7.     Dim tsLstWreckerID
  8.     Dim cfsLstWreckerID
  9.  
  10.  
  11.     'First Get all the last record's on the table ID
  12.     'Getting Last Record I can check the columnd that contains the last used wrecker id
  13.  
  14.     tsLastRecordID = DMax("ID", "TS")
  15.     csfLastRecordID = DMax("ID", "CFS")
  16.     wrckLastRecordID = DMax("ID", "Wreckers")
  17.  
  18.     'If I don't get wrecker id I need the first one to get its name on the if statement...
  19.     wrckFirstRecordID = DMin("ID", "wreckers")
  20.  
  21.  
  22.     'Used the last ID from previous to get the WreckerID field..
  23.     tsLstWreckerID = DLookup("[WreckerID]", "TS", "[ID] = " & tsLastRecordID)
  24.     cfsLstWreckerID = DLookup("[WreckerID]", "CFS", "[ID] = " & csfLastRecordID)
  25.  
  26.     'Here we going to check what to display on the user screen
  27.     'If there hasnt been a previous wrecker id thenwe starting from the first wrecker..
  28.     If IsNull(tsLstWreckerID) And IsNull(cfsLstWreckerID) Then
  29.  
  30.         asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
  31.     ElseIf IsNull(cfsLstWreckerID) Then
  32.        If tsLstWreckerID = wrckLastRecordID Then
  33.          asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
  34.          Return 'Kills Operations
  35.        End If
  36.  
  37.        tsLstWreckerID = tsLstWreckerID + 1
  38.        asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & tsLstWreckerID)
  39.  
  40.     ElseIf IsNull(tsLstWreckerID) Then
  41.         If cfsLstWreckerID = wrckLastRecordID Then
  42.          asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
  43.          Return 'Kills Operations
  44.        End If
  45.  
  46.        cfsLstWreckerID = cfsLstWreckerID + 1
  47.        asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & cfsLstWreckerID)
  48.  
  49.     ElseIf tsLstWreckerID < cfsLstWreckerID Then
  50.  
  51.         'If the last wrecker equals the last of the list then go back to one..
  52.         If cfsLstWreckerID = wrckLastRecordID Then
  53.            asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
  54.            Return
  55.         End If
  56.  
  57.         cfsLstWreckerID = cfsLstWreckerID + 1
  58.         asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & cfsLstWreckerID)
  59.     ElseIf cfsLstWreckerID < tsLstWreckerID Then
  60.  
  61.         'If the last wrecker equals the last of the list then go back to one..
  62.         If tsLstWreckerID = wrckLastRecordID Then
  63.            asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & wrckFirstRecordID)
  64.            Return
  65.         End If
  66.         tsLstWreckerID = tsLstWreckerID + 1
  67.         asswrecker.Value = DLookup("[Wrecker Company]", "Wreckers", "[ID] = " & tsLstWreckerID)
  68.  
  69.     End If
Feb 17 '20 #1
Share this Question
Share on Google+
2 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,385
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.
Feb 18 '20 #2

P: 3
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
Feb 18 '20 #3

Post your reply

Sign in to post your reply or Sign up for a free account.