470,573 Members | 1,769 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,573 developers. It's quick & easy.

Loop Errors in VB 6 and Access

Hi. I have a loop that was written for me by some outstanding programmers here (willakawill and killer42). It works great if I only select one record to check for duplicates. The user can pick on one form, 13 areas they want to schedule use on and on another form they do the same and can select all 9 areas. When they select multiple areas to schedule, my application goes into an infinite loop and keeps displaying the conflicts found dialog until I end task the program. Also, the start/stop date can span many days. Any help would be greatly appreciated. Here is the code

Expand|Select|Wrap|Line Numbers
  1. Dim db As ADODB.Connection
  2.  
  3. Set db = New ADODB.Connection
  4.  
  5. db.CursorLocation = adUseClient
  6.  
  7. db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scheduling\AAGTC_Scheduling.mdb;"
  8.  
  9. Dim adoprimaryrs1 As ADODB.Recordset
  10.  
  11. Dim adoprimaryrs2 As ADODB.Recordset
  12.  
  13. Set adoprimaryrs1 = New ADODB.Recordset
  14.  
  15. Set adoprimaryrs2 = New ADODB.Recordset
  16.  
  17. Dim SchedStart As Date
  18.  
  19. SchedStart = DateValue(frmGlobalForecast.txtDate_In.Text) + TimeValue(frmGlobalForecast.txtTime_In.Text)
  20.  
  21. Dim SchedEnd As Date
  22.  
  23. SchedEnd = DateValue(frmGlobalForecast.txtDate_Out.Text) + TimeValue(frmGlobalForecast.txtTime_Out.Text)
  24.  
  25. 'SQL select statement
  26. strSQL = "SELECT ForecastTable.Flight_Schedule, ForecastTable.Ground_Schedule, ForecastTable.Impact_AREAS_Used, " & _
  27. "ForecastTable.Land_Management_AREA_Used, ForecastTable.Environmental_Flight, ForecastTable.Land_Management_Area_Closures, " & _
  28. "ForecastTable.Document_ID, ForecastTable.Date_In, ForecastTable.Time_in, ForecastTable.Date_Out, ForecastTable.Time_Out, " & _
  29. "DateValue([Date_In])+TimeValue([Time_In]) AS DateTime_In, " & _
  30. "DateValue([Date_Out])+TimeValue([Time_Out]) AS DateTime_Out " & _
  31. "FROM ForecastTable " & _
  32. "WHERE (((DateValue([Date_In])+TimeValue([Time_In]))<=#" & _
  33. Format(SchedEnd, "mm/dd/yyyy hh:nn") & _
  34. "#) AND ((DateValue([Date_Out])+TimeValue([Time_Out]))>=#" & _
  35. Format(SchedStart, "mm/dd/yyyy hh:nn") & "#));"
  36.  
  37. Dim AllAREAS As String
  38. Dim AllAreas1 As String
  39. Dim AllAreas2 As String
  40. Dim AllAreas3 As String
  41.  
  42. 'Execute the first SQL statement to find conflicts, if any exist
  43. adoprimaryrs1.Open strSQL, db, adOpenStatic, adLockOptimistic
  44.  
  45. If adoprimaryrs1!Impact_Areas_Used <> "" Then
  46.     AREAS = adoprimaryrs1!Impact_Areas_Used
  47. End If
  48.  
  49. If adoprimaryrs1!Land_Management_Area_Used <> "" Then
  50.     AREAS1 = adoprimaryrs1!Land_Management_Area_Used
  51. End If
  52.  
  53. If adoprimaryrs1!Environmental_Flight <> "" Then
  54.     AREAS2 = adoprimaryrs1!Environmental_Flight
  55. End If
  56.  
  57. If adoprimaryrs1!Land_Management_Area_Closures <> "" Then
  58.     AREAS3 = adoprimaryrs1!Land_Management_Area_Closures
  59. End If
  60.  
  61. AllAREAS = AREAS & AREAS1 & txtLandMgmtAreaForecast.Text & txtSelectImpactArea.Text & AREAS2 & AREAS3
  62.  
  63.  
  64. Dim OuterLoop As Long
  65. Dim InnerLoop As Long
  66. Dim ar As Variant
  67.  
  68. ar = Split(AllAREAS, " ")
  69.  
  70. For OuterLoop = 0 To UBound(ar) - 1
  71.    For InnerLoop = OuterLoop + 1 To UBound(ar)
  72.       If StrComp(ar(OuterLoop), ar(InnerLoop)) = 0 Then
  73.          'we have a duplicate so name it
  74.         frmGlobalForecast.CheckAREA = 1
  75.         MsgBox "The Land Management AREA(s) and/or Impact AREA(s) " _
  76.                & " " & ar(OuterLoop) _
  77.                & " have already been scheduled for training use" _
  78.                , vbOKOnly, "Conflicting Schedule"
  79.       End If
  80.    Next InnerLoop
  81. Next OuterLoop
  82.  
Nov 30 '06 #1
4 1889
Killer42
8,435 Expert 8TB
Expand|Select|Wrap|Line Numbers
  1. ...
  2. For OuterLoop = 0 To UBound(ar) - 1
  3.    For InnerLoop = OuterLoop + 1 To UBound(ar)
  4.       If StrComp(ar(OuterLoop), ar(InnerLoop)) = 0 Then
  5.          'we have a duplicate so name it
  6.         frmGlobalForecast.CheckAREA = 1
  7.         MsgBox "The Land Management AREA(s) and/or Impact AREA(s) " _
  8.                & " " & ar(OuterLoop) _
  9.                & " have already been scheduled for training use" _
  10.                , vbOKOnly, "Conflicting Schedule"
  11.         Exit For
  12.       End If
  13.    Next InnerLoop
  14.   If frmGlobalForecast.CheckAREA = 1 Then
  15.     Exit For
  16.   End If
  17. Next OuterLoop
  18.  
As far as the "infinite loop" is concerned, it might be enough to Exit For as shown above. As you can see, I've also popped in some code to check whether the outer loop needs to stop. I hope I got your logic right.

Also, I was wondering: when you concatenate the various areas in string ALLAREAS, should you be inserting spaces between them? If they don't already have spaces there somewhere, then I wouldn't think the Split will find any spaces to split on.
Nov 30 '06 #2
Killer42,
Once again, thank you for sharing your knowledge to help me. Here is a piece of code from the impact area form.

Expand|Select|Wrap|Line Numbers
  1. If chkAlpha.Value = 1 Then
  2.         AlphaRange = "Alpha_Range "
  3.     Else
  4.         AlphaRange = ""
  5. End If
There are other modules that I use the split function in, except they have 2 spaces at the end because the split function looks for 2 trailing spaces as the identifier. I tried changing the loop split function to double spaces and then concatenated spaces between the areas fields, but then the loop never found any conflicts. The database I am using had some data that was "hand jammed" in it. So I am going to dump all of that data (it is just test data) and make sure all the data meets the specified criteria by running the application to repopulate the database. It does work the way it is if I only select 1 or 2 land management areas and/or impact areas to test for conflicts. I also do not quite understand how to trap the error that occurs when I test for conflict dates if the start and stop dates I use for the search criteria does not exist in the table. But I am going to try my very best to figure that out on my own. You have been so very helpful and I do not want to take up too much of your time. Thanks again for the help and I hope you have a wonderful holiday season.
Nov 30 '06 #3
Also, I really want to learn more about loops and arrays. Do you have any suggestions for online tutorials or books that I should buy?

Thanks!
Nov 30 '06 #4
Killer42
8,435 Expert 8TB
Also, I really want to learn more about loops and arrays. Do you have any suggestions for online tutorials or books that I should buy?
You could try the ones sashi pointed out in this thread: http://www.thescripts.com/forum/thread517434.html. I haven't had a chance to look at them, so don't know whether they're good or not.

If you use the search function to search thescripts for VISUAL BASIC TUTORIAL, I believe you'll find a few more mentioned.
Nov 30 '06 #5

Post your reply

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

Similar topics

2 posts views Thread by John Smith | last post: by
8 posts views Thread by Abby Lee | last post: by
2 posts views Thread by LostDeveloper via AccessMonster.com | last post: by
5 posts views Thread by oaklander | last post: by
5 posts views Thread by =?Utf-8?B?V2lsbGlhbSBGb3N0ZXI=?= | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.