Verfication of user entered data

  • Thread starter Thread starter Resurrection
  • Start date Start date
R

Resurrection

I have searched but am still lost. Heres the issue: My database
tracks employees by what station they are working on a given day.
Because I have upwards of 800+ employees (and no IT dept) my main table
is Personnel which contains all relevant personal data. I had to
create a UserID for each employee that consists of LASTNAMEFM (where F
and M are the first and middle initials). I figured out the VB code to
make that happen without leaving it up to a data entry person to mess
up since that is how all my employee relevant data will be tracked and
I cannot have duplicates or errors.

That has to be that way because at the user end, a critical part of the
db will be allowing users to input a roster of stations and who is on
each Station for the day by the appropriate user name and people won't
want to input by some other form of UserID. I built a table Roster
(with Station fields and using a Date as the primary key) and a form
Rosterform that can take in the relevant data.

So how do I verify that a user has input a correct UserID and not just
entered part of a last name ? With this many employees I don't want to
try and have a list or combo box, as it would be huge. I'm figuring I
need to have VB code associated with the Before Update event, and I
think the way to do it is to step through my UserID field (from
Personnel table) comparing it to the data just entered in the Current
Focus.

Am I right or wrong and how do I implement this? Is there a better way?
 
Resurrection,

I would use a combobox for the entry of the employee, with the Limit To
List property set to Yes. 800 in the list isn't too many.

If you want to use code, as you say the form's Before Update event would
be good, one approach would be like this...
If DCount("*","Personnel","[UserID]='" & Me.UserID & "'") = 0 Then
MsgBox "This UserID doesn't exist."
Cancel = True
End If

When constructing this UserID, did you cater to the possibility that you
could have two emoloyees with the same name?
 
Using a combination of fields to make a Key is a violation of database
normalization rules. The key should not depend on any of the data in the
table. What happens if you hire John J. Jones, Jr.? What happens when Joanne
J. Smith marries John J. Jones, Jr.? Another fine mess you've gotten us into
Stanley.

Just use an Autonumber as an ID and use a combo box with a concatenated full
name and the ID (which should be hidden). Although it uses a listbox, this
is an example of what you need to do:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
To Steve Schapel: Thank you for the help. After all the effort I
spent researching this before giving up and posting, I think I will try
the coding solution. If I am unable to do it, I can fall back on a
combo box. Fortunately, my number of employees is pretty much static
and won't change beyone +/- 50. I will be back here if the syntax
keeps getting the better of me.

To Arvin Meyer: Thank you for your concerns, and I have no doubt that
you have had to tell people about normalization and the dangers of
calculated fields many many times. Bear with me as I think out loud
for a second: Unfortunately, this is a solid case of me knowing my
end users. They will want to input their roster data by using last
name only, as this is what they currently do in Excel. On occasion
when they have a mutiple of the same last name on a roster, they
usually invent some creative ways of telling people apart on paper and
they will absolutely balk at having to use anything other than last
names. I have read countless documents over the last week telling me
exactly what you said, but I know no other way. Even if I used a
different UserID system, like an AutoNumber for the primary Personnel
key, I would eventually finding myself having to do some kind of a
verification of name data from a roster to determine which employee I
am tracking data on. I agree that this isn't the "right" way, but
unfortunately I find it easier to work around my users, rather than to
do the social engineering required to change their habits. It is
already a long stretch to force them to use last name with initials in
their rosters.

Any ideas on how to "code" a better db that follows your suggestion but
that still keeps my users from revolting?

For what its worth, like I said before, I do understand the dangers of
a calculated field being stored in a table (and used as a key) so I am
taking measures to ensure the integrity of the data by locking down any
ways of changing my UserID once its created.
 
Arvin said:
Just use an Autonumber as an ID and use a combo box with a concatenated full
name and the ID (which should be hidden). Although it uses a listbox, this
is an example of what you need to do:

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...

I'm looking through your files now, but not quite seeing what you want
me to see. For some reason when your form is opened it appears as a
blank white form. But I can see it in design view. I will see if I
can follow what you are trying to show me.
 
Resurrection said:
I'm looking through your files now, but not quite seeing what you want
me to see. For some reason when your form is opened it appears as a
blank white form. But I can see it in design view. I will see if I
can follow what you are trying to show me.
Click to expand...

Ok, I got it now, just had to open your sample exclusively and I can
see the form. You have given me some great ideas about how to do the
data entry on my Rosters form. Workers are devised into "sections"
that are numbered. So each person on a roster will belong to a work
section. Citing your example, if I put a box on the form that lets a
user choose a number for a work section, I can then display just the
users in that work section. This cuts down a users choices of names
down from 800 to about 80. If I put these in a combo box based off
what work section is chosen with AutoComplete turned on and limiting
the data to the list, then I have solved the data entry problem by not
allowing a user to make errors, and it requires no coded verification.


I should then be able to track data by an auto-numbered UserID, right?
Or am I missing something, it can't be that simple?
 
I should then be able to track data by an auto-numbered UserID, right?
Or am I missing something, it can't be that simple?
Click to expand...

Well, it's not *quite* that simple. An autonumbered UserID is unique,
but it's meaningless. There's nothing to prevent you from having the
same person in the table twice, with different UserID's, and there's
nothing to distinguish ID = 319, Fred Brown, from his son ID = 448,
Fred Brown. You'll need some other field (which can be displayed in
the combo) to provide a visible cue for which Fred is intended.

John W. Vinson[MVP]
 
Arvin said:
The key should not depend on any of the data in the
table.
Click to expand...

Misstatement! You have that completely the wrong way around. See:

http://en.wikipedia.org/wiki/Natural_key
Natural key
"a candidate key that has a logical relationship to the attributes
within that row"
Using a combination of fields to make a Key is a violation of database
normalization rules.
Click to expand...

Misstatement! You are probably confused about 3NF:

http://en.wikipedia.org/wiki/Third_normal_form
Third normal form

"to check if all the non-key attributes of a relation depend only on
the candidate keys of the relation"

Note the use of "non-key attributes."

Jamie.

--
 
There have been long discussions about the use of natural keys. I think
they're useful under certain narrow circumstances, like the use of a State
abbreviation as a key. Notice I did say not depend upon any data in the
table. A natural key is (or should be) that data, not a repeat of any part
of it. Perhaps I could have stated it better when I said that a combination
of fields is a violation of normalization. One can use a compound unique
index as a key. What I meant to say is that the key cannot be decomposed
into data that is available in other field. Specifically in the manner of
concatenating 2 or 3 fields together to make a key. The data is being
repeated, and the field is decomposible, and that is a violation of 2NF and
5NF.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
Back
Top