Struggling with validation can I turn it on and off with VBA?

PlasticMonster

Registered User.
Local time
Today, 15:56
Joined
Aug 21, 2012
Messages
30
Im struggling again so once more return for advice, Ive been trying a few random work arounds but im sure my basic approach is askew.

I have a combobox that selects the reason for access [CBreason] when the slection is "Incident" I want the user to input an Incident Number in the[IncidentNo] textbox.

This incident number should be like "INC0001234567". The box becomes visible (and is a shiny red colour).

This is the code that I am currently using its set for the After Update event of [CBreason]:

If Me.CBReason.Column(1) = "Incident" Then
Me.IncidentNo.Visible = True

'On Error Resume Next

Me.IncidentNo.Value = Me.IncidentNo.DefaultValue
Me.IncidentNo.SetFocus

'On Error GoTo 0

Else
Me.IncidentNo.Visible = False
Me.IncidentNo.Value = "INC0000000000"
End If


I dont want the database to have blank entries so if its not needed it reads INC0000000000. I have set a mask up on the form for [IncidentNo] and set the data validation at table level to no zero entries.

Problems with this 'solution' are:

1. I get an error when I return the box to the default value "" as no zero entries are allowed. (You can see I tried to add in on error resume next to get past this but its not really working).

2. If the user changes their mind selecting first "incident", then something else, then "incident" again the [IncidentNo] reads INC0000000000 and they can just use this and progress without out giving their one!!

This is abit of a garbled post but I think staring at the computer all day has melted my brain a little bit.

Please suggest where I am going wrong, im pretty sure setting validation on both the form and the table is a bad idea to begin with, but was my best idea so far....Umm

Help... Please....:confused:
 
Hmmm, if it were me I would make the PK an Autonumber and hidden which would then give complete control over IncidentID and what gets entered. Then you can even control the User changing their mind and/or having all zeroes would be no problem. And yes, I would limit my validation to the form and forget the table. I can control the from not so much with the table.
 
Thankyou, Ill keep my validation to the form and leave the table alone then.

Sadly I cant limit the INC number to be an autonumber as this is generated from another system, they arent sequential as its every incident the company globally deal with.


>>>>>ok>>>>>>

Ive done some more testing and I have a solution but still I major problem.

I have given up trying to get INC000000000 into non used calls, ill accept blank for these.

Set Required in the table validation.
Set no zero values in the table validation.

Set up a mask in the [IncidentNo] box... all good. Works on all new entries etc.

However if they change their mind the code puts the default entry back in and it all falls over with a no zero entries validation error.

Can I trap this error from stopping the entry being entrered? Its ok if you dont use the box at all (and by default it remains blank). How come it isnt if you drop the default back in!!!
 
Last edited:
An alternative solution might be to use an if statement in VBA to change the validation property? Is this possible? Google isnt helping
 
You should read Gina's post over, you misunderstood.
 
Thank you Speakers... my post was misunderstand. I was not suggesting you use the Autonumber for any part of the PK. I was saying it is the PK AND hidden. You can then increment (and control) your Incident ID any way you want.
 
Your welcome. What you are suggesting is how I always do it. The end user will never see a primary key, only something that looks like it is a primary key. After all, the primary key is arbitrary, so why show anyone?
 
There is no need to move the default value into a field. Access does it automatically as soon as you dirty the record.

I would leave the validation at the table level rather than moving it to the form. Table level validation is minimal at best prior to data macros in A2010 but you should still use it for whatever it can do because it is applied by the database engine. That means that no form, query, or other application can put data in a table that violates RI or validation rules. You don't have to keep repeating it if you have multiple spots that cause updates.

I'm glad you decided not to go with the 0-filled value. You did it in defeat but it is actually the best solution. Sometimes you need a default value because it will affect processing. This usually applies to code type fields. Otherwise, "empty" columns should be null. That gives your application some level of consistancy plus it can save considerable space. I just modified a table in a database at a client site and reduced its size from 1.5 Gig to .7 by getting rid of all the zero fills. The data comes from a client's mainframe system and it never occured to them to strip out all the 0's as they loaded it. Access table rows are variable length and shrink and expand depending on the length of each data field.
 
Ive had my hands full in the last few days and have not been working on my project. Getting back I find that there had been lots of advice left fro me.

Thankyou for your help.

Gina, sorry I got the wrong end of the stick. I still cant quite fathom what you are trying to explain to me but I know that is may failing rayher than your solution.

In the end I have created a public function that does all the check s for me and dropped it into the after update event of the forms, this seems to solve it for now... albeit ive made a new problem with my buttons (I need the check to happen but cant get it to call properly).

Im quite curious on the comment from Pat, I was told to put all the "control" validation ect in the form and leave the table alone...the argument being that eventually you might need to modify the data being entered and this could cause issue later much easier to mod the form. I originally thought putting it on the table would be better as that controls everything (if you see what i mean, kinda lowest level assurance).
 
PM - IMPORTANT!!!!!
Validation is done in the Form's BEFOREUpdate event NOT the AfterUpdate event. You need the ability to cancel the save if the data is invalid and you can only do that in the BeforeUpdate event. The AfterUpdate runs AFTER the bad data has already been saved so it is akin to closing the barn door after the horse has escaped. It also puts the form into a loop if you make any data changes because you dirty the record and that causes the BeforeUpdate to run and then the AfterUpdate event where you dirty the record again sending Access back to running the BeforeUpdate event and then the AfterUpdate event where you dirty the record --- are you getting the picture? In earlier versions of Access, this would actually put Access into a hard loop and make the screen flicker as it kept running through the three events over and over and over again. In A2010 and possibly A2007, Access detects this problem and breakes out of the recursive loop gracefully but you should still fix your code.



Table level validation is limited so as I said, you can't do much there. If the BE will NEVER be used by another application then it doesn't much matter where the validation is except that whatever you can do at the table level, you only have to do once. Whatever you do at the form level, you must do in every form that updates/adds the data
 
I probably did it the hard way and put it in all of the after update events of the text boxes and comboboxes. It operates abit like a nag screen :)

There are two values that pop up (visibility=true if checks) that I must have completed so once they do this it leaves them alone but I get what you are saying with regard to the data validation...Im gonna be so pleased with myself if this all actually works lol.

You would never know I work for a huge corporation, you'd think they would have people for this stuff! Not rely on me and internet access!!
 
You REALLY need to move the validation code to the BeforeUpdate event so you can cancel the updata when you detect an error. Just warning people does nothing. They can just ignore your warning and save the bad data.
 

Users who are viewing this thread

Back
Top Bottom