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

[PlasticMonster]

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....

 

[GinaWhipp]

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.

 

[PlasticMonster]

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!!!

 

[PlasticMonster]

An alternative solution might be to use an if statement in VBA to change the validation property? Is this possible? Google isnt helping

 

[speakers_86]

You should read Gina's post over, you misunderstood.

 

[GinaWhipp]

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.

 

[speakers_86]

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?

 

[GinaWhipp]

@speakers +1

 

[PlasticMonster]

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).

 

[PlasticMonster]

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!!