Editing with RecordSet Type = Dynaset

[Access_Novice]

According to the MS website, if the Recordset Type = Dynaset and you have a table with one to many relationships, you cannot edit the field on the "one" side (unless Cascade Updates has been enabled).



http://msdn.microsoft.com/en-us/libr.../jj249050.aspx


In the attached database, go to record 1 of the main form. The mother's name should say Katherine Artemov and gymnast name as Sasha Artemov. Under the parent section, the account number is 0001.

I have the Recordset Type = Dynaset. '
tblParents' and 'tblGymnast' is linked by the primary key called 'accountNo.'
Also, in the relationships window I have the link between 'tblParents' and 'tblGymnast' set so that Cascade Update IS NOT enabled.

Therefore, when I attempt to modify the value in the 'accountNo' field under the "parents section" of the form, which is bound to the table 'tblParents' I should not be able to edit this right?

But if I click inside of the field, I can in edit 0001 and change it to, let's say 0005. Now when I tab out of that field I end up getting a message saying "The record cannot be updated or deleted because table 'tblGymnast' includes related records." But regardless of that message, I thought that I wasn't supposed to even be able to edit and make changes to the value in the accountNo field in the first place, but still I was able to make a change to the existing value in the accountNo field. What am I missing?

[Missinglinq]

...I should not be able to edit this right...

...I can in edit 0001 and change it to, let's say 0005...

......I end up getting a message saying "The record cannot be updated or deleted because table 'tblGymnast' includes related records...

You're not making changes to the Field, you're simply entering data into the Textbox; the Field has only been edited when the Value changes in the Table, and Access is not allowing that.

If you want to prevent the user from even typing into the Textbox, if it is already populated, you can use code like this:

Private Sub Form_Current()
 If Nz(Me.accountNo, "") <> "" Then
   Me.accountNo.Locked = True
 Else
   Me.accountNo.Locked = False
 End If
End Sub

Linq ;0)>

[ItsMe]

I believe the setting 0 they are referring to is when you have referential integrity enforced for the one to many relationship. This setting is found in the relationships window. I don't use the relationships window or the intrinsic referential integrity setting. I update one to many dynasets all of the time. I must be using setting 1.

[Access_Novice]

You're not making changes to the Field, you're simply entering data into the Textbox; the Field has only been edited when the Value changes in the Table, and Access is not allowing that.

Ok. This makes sense. So an Edit is defined as making and successfully saving a change to the underlying table. Thank you.

So if I can't edit the 'accountNo' field under the "parents section" of the form, I wonder why Access would even make it possible for me to make a change to this field at all?

[ssanfu]

So if I can't edit the 'accountNo' field under the "parents section" of the form, I wonder why Access would even make it possible for me to make a change to this field at all?

Again, as Linq said, you are not making a change to a field, you are changing text in a text box control. Forms have controls. Controls can be bound to a field in the form record source. The record source can be a table or a query (virtual table).


This is why I use autonumbers to form relationships. The autonumber has no real world meaning so there is no need to be able to edit it.
If the linking fields were autonumber/Long instead of text (accountNo), you would be able to change the account number if needed.

Also, although you can use a text field to set relationships, it is *much* slower (indexing) because the field can vary in length.
See "Use a Meaningless Primary Key" and "Only Use One Numeric Field as the Primary Key"
at http://www.fmsinc.com/free/newtips/primarykey.asp