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 2098 - ...
-
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 10 results in the
DB. If I was going to do a :
Do Until objRS.EOF
Then it would display the full records, likewise if i put a counter on the
loop then it will run into errors if I have less records than the
count.......
|
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 loop through all the forms in a database
and pull information about the form (controls and properties). We would need
to do the same in our VB.NET project; loop through the project and get the
web form's control and property information...
|
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 miles the person enters
"myitem" is the row...there are seven rows where they can list
expenses.
"myvalue" * .375 = amount to be reimbursed
All the rows are added and placed into the total.
|
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 i'm trying to do is to find "series key" and if so, then parse
the appropriate data. then if it finds "issuer" it needs to exit.
thanks in advance!!!
jung.
|
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 loop. Is there some way
to send a break from VBA code to break out of the loop?
Here's what the error handler code looks like:
Private Function MyFunction
On Error GoTo HandleErr
| |
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 don't have any
misspelled words, it just hangs. If you choose to ignore a misspelled
word, it will keep looping and asking you about the word you chose to
ignore. If you change that word, access will hang while the spell
check continuously...
|
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 to the group.
MS Access.. VBA.. got very many sub relationships and it takes about 30
seconds to see who is certified. But now my problem is I want to go back
through the process and see who is only 1 course group away from being
certified, and...
|
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 language="java" import="java.util.*" %>
<%
HashMap errors = new HashMap();
String firstname = "Joe";
String lastname = "Miller";
|
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 Import_File_Reader As System.IO.StreamReader
While ...
|
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 greatly appreciated.
I am using Windows 2000 with Access 2000, sending email through Outlook 2000.
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim rst As DAO.Recordset
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
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 we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |