date comparison help for access database

[jackson_jl]

I am working on a database project that needs to utilize date comparison code. I could really use some help on this one. Heres the problem below..







Problem:







I need to construct code that compares data in three fields: Assets, StartDate, and EndDate. I have a database that I want to be able to schedule the use of assets for customers from a start date to and end date. Once an asset is scheduled for a customer from the start date, it cannot be used for another customer until after the end date. If an asset is already scheduled, then an error message box will appear on my form, or if the end date is before the start date an error message box will appear.(Sort of like Library Book code that allows the check out and return date of a book where no one else can check the book out until it is returned by the customer) If there is anyone out there who knows of code that I can use to program a form button on my Access 2003 database project please help. I have tried to write it out below in this form:





On Click

1. Opens qryScheduledResourceType

2. Compares Resource Type with StartDate and EndDate.

3. If there is no conflict with the Resource Type and dates, Then a new record

Will be saved.

4. If not, an error message will occur because either resources are already scheduled with the new dates or the end date preceeds the startdate.







I have started to try to write code for this, however I know that I am missing quite a few pieces:

[vba]Private Sub CommandSave_Click()

DoCmd.OpenQuery qryScheduleResourceType, acPreview



If ResourceType = 0 Then

Perform Save Function

ElseIf ResourceType > 0, Where StartDate (New) = between StartDate (Old) and EndDate (Old)Then

MsgBox "Scheduling Error-Resource Type Already Scheduled. Pick Another Resource Type or other Dates"

ElseIf EndDate < StartDate Then

MsgBox "End Date Error - End Date Occurs Before Start Date"

End If

End Function[/vba]





I know this is crude, but it is the only way that I can come up with to explain this. I wish to tie this code, if possible to a Save record button on my form that will run this test, and either input the new record, or will produce an error message telling the customer to reschedule either another asset or dates. If you know how I can do this, I would really appreciate the help. I can be reached by reply to this email or the mail and phone number address below.



Thanks in advance.



James Jackson

813-828-4628

jacksj1@socom.mil

[downwitch]

This is what the code should look like, more or less (I did this in a couple minutes, so disclaimer disclaimer...)[vba]Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo HandleErrors

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim bleSave As Boolean
Dim strMsg As String

If EndDate < StartDate Then
bleSave = False
strMsg = "Invalid end date."
Else
bleSave = True
Set db = CurrentDb
Set rst = db.OpenRecordset("qryScheduleResourceType", dbOpenSnapshot)

With rst
If .RecordCount > 0 Then
If !ResourceType = 0 Then
bleSave = False
strMsg = "Resource type already scheduled - " & !StartDate & " to " & !EndDate
End If
End If
End With
End If

If bleSave = True Then
'do nothing--the record will save itself when you close the form or move off it
Else
Cancel = True
MsgBox strMsg, vbExclamation, "cannot save record"
End If

ExitHere:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

HandleErrors:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation
Resume ExitHere

End Sub[/vba]You will need to set a reference to DAO before this code will compile and run.

There are too many differences between what you've written and what I've written to begin to explain to you. Here are the two key points:
(1) to stop a record from saving if certain conditions aren't met, you use the form's Before Update event; the On-Click event for a button isn't quite the right place.
(2) Queries and recordsets are not the same thing. Your code opens a query on the screen; this code loads the set of records into memory and then retrieves a value from them.
By the way, I suspect that this query's recordset will not retrieve the specific reference you're looking for--i.e. to a particular asset. Not knowing how your data was structured, I couldn't incorporate the record-finding bit.

I would strongly recommend that you get yourself a good reference book on Access and VBA. (Sybex is my publisher of choice--there's a range of books to choose from.) No one I know has ever made it through the initial steps without one (I used two myself, way back when--when one let me down, the other kept me going), and I simply wouldn't know how to teach you some of the things you have to learn to work with it in the context of a forum. (Most forum repliers will not call or send you e-mail, by the way, and many consider it impolite to ask--the point is to do the work in public, so that everyone can benefit from the examples.) I've said too many times to count, and believe it to this day: MS is wrong to package Access with Word, Excel and PowerPoint, as if it was just a simple user-friendly little relational database manager; it's a different animal, and it requires serious learning time.

Good luck.

[OBP]

Why are you looking for error messages, why not just exclude those records not meeting the date requirements in the "Criteria" section of your data selection Query? That way they do not even appear on a form and therefore cannot be selected for a new customer.

[Zack Barresse]

James, [uvba]here ya go[/uvba] I have edited your post to take advantage of these, hope you don't mind.