Do the relationships work correctly for the old records? If so, why do you
want to change them? If not, changing them may not fix what's wrong.
If your UPIN primary key for a particular record is 1234567, and relates to
FK fields, those FK fields also contain the number 1234567. Now let's
suppose you add a new field, and for the record with UPIN 1234567 you now
have 10001 in the new field. Now you can change 1234567 in the FK fields to
10001, and you will end up with *exactly* the same relationship you had
before. The child records will relate to the parent record, the same as
before.
Now you have NPI instead of UPIN. When you create a new doctor record, add
this code to the After Update event for the NPI text box:
Me.UPIN = Me.NPI
For new records, the UPIN is the same as the NPI. To avoid confusion, in
the form's Current event:
If IsNull(Me.NPI) Then ' The doctor does not yet have a NPI
Me.txtUPIN.Visible = True ' Show the UPIN text box
Else
Me.txtUPIN.Visible = False ' Hide the UPIN text box
End If
When all the doctors have an NPI record, you can just remove (or permanently
hide) the UPIN text box. The text box doesn't need to appear on the form.
The field is in the underlying table, and its value is being assigned
programatically.
In the meantime, doctors with a UPIN but no NPI will show the UPIN text box
on the form. Doctors with a NPI will show only the NPI text box. The NPI
value has been assigned automatically to the UPIN field (the After Update
event, above), so UPIN can still be used for the relationships.
In all cases, the UPIN field continues to provide the relationship.
I offered a suggestion in this thread because I noted that nobody else had,
and I had an idea that would probably work. However, it is the only
suggestion I have. You would need some sort of update query, I expect, to
accomplish what you would like to do, but I can't suggest how to go about
that. Your best bet would be to start a new thread, and to specifically
state that you wish to add a new key field to the main table, and to
reestablish the existing relationships so that they are with the new field
rather than the current (UPIN) field.
Don said:
Thanks for the ideas. The problem remains that I have old records that I
still need to associate. I think that I will take a different approach
that
might make more sense. I have created a form that contains the UPIN, NPI,
and
a new number that I will call PK. The PK number can be anything that I
want
it to be. This form contains several subforms in datasheet view that are
linked as child fields to this new PK control.
Now lets suppose that I want to change every related table in the database
that is associated with the PK -- every one of these associations show up
in
my new form and all I have to do is select the column and then use a
find/replace function to change the ID throughout the database. I have
tested
this and it works fine for my needs, but it is a bit labor intensive.
Is there a way to set a macro or some sort of control function that would
all me to select all subform columns and perform a search and replace in
one
easy step? For example, I want a control button that when selected would
ask
"find what" wherein I would indicate the old UPIN or NPI number and then
be
asked "Replace with" wherein I would enter the new PK number. When I
execute
the command, it would find and replace the values in every subform/table
that
is visible.
--
Thanks!
BruceM said:
If you had used autonumber as the PK, you could change other information
about the doctor such as name, address, UPIN, or whatever without
affecting
the relationship. The PK field is used in relationships, but it does not
need to have any meaning. You did not use autonumber, so you can use
UPIN
in the same way as you would have used a "meaningless" autonumber field.
For new physicians, assign a number in the UPIN field, but don't show it
to
them. Depending on the number's format, a way can be found to increment
the
number automatically, or you could just assign the NPI as the UPIN: if
the
record is new (i.e. when creating a record for a new doctor), in the
After
Update event for the text box bound to the NPI field, copy that number to
the UPIN field. There is surely a way to show or hide the UPIN text box
depending on whether the doctor had an active UPIN (by checking for
StartDate in the form's Current event, or something like that).
Look at it this way: if you were planning to add any other field to the
table, would you be concerned about the relationships? For instance,
suppose you had neglected a suffix field (Jr., Sr., etc.). If you add
that
field, then modify records so that some of the doctors have an entry in
that
field, would you be concerned about records that were created before the
field was added?
Whether or not the UPIN is the primary key, I am still faced with the
dilemma
that the parent/child links in my forms and reports are mostly related
to
the
UPIN field. Physicians that have been in the program in the past will
always
have a UPIN and an NPI number, but the use of the UPIN is being phased
out.
New physicians entering the program will only have an NPI. If the UPIN
is
123
and the NPI is 456, they will never see themselves as equals when it
comes
to
relationships. If I have a table that has two controls in it for UPIN
and
NPI, is there no way to programmically tell the database that 123 must
link
with 123, but if 123 doesn't exist, 456 is also ok?
I hope that makes sense. In other words, doctor 123 has been around for
years and I will always want to find his records, but now days he is
referred
to as 456 and I want to see all records with his 456 number as well as
all
of
his records with his 123 number.
--
Thanks!
:
One approach is to continue using the UPIN as the PK, and to add the
NPI
field. It would have the same effect as using an autonumber PK,
except
that
the PK will be visible to the user until UPIN is completely replaced
by
NPI.
After that, you can hide the UPIN field. You would have to come up
with
a
way of incrementing the UPIN field behind the scenes; the method would
depend on the UPIN format. UPIN and NPI would be separate fields in a
single record, so would be "recognized" as synonymous.
For the very reasons you have described I tend to stay away from an
assigned
number as the PK.
In the healthcare industry, physicians are given a national
identifier
number
similar to a social security number. This ID is called a UPIN and I
have
multiple tables, forms, etc, that use this [strUPIN] as a primary
key
or
to
link other elements, subforms, etc. together. This national
identifier
was
recently changed to an NPI number which is 15 characters instead of
7.
The
problem that I have is that insurance carriers can still use the
UPIN
until
the physicians get an NPI number to replace it. If users of our
database
were
to replace the current UPIN number with the NPI number in the
[txtUPIN]
field, then all links to subforms, reports, etc will be severed. If
we
create
a new record using the NPI, then I cannot link to any historical
records
that
used the UPIN number.
Currently we are using the same field [txtUPIN] and treating these
two
IDs
as synonymous elements, but if the provider already had a UPIN on
record,
they are to create a new record using the NPI. In this instance, the
physician exists twice in the database.
Is there any programming or process that can be used so that we can
have
two
separate fields, e.g. [txtUPIN] and [txtNPI] and have the database
recognize
them as synomymous? I have created a table with both of these fields
and
associated them via a simple query, but I don't know how to tell the
database
that if one field element is null, then to look at the other
associated
data
field as if it were the same.
