Access DB question

[Monster Dave]

Hey guys,

I'm having a bit of a brain fart and need some help with a database question:

I'm using Access 2007 and I have a table with Patient ID's that share relationships with other tables. I need to be able to offer the user the ability to change the Patient ID from within a form.

Any ideas what the easiest way to do that would be?

Thanks!

[cyrus buelton]

I'd need your Access File to see how the tables are set up before I can troubleshoot the easiest way to do this.


However, since you said "patient" I figure this must be medically related and you can't send it to me because of HIPPA Laws.

[cyrus buelton]

You should just be able to change in the initial input form.......

or


Is that field locked or a auto-number?

[ryandalling]

You also need to worry about linked tables using the patient number as a key. If you update the number, then the linked data in other tables would not link due to mismatch in the key.

[Monster Dave]

This is where I'm having the brain fart today.

I have a form for adding a new patient - that data includes a PTID and a screening date along with various ethnic information data.

Once that data is entered it goes (obviously) into a table that drives all info related via other tables (1-to-1 cascading). I need to offer an "Edit PTID" from a form but (here's the brain fart), how can I have a user edit the PTID without doing it from within the table itself?

[Monster Dave]

You also need to worry about linked tables using the patient number as a key. If you update the number, then the linked data in other tables would not link due to mismatch in the key.

Since the data cascades would that still mean that the link would mismatch?

Crap - some days this comes so easy, others (like today) I'm struggling with retarded issue.


**Edit - so I tested changing the PTID in the source table directly and the ID change did cascade down through all the linked tables.  

Scratch that part off at least as an issue.

[cyrus buelton]

If you have one main table that drives all your sub-tables / forms, then yeah, you'd only be able to change it in the table at the top of the hierarchy.

[Monster Dave]

Right, that's what I want to do but not from within the table - from within a form.

(tough day today for MD)

[cyrus buelton]

I am not sure that is possible.......

[Monster Dave]

If there's one thing that I've learned about building databases, it not "if" it can be done but "how".

[somegirl]

How about 2 separate IDs - one is your key between tables, one is the externally viewed one that can be modified?

[cyrus buelton]

How about 2 separate IDs - one is your key between tables, one is the externally viewed one that can be modified?

That's a pretty good idea.........

[Monster Dave]

How about 2 separate IDs - one is your key between tables, one is the externally viewed one that can be modified?

That's not a bad idea - however, the slew of tables already in relationships are working from the PTID as the key. So adding a second ID would only muck things up.

I'm still working on it and having a better day today as far as conceptualizing the form.

[cyrus buelton]

Access is probably one of the worst programs ever designed.

It is confusing and just plain sucks.

[Monster Dave]

Access is probably one of the worst programs ever designed.

It is confusing and just plain sucks.

I wouldn't go that far. I actually really like it and have seen some amazing work done with it. You just have to be a little "creative" sometimes!