text part assembled into table

  • Thread starter Thread starter David#
  • Start date Start date
D

David#

I need a field on my form that auto-fills for each record
entered and can be written to the undelying table. This
field consists of the first three letters of last name and
last 4 digits of SS. IE "Abe Lincoln - SS123-45-6789"
would be "lin6789".

This would be the primary key in the table, so we'd have
to adjusts, ofcourse, in the case of a duplicate. Is this
possible?
 
If the combination is not going to be unique then it's not a good candidate
for the primary key.

What you could do is on the Before_Update do a DCOUNT in the underlying
table on the "surnameSS" field and if it returns zero set a bound field to
be "lin6789"
 
I'm assuming that your name is actually broken into FIRST and LAST names. (if this is not the case, then I'm really not sure how to break it up).

Create a text box in your form that is LOCKED:YES to prevent someone from changing the value. Your Control Source (Properties/Data/Control Source) for the text box should be

=Left$([Formname]![LastName],3) & Right([IDnum],4

The Left$ is going to pull the leftmost 3 characters from a string (text) field. If you leave the $ off, the calculation will 'think' you are working with a numerical value instead

Typically, when I have locked fields, I make sure I set the Tab Stop to NO and change the background of the textbox to match the detail area of my form - so the users won't expect to be able to change it

HTH
Dere

----- (e-mail address removed) wrote: ----

OOPS - this IS NOT the primary key - duplicates are ok
 
I've got this pulling the right values, but the problem is
storing it in the table. I tried using the concatenated
text parts as the default value in the table design, but
it won't insert the values. Ca't figure out why.

-----Original Message-----
I'm assuming that your name is actually broken into FIRST
Click to expand...
and LAST names. (if this is not the case, then I'm really
not sure how to break it up).
Create a text box in your form that is LOCKED:YES to
Click to expand...
prevent someone from changing the value. Your Control
Source (Properties/Data/Control Source) for the text box
should be
=Left$([Formname]![LastName],3) & Right([IDnum],4)

The Left$ is going to pull the leftmost 3 characters from
Click to expand...
a string (text) field. If you leave the $ off, the
calculation will 'think' you are working with a numerical
value instead.
Typically, when I have locked fields, I make sure I set
Click to expand...
the Tab Stop to NO and change the background of the
textbox to match the detail area of my form - so the users
won't expect to be able to change it.
 
Okay... well, I've done something similar and ended up using a macro
SUBMIT button to send information by way of an Update or Append query.
I saw something earlier today (another post) using an SQL statement
regarding INSERT. Do a search on "Hit Counter" and read the posting
from Tim Ferguson.
Click to expand...

Oh dear... since my name got dragged into this... I wasn't planning to jump
in....

The original post talked about setting up "a field" made up of bits of
other fields. You'd do better to search for "Intelligent keys" to find out
why this is a Really Really Bad Idea. The version is especially bad because
it is not even guaranteed to be unique!

Rule one: you have to, have to, have to, have to, have to find or create a
key that is never going to change and will always be unique. This mangled
bit-of-a-name, bit-of-a-number does not qualify on either criterion. Many
(most?) entities do not have obvious choices, and that is why autonumbers
are so useful and so widely used.

Hope that helps


Tim F
 
Sorry, Tim. I thought that your Hit Counter post made sense to apply here. And I believe one of the posts in this thread earlier mentioned that they did not need to be unique - so I'm not sure about how to tell the difference between 2 people if there are duplicates

Of course, the other thing to consider is if the db is hacked and SSNs are stores somewhere, the company may be looking into a potential lawsuit regarding privacy. Legally, unless it's for pay or personnel records, it's MUCH safer to just ask the users for the last 4 digits of their SSNs. Since this isn't for the security topic, I'd advise David to go to that newsgroup

Thanks
Dere

----- Tim Ferguson wrote: ----

"=?Utf-8?B?RGVyZWsgV2l0dG1hbg==?=" <[email protected]
wrote in
Okay... well, I've done something similar and ended up using a macr
SUBMIT button to send information by way of an Update or Append query
I saw something earlier today (another post) using an SQL statemen
regarding INSERT. Do a search on "Hit Counter" and read the postin
from Tim Ferguson.
Click to expand...

Oh dear... since my name got dragged into this... I wasn't planning to jump
in...

The original post talked about setting up "a field" made up of bits of
other fields. You'd do better to search for "Intelligent keys" to find out
why this is a Really Really Bad Idea. The version is especially bad because
it is not even guaranteed to be unique

Rule one: you have to, have to, have to, have to, have to find or create a
key that is never going to change and will always be unique. This mangled
bit-of-a-name, bit-of-a-number does not qualify on either criterion. Many
(most?) entities do not have obvious choices, and that is why autonumbers
are so useful and so widely used.

Hope that help


Tim
 
Of course, the other thing to consider is if the db is hacked and SSNs
are stores somewhere, the company may be looking into a potential
lawsuit regarding privacy. Legally, unless it's for pay or personnel
records, it's MUCH safer to just ask the users for the last 4 digits
of their SSNs. Since this isn't for the security topic, I'd advise
David to go to that newsgroup.
Click to expand...

The question here is nothing to do with SS numbers or security or hacking.
The OP suggested a really crappy and wrong way to organise his table, and
that was what I was addressing.

The question of storing SS numbers and security and so on are important
considerations for the business model, but nothing to do with what was
being asked. This is page one of any primer on DB design -- if the table
were to be set up in the manner described, then the entire project will
die. That is the point I was trying to make, albeit a little more gently.

B Wishes


Tim F
 
Back
Top