Control Field Validation

[Jason Lopez]

I am trying to put some VB code in as a form of validation on an Access
form. What I am looking for is essentially the ability to check 5 different
controls for data contained within. The controls are for dates that are
tied to a table that records the training completion dates. In an effort to
avoid out-of-sequence training or edits to the data after the initial entry,
I am wanting to put in this code. It essentially enables the field for
putting in data but disables subsequent fields to maintain the training
sequence.

This is what I have so far (and it is *long* down below). At this point, I
cannot seem to get it to work right. It immediately locks down the form
when there are dates in the fields. The only other thing that I can think
of is to have this same VB code for the BeforeUpdate, On_Click and
AfterUpdate events.

Jason Lopez

Private Sub Form_Load()
If Me!BFT = 0 Then
Me!BFT.Enabled = True
Me!UPT1.Enabled = False
Me!UPT2.Enabled = False
Me!IFF.Enabled = False
Me!FTU.Enabled = False
Me!BFT.Locked = False
Me!UPT1.Locked = True
Me!UPT2.Locked = True
Me!IFF.Locked = True
Me!FTU.Locked = True
Else
If Me!BFT <> 0 & Me!UPT1 = 0 Then
Me!BFT.Enabled = False
Me!UPT1.Enabled = True
Me!UPT2.Enabled = False
Me!IFF.Enabled = False
Me!FTU.Enabled = False
Me!BFT.Locked = True
Me!UPT1.Locked = False
Me!UPT2.Locked = True
Me!IFF.Locked = True
Me!FTU.Locked = True
Else
If Me!BFT & Me!UPT1 <> 0 & Me!UPT2 = 0 Then
Me!BFT.Enabled = False
Me!UPT1.Enabled = False
Me!UPT2.Enabled = True
Me!IFF.Enabled = False
Me!FTU.Enabled = False
Me!BFT.Locked = True
Me!UPT1.Locked = True
Me!UPT2.Locked = False
Me!IFF.Locked = True
Me!FTU.Locked = True
Else
If Me!BFT & Me!UPT1 & Me!UPT2 <> 0 & Me!IFF = 0 Then
Me!BFT.Enabled = False
Me!UPT1.Enabled = False
Me!UPT2.Enabled = False
Me!IFF.Enabled = True
Me!FTU.Enabled = False
Me!BFT.Locked = True
Me!UPT1.Locked = True
Me!UPT2.Locked = True
Me!IFF.Locked = False
Me!FTU.Locked = True
Else
If Me!BFT & Me!UPT1 & Me!UPT2 & Me!IFF <> 0 & Me!FTU = 0
Then
Me!BFT.Enabled = False
Me!UPT1.Enabled = False
Me!UPT2.Enabled = False
Me!IFF.Enabled = False
Me!FTU.Enabled = True
Me!BFT.Locked = True
Me!UPT1.Locked = True
Me!UPT2.Locked = True
Me!IFF.Locked = True
Me!FTU.Locked = False
Else
Me!BFT.Enabled = False
Me!UPT1.Enabled = False
Me!UPT2.Enabled = False
Me!IFF.Enabled = False
Me!FTU.Enabled = False
Me!BFT.Locked = True
Me!UPT1.Locked = True
Me!UPT2.Locked = True
Me!IFF.Locked = True
Me!FTU.Locked = True
End If
End If
End If
End If
End If
End Sub

 

[Beetle]

That's quite an If..Then statement you have there.

First of all, IF you have a control disabled THEN I don't see why you need
to lock it also. It seems like alot of extra coding for nothing.

Second, why don't you just set the default value of all the controls on your
form to locked, then just use the code to unlock them as needed.

Third, it seems like this code would be better placed in the on current
event for your form. As it is now, it is only going to run once when your
form first loads. If a user does an update and then moves to a new record
without ever closing the form, your code will do nothing

You also might consider a Select Case statement.

 

[John W. Vinson]

I am trying to put some VB code in as a form of validation on an Access
form. What I am looking for is essentially the ability to check 5 different
controls for data contained within. The controls are for dates that are
tied to a table that records the training completion dates. In an effort to
avoid out-of-sequence training or edits to the data after the initial entry,
I am wanting to put in this code. It essentially enables the field for
putting in data but disables subsequent fields to maintain the training
sequence.

This is what I have so far (and it is *long* down below). At this point, I
cannot seem to get it to work right. It immediately locks down the form
when there are dates in the fields. The only other thing that I can think
of is to have this same VB code for the BeforeUpdate, On_Click and
AfterUpdate events.

Stepping back a bit... if you have one *field* for each kind of date, you're
perhaps limiting your flexibility in terms of table design. Suppose you want
to add a *sixth* type of training? You'll need to restructure your table,
rewrite all your queries, redesign your forms and reports, rewrite all your
code... ouch!

Could you instead consider a four-table solution:

Employees
EmployeeID <Primery Key>
LastName
FirstName
<other bio data>

Courses
CourseID <Primary Key>
Description
<other info about the training>

Schedule
SeqNo <what order the courses must be taken>
CourseID <which course>
<other info, e.g. comments, valid reasons to take a course out of order or
skip it, etc.>

Trainings
TrainingID
EmployeeID
CourseID
CompletionDate
<comments, pass/fail, retakes, etc.>

You could then simply *add a record* to the Trainings table; the form's
beforeupdate event could be used to ensure that you're not entering a course
out of sequence; it would make reporting MUCH easier.

John W. Vinson [MVP]

 

[Jason Lopez]

You are already thinking along the lines that I am wanting to take these
tables and forms.

The training form is actually just a summary of the additional forms that
will contain the greater detail of each phase/step in training.

Essentially, the tables look like this:

Trg-BFT; Trg-UPT1,... There are 5 phases/tables total with the summary
table (TrgProg) as an extra and sub to the main form/table. To complete
each phase, there are varying requirements. An example is that Trg-BFT has
5 requirements that must be met. When requirement 5 is completed, then the
AfterUpdate event will send the current date to TrgProg in its respective
field (and hopefully for the correct person). With all the code existing in
the forms, it should work out to going to the correct person the moment that
the subform is updated.

But, you are essentially saying to make a table that already has the
sequence there? Is that right?

Jason Lopez

 

[Jason Lopez]

I never thought of setting the default to Disabled and then letting the code
as OnCurrent work then. But what do you mean by a "Select Case" statement?
I have never heard of that before. Also, I am not a programmer in any way.
Barely touching the surface of VB at this point. Though I am probably in
way over my head.

Jason Lopez

 

[John W. Vinson]

You are already thinking along the lines that I am wanting to take these
tables and forms.

The training form is actually just a summary of the additional forms that
will contain the greater detail of each phase/step in training.

Essentially, the tables look like this:

Trg-BFT; Trg-UPT1,... There are 5 phases/tables total with the summary
table (TrgProg) as an extra and sub to the main form/table. To complete
each phase, there are varying requirements. An example is that Trg-BFT has
5 requirements that must be met. When requirement 5 is completed, then the
AfterUpdate event will send the current date to TrgProg in its respective
field (and hopefully for the correct person). With all the code existing in
the forms, it should work out to going to the correct person the moment that
the subform is updated.

But, you are essentially saying to make a table that already has the
sequence there? Is that right?

That's the idea, yes. You can actually create a subform based on a Query "left
outer joining" the requirements table to the results table; if you have this
subform with the person's ID as the master/child link field you don't need
even a single line of code.

John W. Vinson [MVP]

 

[Jamie Collins]

if you have one *field* for each kind ofdate, you're
perhaps limiting your flexibility in terms of table design. Suppose you want
to add a *sixth* type of training?

Could you instead consider a four-table solution:

Employees
EmployeeID <Primery Key>
LastName
FirstName
<other bio data>

Courses
CourseID <Primary Key>
Description
<other info about the training>

Schedule
SeqNo <what order the courses must be taken>
CourseID <which course>
<other info, e.g. comments, valid reasons to take a course out of order or
skip it, etc.>

Trainings
TrainingID
EmployeeID
CourseID
CompletionDate
<comments, pass/fail, retakes, etc.>

But consider that the OP has stated "avoid out-of-sequence training"
as a requirement. I don't see anything in your proposed design to
enforce this. To take a different angle on your "sixth date" argument,
consider these simple validation rules:

Date1 < Date2
Date2 < Date3
Date3 < Date4
Date4 < Date5
Date5 < Date6

How would you achieve the same data integrity with your 'flexible'
design?

Jamie.

--

 

[Jason Lopez]

I actually used a hybrid of code (no query worked to do what I was
ultimately looking for).

It essentially resets the properties and then runs the IF statements to
disable the appropriate box. But, thank you Jamie for bringing back my main
point for each field: proper sequencing validation.

Thank you to all that have helped so far. I am not quite done with that
part of my database form. So I am sure I will still have more questions yet
to come.

Jason Lopez