Entering data twice for verification (1 Viewer)

[iamratman]

I have a db with the following fields: ID, Customer first name, customer last name, account number, date, time, score1, score 2.

My problem is this: We are running a promotion in which the customers receive a score. This score must be entered correctly as we are highly regulated, however the users are constantly fat fingering or miss typing the data. I want to force the score 1 and score 2 fields to match before the record can be saved.

Any suggestions on this would be greatly appreciated; I have to go in every night to correct these errors manually, which defeats the purpose of this db.

 

[Laurentech]

One easy way would be in Score2's afterupdate event put:

If Me.Score2 <> Me.Score1 then
Msgbox "Score 1 and Score 2 must match!"
Me.Score2=""
Exit Sub
End If

 

[iamratman]

Thank you very much for that tip. I tried it out and it worked with one exception. The message pops up telling the user the fields don’t match and the save button will not let the record be saved, however the add new record button puts the data into the table regardless of what the data is.

Another common mistake: the users are putting the account info into the score 1 or 2 fields, I don’t know how they manage but they do. It would be awesome to be able to say data from account field cannot match data in score 1 or score 2. Any thought here?

Thanks again.

 

[iangirven]

scores

try the following in the on exit event and see if that works
if isnull(Me.Score2) = True then
MsgBox "please enter Score2"
DoCmd.cancelevent
exit sub
else
If Me.Score2 <> Me.Score1 then
Msgbox "Score 1 and Score 2 must match!"
Me.Score2=""
DoCmd.CancelEvent
DoCmd.GotoContol (Me.Score2)
Exit Sub
End If
End if

I may not have the syntax correct so please check on this. Hopefully this will require a score to be entered and if the score entered doesnt match score1 will display the message and return you to score2.

regards
ian

 

[iamratman]

Hello Ian,

thanks for the reply.

When i run your code I am taken to the debugger with the following error: Compile error: Only comments may appear after End Sub, End Function, or End Property.

I put your code on the score 2 on exit event, I asume this is where you meant for it to go.

 

[Laurentech]

I believe he meant for you to put the code in the Form_Close () event.

 

[iamratman]

I apologize for my lack of knowledge here but...

I don’t know where to find this: Form_Close () event.

On the properties of the form itself there is: On Close event.
Also on other functions properties there are ON exit functions.

Am I in the ballpark here?


Thanks

 

[Pat Hartman]

The CORRECT event to use is the FORM's BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Score1 = Me.Score2 Then
Else
    Cancel = True
    Msgbox "Score1 must = score2 and they may not be null", vbOKOnly
    Me.Score1.SetFocus
End If
End Sub

"Cancel = True" cancels the update if the two fields are not equal. "Msgbox ..." displays an error message and "Me.Score1.SetFocus" moves focus back to the Score1 field. That's it! That's all you need but you MUST put the code in the correct event.

 

[iamratman]

Ahhhhh, very good.

Now it works. Thank you Pat.

Now all I need is: data from account field cannot match data in score 1 or score 2


I would love help with this if any one is up for it.

 

[john471]

Adding on to Pat's piece....

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Score1 = Me.Score2 Then
    If Me.Score1 = Me.YourAccountFieldName then
        Cancel=True
        Msgbox "The Score must not be the same as the Account.", vbOKOnly
        Me.Score1.SetFocus
    End If
Else
    Cancel = True
    Msgbox "Score1 must = score2 and they may not be null", vbOKOnly
    Me.Score1.SetFocus
End If
End Sub

 

[iangirven]

Correct event

Pat,

Thanx for pointing out the correct place for the code. Its been a while since ive done any real coding but am getting back into it.
its good to see that while trying to help someone out I can still learn things.

cheers
ian

 

[Juan]

This is a nice idea to ensure correct data input, but it's possible for a "lazy" user to simply copy and paste the incorrect data from the first textbox to the second. Is it possible to disable copy and paste, as well as CTRL-C and CTRL-V, whilst the data is being entered into txtboxes1 and 2.

 

[iamratman]

very cool

Thanks you John471, very cool.

That works very well. This db is shaping up nicely thanks to all of you and I am learning quit a bit.

I can continue to add functions I need if there are willing helpers.

The next complaint I am getting is that our customers are only allowed to be entered into the system 3 times per day but the users are putting them in more than that. So some kind of function that limits the account number to being put in only three times on a given date. This may be a post for another area of the forum I'm not sure.

 

[iamratman]

I like that idea Juan.

 

[Juan]

I like that idea Juan.

That's the advantage of posting ideas - others might just think of something that's not been considered before. We just need someone to say whether it's possible to disable copy and paste temporarily and, if so, how!

 

[john471]

Will your users have access to a "paste" option through either the main menu bar, or a shortcut menu bar (right click)? If so, then there's not much point disabling the keyboard shortcut (ctrl V) in the relevant fields.... however if the only way would be via the keyboard then you can use the key Down/Up events to check the key combination and "nobble" attempts to "ctrl-V". i.e. have you, or could you, remove paste menu options from the form?

 

[Juan]

I'd hope to be able to disable ALL attempts at copy (or, more importantly, paste) whilst the data is being entered. That would include the copy/paste icons as well as keyboard shortcuts and menus.

I've done a bit of googling and it seems as if this hasn't been done [ yet :-( ]. I just wonder if one of the gurus here might just come up with a novel "lateral" approach to this as I suspect it may be useful to many folks.