VB6 and Access 2003

[keithsimpson3973]

Hi everyone,
This is the first post for me. I have been looking for about a week now so I hope I have not missed someone posting this same question.

In my access database, I have a Start Date field, Stop Date field, Time_In field, and a Time_Out field.

I am creating a scheduling application. What I am trying to do, if possible, is using an ado connection, take a start date, start time, stop date and stop time from a vb form that the user would plug in the dates and times in, and compare that to those same fields in the Acces db to see if there is overlap from one scheduled event to the next.

For example, Unit A schedules in 11/1/2006 at 10:00 AM til 11/2/2006 at 11:00 AM. Another person gets a call from Unit B who wants to schedule for 11/1/2006 13:00 til 11/2/2006 14:00.

I would like the querry to run from vb6 and see the overlap so I can then display
MSGBOX "There is an overlap of times. Unit A has already schedule use for this time period.", vbokonly,"Range already schedule for use!"

I can not seem to get the querry string right. Any help would be greatly appreciated!

[Killer42]

Hi everyone,
This is the first post for me. I have been looking for about a week now so I hope I have not missed someone posting this same question.

In my access database, I have a Start Date field, Stop Date field, Time_In field, and a Time_Out field.

I am creating a scheduling application. What I am trying to do, if possible, is using an ado connection, take a start date, start time, stop date and stop time from a vb form that the user would plug in the dates and times in, and compare that to those same fields in the Acces db to see if there is overlap from one scheduled event to the next.

For example, Unit A schedules in 11/1/2006 at 10:00 AM til 11/2/2006 at 11:00 AM. Another person gets a call from Unit B who wants to schedule for 11/1/2006 13:00 til 11/2/2006 14:00.

I would like the querry to run from vb6 and see the overlap so I can then display
MSGBOX "There is an overlap of times. Unit A has already schedule use for this time period.", vbokonly,"Range already schedule for use!"

I can not seem to get the querry string right. Any help would be greatly appreciated!

You haven't given us field names or anything, so I'll make some assumptions.

Let's say you have fields SchedStart and SchedEnd on the table. I think to find the overlap you want a WHERE clause something like...

WHERE SchedStart <= #<end-date># AND SchedEnd >= #<start-date>#

Is this any help?

[keithsimpson3973]

The Access 2003 database field names are "Date_In" , "Time_In", "Date_Out", and "Time_Out"

Here is scenario. Different users have access to schedule events. The time and date entries would be like the following.

Unit A "Date_In = 11/1/2006" , "Time_In = 13:00", "Date_Out = 11/3/2006", "Time_Out = 15:00"

Now a different person gets a call from Unit B who wants to schedule range time.

Unit B "Date_In = 11/1/2006", "Time_In = 11:00", "Date_Out = 11/2/2006", "Time_Out = 09:00"

What I am trying to do is this. When the person entering the schedule info for Unit B puts his dates and times in, it should check to see if those times are in use. In this case, Unit B "Date_In" value and "Time_In" value are not a problem until their time span hits 13:00 since Unit A was already scheduled to have that start time.
So I would like VB6 to check the "Date_In" and "Time_In" values and the "Date_Out" and "Time_Out" values the user enters into the vb6 form against the existing dates and times in the database and then display the message box if there are overlap of times.
Thanks so much for the help!

[Killer42]

Just let me check that I'm understanding the question clearly.

We are talking about comparing two time ranges - the one entered and the one (just assuming for simplicity that we check one at a time) on the database. So given your example,

Unit A "Date_In = 11/1/2006" , "Time_In = 13:00", "Date_Out = 11/3/2006", "Time_Out = 15:00"

This represents one continuous range of 50 hours - correct?

And the values entered by the user...

Unit B "Date_In = 11/1/2006", "Time_In = 11:00", "Date_Out = 11/2/2006", "Time_Out = 09:00"

represent another continuous range, of 22 hours - right?

And you want to detect that they overlap, and prevent the Unit B entry?

[keithsimpson3973]

Yes, that is correct. I applologize. I was trying very hard to make sure I listed the question in such a way to make it understandable. Guess I need a lot more practice yet.! But thank you very much for the help!
The database will be an ongoing list of scheduled events. It will contain data for a 1 year period at which point it would then get archived and an empty database would begin to build again with the new year's events.
It is very very very very important that only one unit be allowed on range at a time for their training mission. There will be manual checks and balances, but the accuracy of this program is highly important. Thanks again for the help!
You are also correct that the data the user is adding would be checked against what already exists in the data base.
It would have to check against the "Date_In", "Time_In", "Date_Out", and "Time_Out" all at the same time against what is already in the database.

[keithsimpson3973]

Just to further clarify, ( I am really trying hard to make sure I post things here the correct way, and any advice or criticism would be taken in a good way and I wouldn't feel offended at all if anyone sees me doing something in a way I shouldn't) the program is to schedule training time on a military training facility so that should kind of help to clarify the importance of this program checking for and finding conflicting dates and times that are used to schedule infinite numbers of units of troops.

[Killer42]

Just to further clarify, ( I am really trying hard to make sure I post things here the correct way, and any advice or criticism would be taken in a good way and I wouldn't feel offended at all if anyone sees me doing something in a way I shouldn't) the program is to schedule training time on a military training facility so that should kind of help to clarify the importance of this program checking for and finding conflicting dates and times that are used to schedule infinite numbers of units of troops.

No need to worry, we're a pretty easy-going bunch here. Just wanted to make sure I wasn't sending you in the wrong direction.

Ok, for starters, I think you'll find it easier if you combine your date and time into a single variable of type Date. For example:

Dim SchedStart As Date
SchedStart = DateValue(DateIn) + TimeValue(TimeIn)

You can similarly combine them in a query like so (simplified illustration)

SELECT Table1.Date_In, Table1.Time_In, DateValue([Date_In])+TimeValue([Time_In]) AS StartDateTime
FROM Table1;

To find records which overlap, I believe you should be able to issue some variation on the following query. My memory is terrible, but I'm basing this on program variables SchedStart and SchedTime (combined date/times values, start and end) and made-up query field (as in the example above) of DateTime_In and DateTime_Out

strSQL = "SELECT Table1.Date_In, Table1.Time_In, " & _
"DateValue([Date_In])+TimeValue([Time_In]) AS DateTime_In, " & _
"DateValue([Date_Out])+TimeValue([Time_Out]) AS DateTime_Out " & _
"FROM Table1 " & _
"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") & "#));"

I hope this work out - I'm fairly certain the original query I built in Access was right, but not sure about the translation to VB syntax, which was done in Notepad.

Oh, one other thing - I suppose you can modify the query to just return a count, if you just need to know "does this exist?" rather than the details.

[keithsimpson3973]

Wow. Thanks a bunch. I am not 100% sure how to use the information you gave me, but I am going to try and make it work myself before I trouble anyone more. Thanks again for the quick response and very helpful information.

[keithsimpson3973]

The following is the way I am trying to go, but keep getting error when it reaches the rsRecSet.open. The error is "This operation can not be used or the object is closed"

Private Sub Command1_Click( )

Dim strConnect As String
Dim blnRetVal As Boolean

'replace with your own provider, database path and filename and password
strConnect = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=c:\sched uling\AAGTC_Sch eduling.mdb;Jet OLEDB:Database Password = allow;"
Set cnConn = New ADODB.Connectio n
Set rsRecSet = New ADODB.Recordset

cnConn.Open strConnect
rsRecSet.Cursor Location = adUseClient
'replace with your own table name
' rsRecSet.Open "SELECT POC FROM [Avon Park Air Scheduling Post Data]", cnConn, adOpenKeyset, adLockOptimisti c, adCmdText

Dim SchedStart As Date
SchedStart = DateValue(frmFo recast.txtDate_ In.Text) + TimeValue(frmFo recast.txtTime_ In.Text)

Dim SchedEnd As Date
SchedEnd = DateValue(frmFo recast.txtDate_ Out.Text) + TimeValue(frmFo recast.txtTime_ Out.Text)


strsql = "SELECT ForecastTable.D ate_In, ForecastTable.T ime_In, " & _
"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(SchedSta rt, "mm/dd/yyyy hh:nn") & "#));"

rsRecSet.Open (strsql)





If Data1.Recordset .RecordCount > 0 Then
MsgBox "This schedule would overlap another schedule.", vbOKOnly
End If
End Sub

I am very sorry to be such an idiot, but I do not see what I am doing wrong.

On my form, this code is behind a command button.

On my form I have 12 fields.


Date_In
Date_Out
Time_In
Time_Out
Document_ID
Unit

Theese 6 fields are input from a user via a vb form

Date_In
Date_Out
Time_In
Time_Out
Document_ID
Unit

These 6 fields are text boxes on the same form bound to the database so. Because if the query finds an overlap time I need to display the unit name.

Sorry for being such a pain in the rear. But I only have 2 days to finish it all up.

[schandraram]

'replace with your own table name
' rsRecSet.Open "SELECT POC FROM [Avon Park Air Scheduling Post Data]", cnConn, adOpenKeyset, adLockOptimisti c, adCmdText

Look at the example for opening a record set... you need to mention the connection to the database from which this record set will attempt to fetch the data.

Hope this helps
Chandra

[keithsimpson3973]

Thanks for the help. Killer42, you are awesome! You really helped me out big time. Hope I can return the favor some day and that I get to be as good of a programmer as you are! Thanks again!

The following is what I wound up with that worked.




Dim db As ADODB.Connectio n
Set db = New ADODB.Connectio n
db.CursorLocati on = adUseClient
db.Open "PROVIDER=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\Sched uling\AAGTC_Sch eduling.mdb;"
Dim adoprimaryrs1 As ADODB.Recordset
Set adoprimaryrs1 = New ADODB.Recordset
Dim SchedStart As Date
SchedStart = DateValue(frmGl obalForecast.tx tDate_In.Text) + TimeValue(frmGl obalForecast.tx tTime_In.Text)

Dim SchedEnd As Date
SchedEnd = DateValue(frmGl obalForecast.tx tDate_Out.Text) + TimeValue(frmGl obalForecast.tx tTime_Out.Text)


strsql = "SELECT ForecastTable.D ocument_ID, ForecastTable.D ate_In, ForecastTable.T ime_In, " & _
"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(SchedSta rt, "mm/dd/yyyy hh:nn") & "#));"

adoprimaryrs1.O pen strsql, db, adOpenStatic, adLockOptimisti c

If adoprimaryrs1.R ecordCount > 0 Then
MsgBox "Conflictin g dates.", vbOKOnly, "Conflictin g Dates"
End If

[Killer42]

Thanks for the help. Killer42, you are awesome! You really helped me out big time. Hope I can return the favor some day and that I get to be as good of a programmer as you are! Thanks again!
The following is what I wound up with that worked.
...

Glad to hear that things worked out. I was actually getting a bit lost, and had sent off a message to a couple of Access gurus to ask for help.

Not that I'd ever admit that, of course. :)