How safe using combobox to input foreign key

[arishy]

In a Related Tables, you need to enter a FK to link to the other table.
You either key in the FG from memory !!!!!! or use help.
The standard procedure is to design the field as combo box AND use SQL to select the field that will help to input the ID of the other table. So, if you want to input foreign key 3 , the combo box will display what 3 is then when you selected Access will insert 3 for you.

My question is since I can later on, edit the table and change the the value to another value, and mess the whole thing up!!! HOW can I lock my first choice so it will stay unedited ?

My second question is: Is this the only way to input the FK if you do not remember the exact ID number or there are thousands of records in the related table.?

 

[boblarson]

My question is since I can later on, edit the table and change the the value to another value, and mess the whole thing up!!! HOW can I lock my first choice so it will stay unedited ?

I don't know that you are thinking of database design in the proper way as I can't fathom a situation which would occur as you have suggested.

You would use a table for lookup values for the combo box where there is a PRIMARY KEY for each value. That value represents the FK when entered into the storage table. For example, if I have a products table, each product has a primary key. When I make the selection for a product for entry into my Sales Detail table the key from that products table goes into the sales details table. Now, why would that product change? The key and associated value should not. If another similar product came out, it would get its own primary key and would be used. You should NOT be entering the values in a combo box's Row Source using a Row Source Type of VALUE LIST unless they are something like YES, NO, where they will not change. Otherwise you set the Row Source type to be Table/Query and use a table, saved query, or Select statement for the row source.

My second question is: Is this the only way to input the FK if you do not remember the exact ID number or there are thousands of records in the related table.?

You don't normally enter manually a FK. Of course you can but that's where the power of a combo box or list box comes into play. The user can just select what they need.

 

[Pat Hartman]

If you are using natural keys rather than autonubers, sometimes those keys will change (all the more reason to use autonumbers). If you use natural keys and you allow changes to those keys then you MUST specify Cascade Update on your relationships. That way if you were using something really bad for a PK like a person's name and that person got married and changed "his" name (I have a male friend who actually did this), Cascade Update would propagate the key change to all related records.

As Bob already said, you almost never type in an actual ID although you could. Usually, you choose from a combo that has some relevant text value such as company name or department name or customer name, etc. When there is the potential for duplicates, include enough columns in the combo to allow the user to choose the correct item. I know that I am one of a kind but if you found my name twice in your combo, you'd need my address or phone number to help you choose the real me

 

[RainLover]

I have strong feelings about this.

The Primary Key should always be AutoNumber. Some prefer to use a natural key but if they can't find something suitable then they fall back to the ever reliable AutoNumber. So if you stick with AutoNumber you don't need to think of anything else. Cascades are not required. You are guaranteed uniqueness.

Also the Primary or Foreign key are not for the user and should never be seen by anyone but you. These keys are for the programmer not the user.

To entertain the idea that users are going to enter a correct Foreign Key is wishful thinking. It is simply not going to happen.

 

[gemma-the-husky]

I have strong feelings about this.

The Primary Key should always be AutoNumber. Some prefer to use a natural key but if they can't find something suitable then they fall back to the ever reliable AutoNumber. So if you stick with AutoNumber you don't need to think of anything else. Cascades are not required. You are guaranteed uniqueness.

Also the Primary or Foreign key are not for the user and should never be seen by anyone but you. These keys are for the programmer not the user.

To entertain the idea that users are going to enter a correct Foreign Key is wishful thinking. It is simply not going to happen.

sometimes not autonumber

i expect US designers tend to use a text key in their "States" tables - the 2 letter state abbreviation.

With something i really want to control -such as a processing status, or a VAT/Sales Tax code, i use number, rather than autonumber.

so if status 99 is a "dead" job, it needs to be status 99, and not some random autonumber.

but yes, generally autonumber.

fwiw, I never set up cascading deletes/updates

 

[RainLover]

If you teach someone to use Autonumber then you will never be wrong.

so if status 99 is a "dead" job, it needs to be status 99, and not some random autonumber.

It is statements like this that make me believe that there are many people out there pushing their own wheel burrow who really do not know how to use AutoNumber.

As I said in my previous post, the AutoNumber should never be seen by the user. It does not matter if it is 99 or 7777777. What it is is a unique Number that identifies a particular record.

Apologies for my bluntness. I did not intend to insult, just emphasising a point.

 

[gemma-the-husky]

what i mean, is occasionally within a system you HAVE to have arbitrary rules, and you cannot design for a completely relational system. The vast majority of my tables have an autonumber primary key. In a handful of tables, I prefer a numeric key - and in some instances, I need a certain set of specific values in the table.

I think a job status is a case in point.

eg - a dead job needs to be not shown in certain details extracted from the list of jobs, or treated differently in some instances.

so one way is to have a separate boolean yes/no flag for the "dead" record

another way that might be possible is to use a particular status. instead of a proliferation of boolean flags, you can manage everything with a single status field.

now maybe, you could add to the status table, other columns - eg "dead", so that certain random statuses identify a dead record. But then you find you also need a sort key in the status table to ensure that your output gets sorted correctly. Or, more practically, your system needs to prevent you changing a job to a different status without certain crtieria being met. It's all relatively easier if there is a specific ordering of statuses. eg - simply do not allow a user to select a lower value status. Much harder if the status values are random - then it isn't at all easy to determine whether a status can legitimately be changed to a different status

at some point it just become easier to just say that there a certain number of job statuses and they need to be set to certain particular absolute values - rather than try to design a completely relational system, where the attributes of a status alone determine how it can be used.

Instead, with specfiic known status values, your code can test for the (range of) status value and react accordingly.

----
I think there is a similar thing with tax rates/VAT rates

Yes, you could have any (autonumber) code to represent a particular VAT rate. But often it may be easier to just set up the VAT rates table with numbers, rather than autonumbers, and expect that, say

1 is Standard Rate, 2 is Zero Rate, 9 is exempt etc.

or even use text and say

S is standard rate, Z is zero rate and E is exepmt.

------
It's a bit like XML being self documenting. It might be, but people still have to work to agreed common standards within XML documents.

 

[RainLover]

Dave

You have not described one situation where AutoNumber can't be used. If you want to use Z for Zero Rate then add another column.

The primary Key remains hidden away from the user.

I have never used anything else but Autonumber for the last 15 years or so.

I have expressed my opinion strong enough. It will not change your thinking so I will leave you to have the last say.

 

[Mihail]

If you are using natural keys rather than autonubers, sometimes those keys will change (all the more reason to use autonumbers). If you use natural keys and you allow changes to those keys then you MUST specify Cascade Update on your relationships. That way if you were using something really bad for a PK like a person's name and that person got married and changed "his" name (I have a male friend who actually did this), Cascade Update would propagate the key change to all related records.

Thank you for this, Pat.
Finally I have the explanation for Cascade Update option.
Thank you again !