Validate form and subform on attempted exit

[LPurvis]

You'd not got the validation rules as I'd mentioned.
You'd placed them in the individual fields, this meant they behaved effectively just as the Required property.

They needed to be in the table validation.
So that it is checked when the record is committed.

Bear in mind that such table level validation is generally your safety net.
You would often perform application checking first to provide a more friendly user experience (and message).
For example the validation rule would stop the record being commited - but not necessarily your form from closing.


You also had some calls of
Me.Refresh
This is, as I was saying, causing the record to be committed.
(That's exactly what it does - forces a commit and updates the data from the loaded records).
There's no need for a Refresh where you had it.
Just requering the individual combo's is all you need.
(And I'd always use the Requery method of the control to do that rather than the DoCmd method).

Have fun.

Cheers.

 

[andy_dyer]

I never knew you could do that at a table level!

When adding a new record I can populate one or two of the fields in question and then exit - no validation problem... how??

If I try to get onto the subform without filling them all in then I get my on error bit triggered - not the table validation message...

Even if they are all filled in I get my On Error triggered and can't get at the subform....

Sorry for being a pain!!

 

[LPurvis]

>> "When adding a new record I can populate one or two of the fields in question and then exit - no validation problem... how??"

I don't know - it's raising the validation error for me.
These fields aren't in the subform are they?

>> "If I try to get onto the subform without filling them all in then I get my on error bit triggered - not the table validation message..."

That's because there is still the error handler in your form's VBA code remember?
Actually looking at that for the first time you have an error in there too.
It should be:
If DataErr = 3314 Or DataErr = 3316 Or DataErr = 3317 Then
Not:
If DataErr = 3314 Or 3316 Or 3317 Then

As the latter two are coerced into boolean results then - i.e. always true!
So it's not reporting the correct error back to you - it's all being masked.
Implement as above - and you'll get the real default error message.
To me - it's looking likely it's due to your unique index on the Project Number field.

Just to note, you can't get at the subform because that causes the record to be committed. (Navigation between parent and subform). Hence errors are raised at that point.
If you go to the subform first and enter data there's no parent record to validate, hence why that might have explained your earlier situation.

Cheers.

 

[andy_dyer]

I've added a me.refresh on the exit button - this now commits the record and generates error messages...

Problem is despite them being 3316 and 3317 messages they are not being captured by the error handler... (I've fixed the code as you suggested) and removed the validation text (but kept the rule) as I want it to go via the error handler as look of the error is far simpler...

I don't want my users to get errors that ask if they want to end or debug (it may freak them out)


----------------------------------------------------------------------

I've also got a seperate issue reagrding how I ensure the same project isn't used more than once for each project type... (I've tried making this field indexed with no duplicates - but again either don't get any error messages or I get lots of errors that I struggle to handle properly)

e.g. for a project with the number 1111 I want to be able to have one project record, one expenses record etc - is this possible to check as soon as the project number has been chosen from the combo box?



Thanks Leigh for all your help and patience!!

 

[LPurvis]

Been too busy lately. Is this last issue you report ongoing still?

 

[andy_dyer]

Hi Leigh,

Don't worry I've been busy too - I do appreciate your help!

I've still got the errors i.e. if I just select Type and try and exit I get errors that should be trapped by the handler but are not and I get the confusing Access messages.

If I try and close the form by clicking the X in the corner I get my error message... if I click return to main menu button I get the access message...

With regard the other points they won't stop people using the database - I may just pull a query that shows if there are more than one entry for a project number and type and enable people to go and fix it afterwards instead of trying to control people doing it in the first place - it may just be too hard unless you have a suggestion...

I really just need to get the requirement to ahve some fields complete working properly in order to roll this out...

If you can help me that would be great!!

 

[LPurvis]

Sorry, work and things are just crazy.
Is this still ongoing? Can you give me a quick summary to remind me where we were (and even what we were doing? :-s)

Cheers.