Using arrays to simplify this code

[keithsimpson3973]

Is there was a better way to do the following code that is currently in my project? Thanks

Dim db As ADODB.Connection

Set db = New ADODB.Connection

db.CursorLocation = adUseClient

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

Dim adoprimaryrs1 As ADODB.Recordset

Set adoprimaryrs1 = New ADODB.Recordset

Dim strSQL As String

'SQL select statement

strSQL = "SELECT [Avon Park Air Scheduling Post Data].Canceled FROM [Avon Park Air Scheduling Post Data] WHERE Document_ID = " & "'" & Me.txtDocumentID.Text & "'"

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

If adoprimaryrs1.BOF = False And adoprimaryrs1.EOF = False Then

If adoprimaryrs1!canceled <> "" Then

Dim Output1 As String
Dim Output2 As String
Dim Output3 As String
Dim Output4 As String
Dim Output5 As String
Dim Output6 As String
Dim Output7 As String
Dim Output8 As String

inputstring = adoprimaryrs1!canceled
On Error Resume Next
Output1 = Split(inputstring, " ")(0)
Output2 = Split(inputstring, " ")(1)
Output3 = Split(inputstring, " ")(2)
Output4 = Split(inputstring, " ")(3)
Output5 = Split(inputstring, " ")(4)
Output6 = Split(inputstring, " ")(5)
Output7 = Split(inputstring, " ")(6)
Output8 = Split(inputstring, " ")(7)

'Trap value for Output1
If Output1 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output1 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output1 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output1 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output1 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output1 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output1 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output1 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If


'Trap value for Output2
If Output2 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output2 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output2 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output2 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output2 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output2 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output2 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output2 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If


'Trap value for Output3
If Output3 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output3 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output3 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output3 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output3 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output3 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output3 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output3 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If

'Trap value for Output4
If Output4 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output4 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output4 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output4 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output4 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output4 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output4 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output4 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If

'Trap value for Output5
If Output5 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output5 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output5 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output5 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output5 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output5 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output5 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output5 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If

'Trap value for Output6
If Output6 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output6 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output6 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output6 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output6 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output6 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output6 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output6 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If

'Trap value for Output7
If Output7 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output7 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output7 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output7 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output7 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output7 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output7 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output7 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If

'Trap value for Output8
If Output8 = "Aircraft_1_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
End If

If Output8 = "Aircraft_2_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
End If

If Output8 = "Aircraft_3_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
End If

If Output8 = "Aircraft_4_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
End If

If Output8 = "Aircraft_5_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
End If

If Output8 = "Aircraft_6_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
End If

If Output8 = "Aircraft_7_Canceled" Then
   frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
End If

If Output8 = "All_Canceled" Then
   frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
End If

End If

If Output1 = "" Then
    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 0
End If

If Output2 = "" Then
    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 0
End If

If Output3 = "" Then
    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 0
End If

If Output4 = "" Then
    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 0
End If

If Output5 = "" Then
    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 0
End If

If Output6 = "" Then
    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 0
End If

If Output7 = "" Then
    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 0
End If

If Output8 = "" Then
    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 0
End If

End If

Set adoprimaryrs1 = Nothing

Set db = Nothing

[Killer42]

Is there was a better way to do the following code that is currently in my project?
...

Yes. :)

[Killer42]

Seriously, I'd say that the one biggest improvement you could make would be to use an array rather than a bunch of separate string variables. An array allows you to use a loop, which in effect applies the same processing to any number of elements, rather than writing the same code over and over for individual variables.

A simple example...

Dim MyString(1 To 10) As String
Dim I As Long

' Load some values into the array.
For I = 1 To 10
  MyString(I) = "Aircraft_" & Format(I) & "_Cancelled"
Next

' Print them out.
For I = 1 To 10
  Debug.Print "MyString(" ; Format(I); ") = "; MyString(I)
Next

I won't go into greater detail for now, as my goal is to help you understand the subject, not to do the job for you.

[keithsimpson3973]

Thanks Killer42. That is a huge help. I am going to play with that for a day or 2 and see how far I can go. I just seem to be having a very difficult time understanding arrays, there process, their logic, and the mechanics of putting them into use. As always, I am in awe of your knowledge. Thanks again!

[Killer42]

Yeah, arrays can be a little difficult to grasp at first. Well worth it though, as they are an extremely powerful concept.

...

I was trying to think of an analogy to help clarify it, but went completely blank. Anyway, you can probably finc tons of tutorials on the web.

But yes, playing around with them is probably a good way to develop and understanding of how they work.

Good luck.