Validating existing records with new validationrule

[SiberBob]

Greetings.

I am having a problem with record validation in access 2000.

I have a table of records for appointments. I INSERT INTO the table records with available dates and times for the next three months when the form is opened. Those records only have the appointment date and time, and none of the other fields in the table are required at that time.

Now, when a user reserves an existing appointment time by clicking the boolean "Reserve Appt" that record is locked for editing.

At this point, I need to change the validationrule for some of the controls bound to that record.

I tried to code Me.txtApptName.validationrule = " > ''" on the tglReserveAppt_Click event. How do I trap for violations of that validationrule?

Do you have suggestions on other ways to catch this? Mostly I just need to make some fields "required" when the record has been set as reserved.

 

[pbaldy]

I generally do validation in the before update event of the form. You can do your testing there, and if validation fails add:

Cancel = True

to the code which will stop the update. I'd also give the user a message box so they know what went wrong.

Paul
MS Access MVP 2007/2008

 

[SiberBob]

That may be problematic:

On the "tglReserveAppt_Click" event the "ApptReserved" boolean value for that record is set to True which causes the record to be locked for editing so the user can fill in the rest of the 'required' fields, but it also fires the BeforeUpdate event prior to those other fields being filled in doesn't it?

 

[pbaldy]

Have you tested it?

Paul
MS Access MVP 2007/2008

 

[SiberBob]

Yes I have.

On an existing record, changing a boolean value bound to the record will cause the "Before Update" event to fire.

 

[pbaldy]

I tested and it did not. I'm talking about the before update of the form, not the checkbox. Is that where you have the code?

Paul
MS Access MVP 2007/2008