validation rule

[Christos]

I am a newbie, i just started useing access 2000.

i have a table Sport and a table SportPoints.

In the table Sport there is a field called MaxPointsAvailable:

e.g. for tennis the MaxPointsAvailable is 30
for squash the MaxPointsAvailable is 40

In the table SportPoints i have a field called Points and a field called SportTitle.

What i am trying to do is to write a validation rule in the field Points (which is a Number) to say :

Depending the SportTitle (e.g. tennis) the max value that can take is 30 and so on...

with other words to be something like:SportPoints.Points <=Sport.MaxPointsAvailable

Can please anyone help me.

 

[Jack Cowley]

I assume that you have a form for data input otherwise you cannot do any validation checking.

In your forms Point control Before Update event code similar to this should do the trick:

If Not IsNull(Me![Points]) Then
Select Case SportTitle
Case "Tennis"
If Me.Points > 30 Then
MsgBox "Max. entry allowed is 30"
Cancel = True
Me.Points.Undo
End if
Case "Squash"
....etc...
End Case
End if

Search help for more information on the Before Update event and Select Case....

hth,
Jack

 

[Christos]

yes but

No i dont have a form...

but when you open the table in design view and you click on data type, down to field properties there is a field called validation rule. you can write the validation rule there.

Am i right....?

Thanks for the help

 

[Jack Cowley]

You should not allow your users to work directly with tables. You have no control over what they do in this circumstance. For Access to work, as it was intended, you must use forms as this is where all the 'action' takes place. Tables are where the data is stored and Forms are the windows on that data. You must use forms, queries and reports if you want to create a user-friendly database where you as the programmer have some control over the type of data, validation rule, etc....

Jack

 

[Christos]

yes i know but

i know... but just for myself...To prevent me inserting a point bigger than the max point when i am working directly with tables.

Is it possible to tell me how can i do it.?

Thanks for the help.

 

[Jack Cowley]

Sorry, you can't do it... Tables have no way of triggering code and since you have multiple possibilities you need to use code.

Jack

 

[Christos]

Thanks

thanks at least now i know that this cannot be done.

I asked because when i am writing e.g. <= 100 in the field *validation rule* is not letting me type a number more than 100 and i was wondering if i could write a code for that.

Thanks for the help

 

[Jack Cowley]

Since you are just getting started in Access I suggest that you start using forms now rather than later. Eventually you are going to want more control over data entry, data display, etc. so start using the tools at your command now rather than at some time in the future... That is just my opinion and you can do whatever works best for you.

Good luck with your project!

Jack

 

[Christos]

ok...

thanks for the help

and i am not doing any project!

 

[Jack Cowley]

You are welcome....

Jack

 

[Christos]

thanks

I am sorry if i sounded rude...

I do appreciate the help.

Christos

 

[Jack Cowley]

Not a problem Christos. I am glad to help you...

Jack