Loop Errors in VB 6 and Access

[keithsimpson3973]

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

Dim db As ADODB.Connection

Set db = New ADODB.Connection

db.CursorLocation = adUseClient

db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Scheduling\AAGTC_Scheduling.mdb;"

Dim adoprimaryrs1 As ADODB.Recordset

Dim adoprimaryrs2 As ADODB.Recordset

Set adoprimaryrs1 = New ADODB.Recordset

Set adoprimaryrs2 = New ADODB.Recordset

Dim SchedStart As Date

SchedStart = DateValue(frmGlobalForecast.txtDate_In.Text) + TimeValue(frmGlobalForecast.txtTime_In.Text)

Dim SchedEnd As Date

SchedEnd = DateValue(frmGlobalForecast.txtDate_Out.Text) + TimeValue(frmGlobalForecast.txtTime_Out.Text)

'SQL select statement
strSQL = "SELECT ForecastTable.Flight_Schedule, ForecastTable.Ground_Schedule, ForecastTable.Impact_AREAS_Used, " & _
"ForecastTable.Land_Management_AREA_Used, ForecastTable.Environmental_Flight, ForecastTable.Land_Management_Area_Closures, " & _
"ForecastTable.Document_ID, ForecastTable.Date_In, ForecastTable.Time_in, ForecastTable.Date_Out, ForecastTable.Time_Out, " & _
"DateValue([Date_In])+TimeValue([Time_In]) AS DateTime_In, " & _
"DateValue([Date_Out])+TimeValue([Time_Out]) AS DateTime_Out " & _
"FROM ForecastTable " & _
"WHERE (((DateValue([Date_In])+TimeValue([Time_In]))<=#" & _
Format(SchedEnd, "mm/dd/yyyy hh:nn") & _
"#) AND ((DateValue([Date_Out])+TimeValue([Time_Out]))>=#" & _
Format(SchedStart, "mm/dd/yyyy hh:nn") & "#));"

Dim AllAREAS As String
Dim AllAreas1 As String
Dim AllAreas2 As String
Dim AllAreas3 As String

'Execute the first SQL statement to find conflicts, if any exist
adoprimaryrs1.Open strSQL, db, adOpenStatic, adLockOptimistic

If adoprimaryrs1!Impact_Areas_Used <> "" Then
    AREAS = adoprimaryrs1!Impact_Areas_Used
End If

If adoprimaryrs1!Land_Management_Area_Used <> "" Then
    AREAS1 = adoprimaryrs1!Land_Management_Area_Used
End If

If adoprimaryrs1!Environmental_Flight <> "" Then
    AREAS2 = adoprimaryrs1!Environmental_Flight
End If

If adoprimaryrs1!Land_Management_Area_Closures <> "" Then
    AREAS3 = adoprimaryrs1!Land_Management_Area_Closures
End If

AllAREAS = AREAS & AREAS1 & txtLandMgmtAreaForecast.Text & txtSelectImpactArea.Text & AREAS2 & AREAS3


Dim OuterLoop As Long
Dim InnerLoop As Long
Dim ar As Variant

ar = Split(AllAREAS, " ")

For OuterLoop = 0 To UBound(ar) - 1
   For InnerLoop = OuterLoop + 1 To UBound(ar)
      If StrComp(ar(OuterLoop), ar(InnerLoop)) = 0 Then
         'we have a duplicate so name it
        frmGlobalForecast.CheckAREA = 1
        MsgBox "The Land Management AREA(s) and/or Impact AREA(s) " _
               & " " & ar(OuterLoop) _
               & " have already been scheduled for training use" _
               , vbOKOnly, "Conflicting Schedule"
      End If
   Next InnerLoop
Next OuterLoop

[Killer42]

...
For OuterLoop = 0 To UBound(ar) - 1
   For InnerLoop = OuterLoop + 1 To UBound(ar)
      If StrComp(ar(OuterLoop), ar(InnerLoop)) = 0 Then
         'we have a duplicate so name it
        frmGlobalForecast.CheckAREA = 1
        MsgBox "The Land Management AREA(s) and/or Impact AREA(s) " _
               & " " & ar(OuterLoop) _
               & " have already been scheduled for training use" _
               , vbOKOnly, "Conflicting Schedule"
        [U][B]Exit For[/B][/U]
      End If
   Next InnerLoop
  [U][B]If frmGlobalForecast.CheckAREA = 1 Then[/B][/U]
    [U][B]Exit For[/B][/U]
  [U][B]End If[/B][/U]
Next OuterLoop

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.

[keithsimpson3973]

Killer42,
Once again, thank you for sharing your knowledge to help me. Here is a piece of code from the impact area form.

If chkAlpha.Value = 1 Then
        AlphaRange = "Alpha_Range "
    Else
        AlphaRange = ""
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.

[keithsimpson3973]

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!

[Killer42]

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.