R
Roland Alden
I have a fairly complex case and have run into a subtle problem I hope is
easy to fix
The data/record structure behind the forms is as follows:
Tables are person, dog and person-dog. All are PK'ed by a uniqueidentifier.
Records in person-dog establish linkages between persons and dogs. In
principle persons and dogs can be created independently and records in the
person-dog table may or may not exist to tie any particular pair together.
In order to insert a new record into person-dog the record has to hold valid
uid's to one person and one dog but I do not think that pertains to my
problem here. The flip is not true; a dog can exist without a relationship
to a person and a person can exist without a dog.
As for forms I have the following. There is a top level form for dog and
within that is a subform for person-dog that enumerates all the persons
associated in some way with this dog. Within that subform is yet another
subform for a single person from that subset. This all works fine for
existing records with linkages in them. You move to dog X and see their
people just fine and I can update data within the forms.
However, creating new records is something of a challenge. This "UI"
framework is what the customer wants to see but exactly what happens when
"new record" fires is another matter. At the top level I don't think I have
a problem. I can create a new dog and it will, in an inital state have no
matching person-dog records and that is that. And I can create new
person-dog records within a new (or old) dog record with no problem. AS LONG
AS THE PERSON ALREADY EXISTS.
What I am struggling with is the creation of a new person. It is almost as
simple as this: 1) inside the person subform hit the new record button. This
gives me a nice blank form to work with but when I go to save it I get a
duplicate PK error on the insert. If I show the uid (PK) field on the form I
can see that my "new" "blank" record is blank in all respects but the uid
field which is the same as the previous record. This no doubt is because the
parent/child subform relationship is over this uid and when it comes time to
write a new record Access is trying to be helpful and is controlling what is
being written in this field so it matches the outer parent form. However, in
this case I would like to override that behavior.
I realize if I do the following will happen. I'm sitting in an outer "dog"
record and create a new person record which is (not yet) tied to this dog
through a person-dog record. After I create my new person and hit save the
record will disappear because the person is not yet tied to the dog. I will
have to use a control on the dog form to create a person-dog record that
ties the new person to the dog (through a new person-dog record) and no
doubt do a few "refresh" operations in the right places.
I've looked at testing Me.NewRecord in Current and also a BeforeInsert
handler that would try to hammer me.uid. However, I have three
problems/questions.
1) There is the field in the underlying data record (named uid). It is this
I really care about. But, within a forms handler I'm not sure how to access
it.
2) If I bind it to a control (perhaps hidden) I can access it. It would be
nice to be able to refer to the data field without the ritual of creating an
invisible control.
3) If I access it through a control and try to set it to Null in hopes that
Access will let the underlying SQL Server generate a new default uid/pk when
the insert takes place I get an error from Access saying I can't assign Null
because the field is not a variant record type.
Of course I don't really want the thing to be "Null" anyway; I do want a new
uid to be allocated upon the insert of the new record and I don't want the
uid from the current outer parent form to be "forced upon" this new record.
Help!
easy to fix
The data/record structure behind the forms is as follows:
Tables are person, dog and person-dog. All are PK'ed by a uniqueidentifier.
Records in person-dog establish linkages between persons and dogs. In
principle persons and dogs can be created independently and records in the
person-dog table may or may not exist to tie any particular pair together.
In order to insert a new record into person-dog the record has to hold valid
uid's to one person and one dog but I do not think that pertains to my
problem here. The flip is not true; a dog can exist without a relationship
to a person and a person can exist without a dog.
As for forms I have the following. There is a top level form for dog and
within that is a subform for person-dog that enumerates all the persons
associated in some way with this dog. Within that subform is yet another
subform for a single person from that subset. This all works fine for
existing records with linkages in them. You move to dog X and see their
people just fine and I can update data within the forms.
However, creating new records is something of a challenge. This "UI"
framework is what the customer wants to see but exactly what happens when
"new record" fires is another matter. At the top level I don't think I have
a problem. I can create a new dog and it will, in an inital state have no
matching person-dog records and that is that. And I can create new
person-dog records within a new (or old) dog record with no problem. AS LONG
AS THE PERSON ALREADY EXISTS.
What I am struggling with is the creation of a new person. It is almost as
simple as this: 1) inside the person subform hit the new record button. This
gives me a nice blank form to work with but when I go to save it I get a
duplicate PK error on the insert. If I show the uid (PK) field on the form I
can see that my "new" "blank" record is blank in all respects but the uid
field which is the same as the previous record. This no doubt is because the
parent/child subform relationship is over this uid and when it comes time to
write a new record Access is trying to be helpful and is controlling what is
being written in this field so it matches the outer parent form. However, in
this case I would like to override that behavior.
I realize if I do the following will happen. I'm sitting in an outer "dog"
record and create a new person record which is (not yet) tied to this dog
through a person-dog record. After I create my new person and hit save the
record will disappear because the person is not yet tied to the dog. I will
have to use a control on the dog form to create a person-dog record that
ties the new person to the dog (through a new person-dog record) and no
doubt do a few "refresh" operations in the right places.
I've looked at testing Me.NewRecord in Current and also a BeforeInsert
handler that would try to hammer me.uid. However, I have three
problems/questions.
1) There is the field in the underlying data record (named uid). It is this
I really care about. But, within a forms handler I'm not sure how to access
it.
2) If I bind it to a control (perhaps hidden) I can access it. It would be
nice to be able to refer to the data field without the ritual of creating an
invisible control.
3) If I access it through a control and try to set it to Null in hopes that
Access will let the underlying SQL Server generate a new default uid/pk when
the insert takes place I get an error from Access saying I can't assign Null
because the field is not a variant record type.
Of course I don't really want the thing to be "Null" anyway; I do want a new
uid to be allocated upon the insert of the new record and I don't want the
uid from the current outer parent form to be "forced upon" this new record.
Help!