VBA Validation

[blueboy2001]

I have made a database for my final year uni project for a contracting firm to manage their job bookings. Each job can have multiple pieces of equipment and multiple drivers assigned to it.

I'm using a form frmjob with 2 subforms to allow the input of the drivers and equipment on that job. What I really need is some validation that when the Save button is clicked, it checks that the driver is not on another job on the same date with overlapping times, and likewise for the equipment.

In all honesty I haven't got a clue where to start with this, I've done some VB programming in the past but I'm no expert when it comes to using VBA in databases.

Can anyone point me in the right direction of where to start?

 

[Cosmos75]

Each job can have multiple pieces of equipment and multiple drivers assigned to it.

I'm using a form frmjob with 2 subforms to allow the input of the drivers and equipment on that job. What I really need is some validation that when the Save button is clicked, it checks that the driver is not on another job on the same date with overlapping times, and likewise for the equipment.

So you have a one-to-many relationship between Job & Driver/Date?

If it is just that you can't schedule a driver to two jobs on one day then you can create a unique index using Driver and date/time and set the properties to unique (i.e. you can't have two records with the same Driver & Date)?

But I have on equestion, if a driver is "booked" for Jan 1st, does that mean he is free on Jan 2nd? Or is it possible that the job will take more than one day? If so, then it will be more difficult (not impossible) to validate data entry.

If drivers can be scheduled to mutiple jobs in one day, then you would have to somehow query to check what jobs that particular driver is schedule to do, the times (I asume that you would need to include how long the job would take), and then check against the time of the new job to make sure there is no overlapping.

Old Job = Job1 at 1pm for 2 hours
New Job = Job2 at X o clock for 1 hour
e.g. IF
(X o'clock + 1 hour) is less than 1pm
or
X O'clock is greater than (1pm + 2 hours) then you can schedule that driver.

 

[blueboy2001]

Drivers will be assigned several jobs per day, normally 2 or 3.

In my Job Table (tbljob) I have fields for Date, Start Time, and Hours (that the job will take). I've then got tblJobDriver which holds JobID and DriverID to allow multiple drivers to be on each job.

The final paragraph of your reply is pretty much what I need to do.

I'm thinking would think I'd need a query to return the DriverID's, JobID's, StartTime, Hours where JobDate=FORMS![frmjob].[txtdate] and DriverID=FORMS![frmjob].[DriverID] (thats not actually a field on my form)

I presume the query would have to run as an SQL string from the event procedure for the button?

If so, how would the validation code look, ie where would the query return its results to?

 

[Cosmos75]

In all honesty I haven't got a clue where to start with this, I've done some VB programming in the past but I'm no expert when it comes to using VBA in databases.

See here for more on using DAO and ADO in code.

Can anyone point me in the right direction of where to start?

I'll do better and attach a sample database.

The final paragraph of your reply is pretty much what I need to do.

I presume the query would have to run as an SQL string from the event procedure for the button?

If so, how would the validation code look, ie where would the query return its results to?

See sample. You don't have to run the query as an SQL string in the event procedure, but I find it easier to use a parameter query using code, rather than a querydef. But I have been told that it is better to use a querydef. See here.

HOPE THIS HELPS!

I have made a database for my final year uni project for a contracting firm to manage their job bookings.

Does this violate any honor code?

 

[blueboy2001]

Thanks very much for all that.

If any of your work is included, I assure you that it will be suitably referenced in my bibliography, and I will include a copy of this thread in my appendix.

 

[Cosmos75]

Thanks very much for all that.

You're most welcome.

If any of your work is included, I assure you that it will be suitably referenced in my bibliography, and I will include a copy of this thread in my appendix.

Cool!

 

[Cosmos75]

Mistake!

    If Check1 = -1 Then
        [color=green]'OK, End time for new job is before or equal
        'to start time of existing job[/color]
        [color=red][b]Exit Sub[/b][/color]
    ElseIf Check2 = -1 Then
        [color=green]'OK, Start time for new job is after or equal 
        'to start time of existing job[/color]
    Else
        MsgBox "Cannot assign this driver! Please choose another driver!", & _
        vbCritical, "Driver not available for this job!"
        Cancel = True
        Exit Sub
    End If

You will need to remove the "Exit Sub" line after the first IF statement. If you don't, what will happen is that if the first Job that it checks is OK on that condition it exits and won't check any further!!


MY BAD!