Set Maximum quantity for data entry 1

[rj51cxa]

I have a form which displays four records. Each record has two fields, the practice number of the competiton and the number of hits on a target or, in some cases, the score.
For each practice there is a maximum number of hits or a maximum score.
In use, it has been found that the person entering the data has made a keying error and entered a figure which is greater than either the maximum number of hits or the maximum score.
I would like to set a "Before Update" event to compare the figure entered with a maximum value. If it is less than the maximum, the figure is accepted, if it is greater, a warning message is displayed.
Can anyone give me a steer as to how this can be achieved.
Best Regards
John

 

[RivetHed]

John, I'd have thought that you should be able to set this with a Validation Rule in the textbox properties rather than needing an event procedure.

 

[TheAceMan1]

How are ya rj51cxa . . .

Curious . . . what differentiates a [blue]maximum number of hits[/blue] from a [blue]maximum score[/blue], and how does the user know which to use?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[rj51cxa]

Hi RivetHed and AceMan1,

Thanks for the tip about the validation Rule. I tried that and it works well.

The problem refers to a shooting championship in which each competitor competes in three different competitions. In one of these competitions, the data entry person is presented with a results sheet that gives an actual score. The problem of keying errors is now taken care of by using the Validation Rule.

However, in the two other competitions, the data entry person is presented with a sheet that lists the number of hits on targets at different ranges. For each range, there is a different maximum number of hits e.g. 300 yds 10 hits, 100 yds 5 hits etc. The score for each range (hits x 4)is calulated in the final report.

The form for entering the results of these competitions displays four records - one for each range. Consequently, the validation rule needs to be varied for each record. i.e Record 1 - 300 yds - maximum 10
Record 2 - 200 yds - maximum 5 etc.

I have looked at the Validation Rule section of the field and it would appear that you cannot enter a code, only build an expression. If that is the case, I would need to create a series of "If" expressions. The problem is that I can build the "If" part but not the "Then" part.

e.g
If [LngEventRef] = "21" Then "Max Hits" = "<=10"
If [LngEventRef] = "22" Then "Max Hits" = "<=5"
etc.

([LngEventRef] refers to the field in the underlying table that gives each competition and range a different reference number.)

How can I set up the "Then" part of the expression to change the Validation Rule?

I hope I have explained this clearly enough.
Best Regards
John

 

[RivetHed]

John, you can't use if statements in Validation rules to my knowledge but if you structure it like this it should work:

([LngEventRef] = "21" AND [EntryField]<=10) OR ([LngEventRef] = "22" AND [EntryField]<=5)

And keep going for all your possible validation rules.

 

[rj51cxa]

Thanks for that RivetHed, I'm glad you could understand what I was after. I'll give it a try and let you know.

Best Regards
John

 

[rj51cxa]

Hi RivetHed

I've now had the chance to try out your tip and it works really well. Have a star.

There is one problem, however. The new expression causes a clash with an event that is already included in the form.

I have the following code in the "On Change" event:

Private Sub Score_Change()
If Len(Me![Score].Text) = 2 And [LngEventRef] = "21" Then
DoCmd.RunCommand acCmdRecordsGoToNext
End If
If Len(Me![Score].Text) = 2 And [LngEventRef] = "22" Then
DoCmd.RunCommand acCmdRecordsGoToNext
End If
If Len(Me![Score].Text) = 2 And [LngEventRef] = "23" Then
Forms![FrmCompetitorScores5]![Combo36].SetFocus
End If
End Sub

This code is designed to reduce the key strokes when entering data. It automatically moves to the next record after two key strokes and, at the end of the the third record, it moves to the Combo Box in the main form which is used to select the next competitor.

What I am finding now, is that, having set the Validation Rule, if I make a keying error and then correct it, the code has moved the entry point to the next but one record. i.e. if I make a keying error in record one, I receive the error message. I return to record one and, after I correct the entry, the entry point moves to record 3. It misses out record 2.

It is obviously something to with the number of key strokes and I was wondering if there is any way that the Validation expression and the Event Procedure can be combined in order to avoid this problem.

Any ideas would be much appreciated.

Best Regards
John

 

[RivetHed]

Maybe try running DoEvents at the start of your event procedure?

 

[rj51cxa]

Hi again RivetHed

I put in DoEvents as you suggested but it still jumps all over the place. Is also tells me that, if I enter an incorrect figure in record 3, that the the focus cannot be moved to the combo box on the main form.

The other thing I notice is that, if I enter a correct quantity into a record, the entry point moves to the next record. If I enter an incorrect number, nothing happens until I press the Enter key. Then I get the error message.

What I would like to see is after entering two digits (2 key strokes), if the entry is wrong, the error message appears automatically. I should then be able to correct the error (backspace and re-key), after which the entry point moves to the next record, or to the combo box on the main form. Whether this is possible I don't know.

Best Regards
John

 

[RivetHed]

John,

I didn't realise initially that you already had events running on your form, it may well be easier to validate in your event rather than trying to manage the conflicts between the Access validation and your events.

Maybe try amending to somthing along the lines of this and scrapping the validation rules.

Private Sub Score_Change()

If len(Score) = 2 then

Select case LngEventRef

case is ="21"
if txtEntry <=10 Then
DoCmd.RunCommand acCmdRecordsGoToNext
else
Msgbox "Your error message"
Exit sub
end if

case is = "22"
'Etc etc
End Select
End if
End Sub

 

[rj51cxa]

Thanks RivetHed,

That's cracked it. Just what I needed. Have another star.

Thanks very much for all your help, it was very much appreciated.

Best Regards
John

 

[RivetHed]

No problem