473,320 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VB6 and Access 2003

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!
Nov 15 '06 #1
11 2497
Killer42
8,435 Expert 8TB
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...
Expand|Select|Wrap|Line Numbers
  1. WHERE SchedStart <= #<end-date># AND SchedEnd >= #<start-date>#
Is this any help?
Nov 15 '06 #2
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!
Nov 16 '06 #3
Killer42
8,435 Expert 8TB
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?
Nov 16 '06 #4
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.
Nov 16 '06 #5
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.
Nov 16 '06 #6
Killer42
8,435 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim SchedStart As Date
  2. SchedStart = DateValue(DateIn) + TimeValue(TimeIn)
You can similarly combine them in a query like so (simplified illustration)
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.Date_In, Table1.Time_In, DateValue([Date_In])+TimeValue([Time_In]) AS StartDateTime
  2. 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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT Table1.Date_In, Table1.Time_In, " & _
  2. "DateValue([Date_In])+TimeValue([Time_In]) AS DateTime_In, " & _
  3. "DateValue([Date_Out])+TimeValue([Time_Out]) AS DateTime_Out " & _
  4. "FROM Table1 " & _
  5. "WHERE (((DateValue([Date_In])+TimeValue([Time_In]))<=#" & _
  6. Format(SchedEnd,"mm/dd/yyyy hh:nn") & _
  7. "#) AND ((DateValue([Date_Out])+TimeValue([Time_Out]))>=#" & _
  8. Format(SchedStart,"mm/dd/yyyy hh:nn") & "#));"
  9.  
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.
Nov 16 '06 #7
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.
Nov 17 '06 #8
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=Microsoft.Jet.OLEDB.4.0;Data Source=c:\scheduling\AAGTC_Scheduling.mdb;Jet OLEDB:Database Password = allow;"
Set cnConn = New ADODB.Connection
Set rsRecSet = New ADODB.Recordset

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

Dim SchedStart As Date
SchedStart = DateValue(frmForecast.txtDate_In.Text) + TimeValue(frmForecast.txtTime_In.Text)

Dim SchedEnd As Date
SchedEnd = DateValue(frmForecast.txtDate_Out.Text) + TimeValue(frmForecast.txtTime_Out.Text)


strsql = "SELECT ForecastTable.Date_In, ForecastTable.Time_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(SchedStart, "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.
Nov 17 '06 #9
'replace with your own table name
' rsRecSet.Open "SELECT POC FROM [Avon Park Air Scheduling Post Data]", cnConn, adOpenKeyset, adLockOptimistic, 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
Nov 17 '06 #10
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.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
Set adoprimaryrs1 = 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)


strsql = "SELECT ForecastTable.Document_ID, ForecastTable.Date_In, ForecastTable.Time_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(SchedStart, "mm/dd/yyyy hh:nn") & "#));"

adoprimaryrs1.Open strsql, db, adOpenStatic, adLockOptimistic

If adoprimaryrs1.RecordCount > 0 Then
MsgBox "Conflicting dates.", vbOKOnly, "Conflicting Dates"
End If
Nov 17 '06 #11
Killer42
8,435 Expert 8TB
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. :)
Nov 19 '06 #12

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

Similar topics

11
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
2
by: BT Openworld | last post by:
I have just had to upgrade to Access 2003 as Access 97 EMail (SendObject) doesn't work when loaded on Windows XP. I'm finding my way around Access 2003 but my biggest problem is getting...
1
by: Wayne Aprato | last post by:
I have a client who is running several Access 97 databases that I have written for them. They are about to upgrade to Access 2003. Is the default file format of Access 2003 still Access 2000 the...
3
by: Colin Chudyk | last post by:
Hi, Here is my situation. Can anyone provide insight? I have developed a database in Access 2002. I am planning to distribute it as a split MDE (front) / MDB (back) to be used by the Access...
7
by: Wayne Aprato | last post by:
I have several Access 2003 mde databases. When I try to open them in Access 2002 I get the following error: "The Visual Basic for Applications project in the database is corrupt." ...
2
by: Jeff | last post by:
Does anyone know of any potential problems running a 2000 database with 2003? Also, what about installing all other Office products as 2003 versions but leaving Access as 2002 running a 2000...
10
by: Lauren Wilson | last post by:
Ok I have searched the MS website for info on this. I am totally confused. If I want to deploy an Access 2003 app and allow my users to run it using Access 2003 Runtime, where do I get the...
47
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
49
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.