'Fail Safe' data entry

[Jim Williams]

I'm working on designing a database for my employer to track training,
and I have a multiple results entry form - the boss likes the idea, but
wants us to enter both the internal id we assign AND their employee id
to make sure we don't miskey the scores into the system. Any thoughts
on how we should go about this? Its been baffling me for a few days
now.

I almost thought it would be handy to have the form display the
student's name once the student and/or employee id were entered into
the form to allow a third level of verification as well. Is something
like that possible from within a form?

Thanks for any help anyone might be able to provide!

 

[John Vinson]

I'm working on designing a database for my employer to track training,
and I have a multiple results entry form - the boss likes the idea, but
wants us to enter both the internal id we assign AND their employee id
to make sure we don't miskey the scores into the system. Any thoughts
on how we should go about this? Its been baffling me for a few days
now.

I almost thought it would be handy to have the form display the
student's name once the student and/or employee id were entered into
the form to allow a third level of verification as well. Is something
like that possible from within a form?

Thanks for any help anyone might be able to provide!

I'd REALLY suggest using the form tools that Access provides - combo
boxes for example. Making users type in an ID number and then
punishing them when they miss a keystroke is just plain rude, given
that you can help them by letting them pick from a list (which can
show the name, the employee ID, or whatever).

Users should certainly NEVER need to even *see* an Access Autonumber
value, much less type it in!!

If you have two different identifiers stored in your table (say, name
and employee ID) you can certainly validate the entry. You could have
an unbound textbox on the form into which the user would enter the
confirmatory ID, after having entered the first ID. You'ld put VBA
code in the Form's BeforeUpdate event to compare the value in the
unbound textbox with the ID stored in the table:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me.txtVerifyID <> Me.EmployeeID Then
MsgBox "Please check this person's identity, EmployeeID mismatch", _
vbOKOnly
Cancel = True
End If
End Sub

John W. Vinson[MVP]

 

[gibsonsgman]

I'm working on designing a database for my employer to track training,
and I have a multiple results entry form - the boss likes the idea, but
wants us to enter both the internal id we assign AND their employee id
to make sure we don't miskey the scores into the system. Any thoughts
on how we should go about this? Its been baffling me for a few days
now.

I almost thought it would be handy to have the form display the
student's name once the student and/or employee id were entered into
the form to allow a third level of verification as well. Is something
like that possible from within a form?

Thanks for any help anyone might be able to provide!

I'm not 100% clear on what you are looking for, but from what you say
you want to enter 2 ID's and have the database check to make sure they
are associated with eachother? If this is the case, here is what you
can do.

Private Sub Command2_Click()
Dim stX As String
stX = "X"

' get employee id
stX = Nz(DLookup("[EmpID]", "[Table1]", "[StudentID]='" &
Nz(Me.StudentID, " ") & "' AND [EmpID] ='" & Nz(Me.EmpID, " ") & "'"),
"X")

If stX = "X" Then
MsgBox "Those EmpID and StudentID's do not match, please enter
new ones"
Exit Sub
End If

'put some event here
End Sub

i put this in a command button, so it you follow this example, the user
would have to enter both names click enter and it would possibly take
them to another form to enter scores. Another option is to manipulate
this coding to work in the after update events of both text boxes. if
you have any questions about this code let me know.
~Brian

 

[gibsonsgman]

i noticed someone else responded before i finished my entry. Just to
let you know, the first response is only for if you have two of teh
same ID's that you are comparing. If you have two different ID's that
you want to compare then you need to use my method otherwise the first
one will work just fine.

 

[Jim Williams]

Thanks to all who responded. I ended up convincing TPTB that this was
not a 'smart' way to do this - what I did was created a combo box that
once the ID was entered displayed the name, so they can confirm that
they are entering a result for the correct person.