common field problems on forms

[deejay_totoro]

Hello all.

If you have a chance, could you help me with this problem?

Here is my problem.

I have 2 tables. I created a main form (table 1) and then a subform (table2) on the main form. The 2 underlying tables are joined by a common field.

Now the problem is: if I change the information in the joining field (for example, a spelling mistake when originally entering the data), the corresponding record in form 2 is wiped. Its as if Access is saying "ah! you changed the field information in the common field, so this must be a new record."

Now the thing is that the information is not actually erased. When I look at the tables 2 I can see all these extra records where spellings have been changed.

So, how can I stop this happening? Why is it happening?

Thank you very much in advance.

Dj_t

 

[AncientOne]

how did you create the form/subform? What is the recordsource for your form and your subform? Give an example of what you change for this fault to happen.

 

[neileg]

You need to look at how you enter the data.

If table 1 is joined to table 2 in a one to many or a one to one relationship then, if the form/subform relationships are properly setup, the data in the 'joining field' should only be entered once, ie in the mainform. The record in table 2 will inherit this same value.

If table 2 is a lookup table where you select the entry from 2 that you want to match to the record in 1, then you should use a combo or a list box so that the user has to choose a valid entry.

 

[AncientOne]

To put it another way:

The records on your subform are from rows in Table 2, which contain data corresponding to the key fields on your main form.(table1). If you alter the main form key field, the records in Table2 no longer match with that key field, so they disappear. They are orphans. They don't get deleted, but they can't be accessed. To get them back, you must change their join fields to the same value as the amended main form's record.

You can do this with an update query.

The best thing is to hide or disable the key field on the main form, so you can't amend it in the first place.

 

[deejay_totoro]

is it a relationship thing?

Thanks for the replies.

Still cant work it out though. Let me show you an example.

I created 2 tables, called table 1 and table 2:

Table 1:
BandID (Autonumber & Primary Key)
BandName
AlbumName
YearOfRelease
Rating

Table 2
BandName (joining field – gets its information from BandName in Table 1)
AlbumReview
FanComments

Next using the form wizard, I created a form using table 1. I opened the (table1) Form in design mode, and used the subform wizard to add a subform based on table 2. The wizard was setup to “Show Table 2 for each record in Table 1 using BandName.”

Now if I open up form 1, I can start entering data – and the bandname data is correctly passed to the BandName field in Table/Form 2. Data (at this point) is stored correctly.

Now here is the problem – lets say I made a spelling mistake when entering the first bands name. If I change the BandName field on form 1, then the information on (sub)form2 is cleared (becomes orphaned?)

So, is it something to do with the relationship set-up?

Thanks again.

Dj_t

 

[neileg]

Don't use BandName in Table 2, use BandID. Otherwise, what is the point of having the BandID? This is there to uniquely identify the record, even if all the data changes.

 

[deejay_totoro]

primary key?

Hello,

Thank you for the helpful replies.

Maybe my understanding of a primary key is wrong?

For example, I thought that the primary key is a unique field - something that is unique to a particular table, something that uniqely identifies that table?

Now, if I want to share data between tables, then the best way to do this is to link by a common field. Thats why I included BandName on the second table, as this would be make most sense.

But it would appear that I should only link tables via a field that will not change? Is that correct?

Many thanks again.

dj_t

 

[neileg]

You're 90% correct. When you use the value of a primary key in another table to link records it is known as a foreign key. The second table may have its own primary key, but this won't be the same as the foreign key.

You are quite correct that the reason for using it is that it doesn't change.

 

[deejay_totoro]

limited in choice linking field?

Hello,

So, does that mean that the the choice of a "linking" field is quite limited?

It could really only be an acccess generated autonumber, as this would never change.

If another source of data was used for linking tables (for example, address, or bank number, or car registration) then they would be all open to error because of the possibilty of spelling mistakes.

Is that right?

Thanks!

dj-t

 

[neileg]

The choice is unlimited, you can use anything you like, as you have. However, you correctly identify some of the problems and many developers use an autonumber as a first choice.

On the other hand, with properly designed input forms, you will be entering this linking fields only once and populating both tables at the same time. Therefore a spelling mistake would would exist in both places and the link would still work, unless you ended up with a duplicate value in your main table primary key. It would be unusual to allow the linking data to modified by the user, so this tends to exclude anything that requires user input.

Use of a non-autonumber PK can be appropriate when data is imported from outside the database and the common key already exists in the data.