0

I have a number of tables in an MS Access Db that have to use Replication IDs (GUID) as the primary key (and therefore also as foreign key contraints in the relationships between them). The data for these tables comes from an external application and I can't use any other field as a primary as they aren't unique. (I've also chosen, not to use local integer keys, "in loco parentis" for the GUID).

The database worked fine and all the relationships worked as expected and I am able to present the related records in a hierarchical MS Access form (one-to-many-to-many). The problem occurs when I try to present a count as part of the relationhips.

If I have GUIDInParent and GUID in Child as two fields, I can get the children by creating a form with Source Object = ParentToChildRelationship and Link Master Field = GUIDInParent and Link Child Field = GUIDInChild.

However, if I want indicate how many Chidren I'm going to display (since they may be hidden under a scrolled section), I use a separate form field populated via a DCount("1", "ParentToChildRelationship", "[GUIDinChild] = '" & [GUIDInParent] & '")

For the GUID field - this doesn't work... The reason appears to be that while on the form, the GUID (Replication ID) displays as a "GUID" - of the form "{HHHHHHH-HHHH-HHHH-HHHH-HHHHHHHHHHHH}" when assigned to another field it (literally) displays as "Chinese". Even assigning via the GUIDAsString function doesn't change this.

What's going on an is there any way I can use these GUIDs as I intend?

I have "jury rigged" a solution by having two columns in the tables (GUID - as number and GUIDString - as string) both set tot he same value and using the rendering that works in each case... The DCount("1", "ParentToChildRelationship", "[GUIDinChild] = '" & [GUIDInParent] & '") thus becomes: DCount("1", "ParentToChildRelationship", "[GUIDinChild] = '" & [GUIDInParentString] & '") and thus works...

TIA, Paolo

Dai
  • 141,631
  • 28
  • 261
  • 374
PaoloFCantoni
  • 967
  • 2
  • 12
  • 26

1 Answers1

0

Have you considered a control in the subform footer to count the records? This can be referenced in the main form by name. Alternatively, you can refer to MySubformControl.Form.Recordset.Recordcount

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Hi Remou, Is that the subform footer? I tried the .Form.Recordset.Recordcount but that caused a Name error. But the REAL issue is the GUID usage - not the count. Why do GUIDs behave this way? – PaoloFCantoni Jun 05 '12 at 00:50
  • Got the count going using the subform properties - so thanks for that Remou. But hte substantive question still holds... Paolo – PaoloFCantoni Jun 05 '12 at 01:08
  • The name error may be due to using the name of the form contained in the subform control, rather than the name of the subform control. Subforms are also forms in their own right, as it were. – Fionnuala Jun 05 '12 at 08:25
  • GUID can be a problem in MS Access : http://stackoverflow.com/questions/393381/recommendations-on-using-sql-server-guid-from-ms-access – Fionnuala Jun 05 '12 at 08:57