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
-
4 2088 - ...
-
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"
-
Exit For
-
End If
-
Next InnerLoop
-
If frmGlobalForecast.CheckAREA = 1 Then
-
Exit For
-
End If
-
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.
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.
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!
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: John Smith |
last post by:
I'm trying to perform a loop to display the contents of my DB, the only
issue is that I would only like to display 10 results maximum this is
relatively easy but what happens if there are less than...
|
by: ALthePal |
last post by:
Hi,
I'm not sure if we are able to or even how to loop through the web forms in
a VB.NET project during design time. In MSAccess we are able to go through
the database -> forms collection and...
|
by: Abby Lee |
last post by:
My function works but there has got to be a way to make a for loop to
handle this...but I can't get a for loop to work. You can tell, I'm
not very good at this...help.
"myvalue" is the number of...
|
by: JMCN |
last post by:
help! i'm caught in the endless of "compile error message: do without
loop." i thought that i closed all of my statments but it appears
not. does anyone know why my structure is incorrect?
what...
|
by: deko |
last post by:
I have a logging routine that's supposed to silently log errors caught by
error handler code on certain functions. The problem is sometimes stuff
happens and the error handler can get caught in a...
| |
by: LowRyder |
last post by:
I haven't been able to find any fixes for this. I have a form with two
memo fields. Sometimes when you push the spell check button or press
F7 the spell check goes into an infinite loop. If you...
|
by: LostDeveloper via AccessMonster.com |
last post by:
Relationship goes Certification can have multiple variations (any single
variation satifies the certification), which require multiple course groups
which require any one of the products assigned...
|
by: oaklander |
last post by:
I have this JSP where I have alot of fields with conditions.
I would like to make it more efficient and use a for loop.
Here is an example (showing 2 fields for example only):
<%@ page...
|
by: =?Utf-8?B?V2lsbGlhbSBGb3N0ZXI=?= |
last post by:
Good evening all,
I am trying to write a process that uses a while loop to cycle multiple
files from an array throught the StreamReader Process.
The whole thing works using:
Dim...
|
by: nspader |
last post by:
I am having trouble with my loop code. The code works very well. However, it only loops through 3 records and then completes without errors. I will post code below. Any help with this would be...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |