Update Field from Unbound Combo

[TSSTechie]

Howdy,

I have a form that has an unbound combo. The rowsource for this combo is the primary key from the forms underlying table.

What I would like to do is save the value of this unbound combo back to the underlying table to replace the value that was selected in the combo thus updating the current record with a new primary key value .

Can anyone advise on how to do this ??

Thanks in advance

TSSTechie

May The Force Be With You

 

[TheAceMan1]

How are ya TSSTechie . . . . .

Changing the PK will never be the best thing yo can do to any database. I hae have to ask . . . .

[blue]Why do you find it necessary?[/blue]

See Ya! . . . . . .

 

[TSSTechie]

Hi TheAceMan1,

This database lists staff details. The primary key is a combination of the member of staffs Surname followed by their initial. I have only just thought of a potential problem with this. What if someone changes their surname (i.e. marriage, divorce, etc.)??

If I have to then I will change the primary key to an autonumber but this will mean a lot of reworking the database which hardly seems worth it considering how often this problem is going to come into play. Is this going to be possible ??

Thanks again

TSSTechie

May The Force Be With You

 

[RickSpr]

Reconsider whether switching to an autonumber means reworking the database. I doubt that it does. All you should have to do is retain the unique index on the Surname and Initial fields. Just about any existing query (including RecordSource/RowSource properties and data access from VBA) should continue to work fine.

Those of us who cut our teeth on older data management technologies often forget that keys are far less important in relational systems. Relational systems are specifically designed so that indexes can be created and dropped as needed to tune the database performance, without impacting the results of any query. It is for this exact reason that an SQL query defines the result set without including any information about what indexes, if any, should be used. As a fortunate side effect of this, an SQL query should obtain the same results irrespective of what indexes may or may not exist.

In relational theory, a primary key is barely more than just another index. In Jet databases (and in many other DBMSs), its only special characteristic is that rows retrieved in primary key order will have the strongest probability of being physically located near each other on the disk. The only effect this has is on performance, but the performance degradation of using a non-primary-key index will be negligible unless your query's result set is quite large.

So seriously, you should consider whether using an Autonumber really has that much negative impact. You don't truly have to change your foreign keys if you don't want to. (But if you did, that would have space saving advantages--4 byte foreign keys instead of however big the text field is--and performance advantages whenever a staff member's name is changed, because no cascading updates to child tables would be required.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[willir]

TSSTechie

primary key is a combination of the member of staffs Surname followed by their initial

Rick Sprague is absolutely correct. Using a name, specially the last name for the primary key will be problematic.
- name change
- duplicate names, SmithJ = John Smith, SmithJ = Jane Smith

I had to work with a database where we resorted to nubmers...
SmithJ1
SmithJ2
...which sucked.

Changing names with linked fields becomes extremely problematic.

You will save yourself a whole lot of hurt by changing now than resorting to work-around solutions later.

Richard