MSAccess subforms and Guids - linkage not being updated when on 'new record'

[astro]

I've done a little experimenting and have spotted some behavior I don't
understand in Access subform linkage when the PK's are Guid's (which
happends to be the case for me with a replication project I'm involved in)
and the backend is SQL-Server.

I've setup a parent-child table..each with PK's which are Guids - with
'rowGuid' = yes and 'default value' = "(newid())" for their respective PK's.

I've setup 2 forms with each doing a "select *" query on their respective
table.

I've added the table2/subform to the table1 form and linked them on the
table1 PK...

I've opened the form1 - which is on record 1 of the parent table. The
subform correctly show the child record which has a child non-PK field value
of "nnnn".

I now move the parent to the 'new record' position to add new parent value.
The subform lists a child record with a non-PK field value of "nnnn" and a
PK1 value identical to the 1st record...

Basically the subform is not being updated to blank when I move the parent
to the new record marker...whether any call to the internal update linkage
event is being done at all I'm not certain of....

I've done the same thing with an Access backend and an SQL-Server backend
without Guilds and the behavior is consistent -the child form is not
pre-populated with the previous records values..

I had no form-code in any of these 3 tests......

Has anyone else experienced this behavior?

 

[Paul Overway]

I'd be curious as to whether converting the Guid to a string (in both the
parent and child recordsource) and then using THAT for linking would resolve
the links properly. Of course, this isn't a complete solution (if it even
works) because you'd have to programmatically assign the parent Guid to the
child field from within your subform when adding records. So, in the
BeforeUpdate event on the subform, you'd need Me.LocationID =
Me.Parent.LocationID.

I can confirm that guids are a pain to work with as PKs. If possible, you
might want to create a non-guid field as a secondary PK. Although, in a
replication scenario that is complicated too.

 

[astro]

I might en up resorting to this..but performance would suffer since it would
negate the use of indexes I think...

 

[Paul Overway]

Yes....which is another reason to consider using a non-guid as a secondary
unique index. You might use a random long. Are you using Access
replication or SQL Server replication?

 

[astro]

work-around for this is to have the subform masterlink property point to a
control on the form which is bound to the Guid PK.

This seems to correct the problem.

 

[Paul Overway]

Glad you figured it out. I'll tuck the work around away for future
reference.

 

[astro]

))

Glad you figured it out. I'll tuck the work around away for future
reference.