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

Using arrays to simplify this code

P: 63
Is there was a better way to do the following code that is currently in my project? Thanks
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=F:\Scheduling\AAGTC_Scheduling.mdb;"
  8.  
  9. Dim adoprimaryrs1 As ADODB.Recordset
  10.  
  11. Set adoprimaryrs1 = New ADODB.Recordset
  12.  
  13. Dim strSQL As String
  14.  
  15. 'SQL select statement
  16.  
  17. strSQL = "SELECT [Avon Park Air Scheduling Post Data].Canceled FROM [Avon Park Air Scheduling Post Data] WHERE Document_ID = " & "'" & Me.txtDocumentID.Text & "'"
  18.  
  19. 'Execute the first SQL statement to find conflicts, if any exist
  20. adoprimaryrs1.Open strSQL, db, adOpenStatic, adLockOptimistic
  21.  
  22. If adoprimaryrs1.BOF = False And adoprimaryrs1.EOF = False Then
  23.  
  24. If adoprimaryrs1!canceled <> "" Then
  25.  
  26. Dim Output1 As String
  27. Dim Output2 As String
  28. Dim Output3 As String
  29. Dim Output4 As String
  30. Dim Output5 As String
  31. Dim Output6 As String
  32. Dim Output7 As String
  33. Dim Output8 As String
  34.  
  35. inputstring = adoprimaryrs1!canceled
  36. On Error Resume Next
  37. Output1 = Split(inputstring, " ")(0)
  38. Output2 = Split(inputstring, " ")(1)
  39. Output3 = Split(inputstring, " ")(2)
  40. Output4 = Split(inputstring, " ")(3)
  41. Output5 = Split(inputstring, " ")(4)
  42. Output6 = Split(inputstring, " ")(5)
  43. Output7 = Split(inputstring, " ")(6)
  44. Output8 = Split(inputstring, " ")(7)
  45.  
  46. 'Trap value for Output1
  47. If Output1 = "Aircraft_1_Canceled" Then
  48.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  49. End If
  50.  
  51. If Output1 = "Aircraft_2_Canceled" Then
  52.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  53. End If
  54.  
  55. If Output1 = "Aircraft_3_Canceled" Then
  56.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  57. End If
  58.  
  59. If Output1 = "Aircraft_4_Canceled" Then
  60.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  61. End If
  62.  
  63. If Output1 = "Aircraft_5_Canceled" Then
  64.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  65. End If
  66.  
  67. If Output1 = "Aircraft_6_Canceled" Then
  68.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  69. End If
  70.  
  71. If Output1 = "Aircraft_7_Canceled" Then
  72.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  73. End If
  74.  
  75. If Output1 = "All_Canceled" Then
  76.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  77. End If
  78.  
  79.  
  80. 'Trap value for Output2
  81. If Output2 = "Aircraft_1_Canceled" Then
  82.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  83. End If
  84.  
  85. If Output2 = "Aircraft_2_Canceled" Then
  86.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  87. End If
  88.  
  89. If Output2 = "Aircraft_3_Canceled" Then
  90.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  91. End If
  92.  
  93. If Output2 = "Aircraft_4_Canceled" Then
  94.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  95. End If
  96.  
  97. If Output2 = "Aircraft_5_Canceled" Then
  98.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  99. End If
  100.  
  101. If Output2 = "Aircraft_6_Canceled" Then
  102.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  103. End If
  104.  
  105. If Output2 = "Aircraft_7_Canceled" Then
  106.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  107. End If
  108.  
  109. If Output2 = "All_Canceled" Then
  110.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  111. End If
  112.  
  113.  
  114. 'Trap value for Output3
  115. If Output3 = "Aircraft_1_Canceled" Then
  116.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  117. End If
  118.  
  119. If Output3 = "Aircraft_2_Canceled" Then
  120.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  121. End If
  122.  
  123. If Output3 = "Aircraft_3_Canceled" Then
  124.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  125. End If
  126.  
  127. If Output3 = "Aircraft_4_Canceled" Then
  128.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  129. End If
  130.  
  131. If Output3 = "Aircraft_5_Canceled" Then
  132.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  133. End If
  134.  
  135. If Output3 = "Aircraft_6_Canceled" Then
  136.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  137. End If
  138.  
  139. If Output3 = "Aircraft_7_Canceled" Then
  140.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  141. End If
  142.  
  143. If Output3 = "All_Canceled" Then
  144.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  145. End If
  146.  
  147. 'Trap value for Output4
  148. If Output4 = "Aircraft_1_Canceled" Then
  149.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  150. End If
  151.  
  152. If Output4 = "Aircraft_2_Canceled" Then
  153.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  154. End If
  155.  
  156. If Output4 = "Aircraft_3_Canceled" Then
  157.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  158. End If
  159.  
  160. If Output4 = "Aircraft_4_Canceled" Then
  161.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  162. End If
  163.  
  164. If Output4 = "Aircraft_5_Canceled" Then
  165.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  166. End If
  167.  
  168. If Output4 = "Aircraft_6_Canceled" Then
  169.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  170. End If
  171.  
  172. If Output4 = "Aircraft_7_Canceled" Then
  173.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  174. End If
  175.  
  176. If Output4 = "All_Canceled" Then
  177.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  178. End If
  179.  
  180. 'Trap value for Output5
  181. If Output5 = "Aircraft_1_Canceled" Then
  182.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  183. End If
  184.  
  185. If Output5 = "Aircraft_2_Canceled" Then
  186.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  187. End If
  188.  
  189. If Output5 = "Aircraft_3_Canceled" Then
  190.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  191. End If
  192.  
  193. If Output5 = "Aircraft_4_Canceled" Then
  194.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  195. End If
  196.  
  197. If Output5 = "Aircraft_5_Canceled" Then
  198.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  199. End If
  200.  
  201. If Output5 = "Aircraft_6_Canceled" Then
  202.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  203. End If
  204.  
  205. If Output5 = "Aircraft_7_Canceled" Then
  206.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  207. End If
  208.  
  209. If Output5 = "All_Canceled" Then
  210.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  211. End If
  212.  
  213. 'Trap value for Output6
  214. If Output6 = "Aircraft_1_Canceled" Then
  215.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  216. End If
  217.  
  218. If Output6 = "Aircraft_2_Canceled" Then
  219.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  220. End If
  221.  
  222. If Output6 = "Aircraft_3_Canceled" Then
  223.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  224. End If
  225.  
  226. If Output6 = "Aircraft_4_Canceled" Then
  227.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  228. End If
  229.  
  230. If Output6 = "Aircraft_5_Canceled" Then
  231.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  232. End If
  233.  
  234. If Output6 = "Aircraft_6_Canceled" Then
  235.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  236. End If
  237.  
  238. If Output6 = "Aircraft_7_Canceled" Then
  239.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  240. End If
  241.  
  242. If Output6 = "All_Canceled" Then
  243.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  244. End If
  245.  
  246. 'Trap value for Output7
  247. If Output7 = "Aircraft_1_Canceled" Then
  248.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  249. End If
  250.  
  251. If Output7 = "Aircraft_2_Canceled" Then
  252.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  253. End If
  254.  
  255. If Output7 = "Aircraft_3_Canceled" Then
  256.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  257. End If
  258.  
  259. If Output7 = "Aircraft_4_Canceled" Then
  260.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  261. End If
  262.  
  263. If Output7 = "Aircraft_5_Canceled" Then
  264.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  265. End If
  266.  
  267. If Output7 = "Aircraft_6_Canceled" Then
  268.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  269. End If
  270.  
  271. If Output7 = "Aircraft_7_Canceled" Then
  272.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  273. End If
  274.  
  275. If Output7 = "All_Canceled" Then
  276.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  277. End If
  278.  
  279. 'Trap value for Output8
  280. If Output8 = "Aircraft_1_Canceled" Then
  281.    frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 1
  282. End If
  283.  
  284. If Output8 = "Aircraft_2_Canceled" Then
  285.    frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 1
  286. End If
  287.  
  288. If Output8 = "Aircraft_3_Canceled" Then
  289.    frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 1
  290. End If
  291.  
  292. If Output8 = "Aircraft_4_Canceled" Then
  293.    frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 1
  294. End If
  295.  
  296. If Output8 = "Aircraft_5_Canceled" Then
  297.    frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 1
  298. End If
  299.  
  300. If Output8 = "Aircraft_6_Canceled" Then
  301.    frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 1
  302. End If
  303.  
  304. If Output8 = "Aircraft_7_Canceled" Then
  305.    frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 1
  306. End If
  307.  
  308. If Output8 = "All_Canceled" Then
  309.    frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 1
  310. End If
  311.  
  312. End If
  313.  
  314. If Output1 = "" Then
  315.     frmEditCompletedFlightMissions.chkAircraft1Cancelled.Value = 0
  316. End If
  317.  
  318. If Output2 = "" Then
  319.     frmEditCompletedFlightMissions.chkAircraft2Cancelled.Value = 0
  320. End If
  321.  
  322. If Output3 = "" Then
  323.     frmEditCompletedFlightMissions.chkAircraft3Cancelled.Value = 0
  324. End If
  325.  
  326. If Output4 = "" Then
  327.     frmEditCompletedFlightMissions.chkAircraft4Cancelled.Value = 0
  328. End If
  329.  
  330. If Output5 = "" Then
  331.     frmEditCompletedFlightMissions.chkAircraft5Cancelled.Value = 0
  332. End If
  333.  
  334. If Output6 = "" Then
  335.     frmEditCompletedFlightMissions.chkAircraft6Cancelled.Value = 0
  336. End If
  337.  
  338. If Output7 = "" Then
  339.     frmEditCompletedFlightMissions.chkAircraft7Cancelled.Value = 0
  340. End If
  341.  
  342. If Output8 = "" Then
  343.     frmEditCompletedFlightMissions.chkCancelEntireEvent.Value = 0
  344. End If
  345.  
  346. End If
  347.  
  348. Set adoprimaryrs1 = Nothing
  349.  
  350. Set db = Nothing
  351.  
Apr 26 '07 #1
Share this Question
Share on Google+
4 Replies


Expert 5K+
P: 8,434
Is there was a better way to do the following code that is currently in my project?
...
Yes. :)
Apr 26 '07 #2

Expert 5K+
P: 8,434
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...
Expand|Select|Wrap|Line Numbers
  1. Dim MyString(1 To 10) As String
  2. Dim I As Long
  3.  
  4. ' Load some values into the array.
  5. For I = 1 To 10
  6.   MyString(I) = "Aircraft_" & Format(I) & "_Cancelled"
  7. Next
  8.  
  9. ' Print them out.
  10. For I = 1 To 10
  11.   Debug.Print "MyString(" ; Format(I); ") = "; MyString(I)
  12. 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.
Apr 26 '07 #3

P: 63
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!
Apr 27 '07 #4

Expert 5K+
P: 8,434
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.
Apr 28 '07 #5

Post your reply

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