referencing Table ForeignKey - Import/Forms

[flebber]

Just wanted a bit of advice regarding 2 things about foreign Keys.

1. When I am importing data into a table and I will be importing data into the foreign key should I be using an append query on that field only. That would then allow the referenced table to either update or choose Fkey ID in table wouldn't it?

2. When using a form for input I notice some people use lookups for the foreign key so the user knows what field name to select, eg its far easier to select Bobs Electrician than know the supplier key is 23 say. However the access http://www.mvps.org/access/tencommandments.htm ten commandment say in commandment two that this is evil. Now the only other workaround I can think of is to have to base the form off a query not a table, is that the best method?

For reference these are my tables

 

[ted.martin]

If its legal and it works - then go for it!

 

[Rabbie]

Flebber, I think you may have misunderstood the advice in the 10 commandments. What is being advised against is the the use of look-up fields in the table fields not the use of look-ups in forms where they help to make a better user interface.

 

[vbaInet]

1. When I am importing data into a table and I will be importing data into the foreign key should I be using an append query on that field only. That would then allow the referenced table to either update or choose Fkey ID in table wouldn't it?

Ensure you've enforced referential integrity so it flags up FKs that are not present in the primary table. You use an update query if you want to change or amend existing data. Append queries are for inserting new data. On the basis that you're "importing into", I would imagine you need an Append Query.

2. ...Now the only other workaround I can think of is to have to base the form off a query not a table, is that the best method?

Yes, it is common practice to base your forms on queries.