Using replicationid as autonumber works but not in subforms - why? (1 Viewer)

Usually I would be answering this question, but this has me stumped. Just want to see if anyone has some suggestions.

For a client, I have a need to be able to export selected data (based on user) from a master db backend to a local db for the user to take somewhere without access to the master db, make changes and additions and update the master db on their return. Basically a simple form of replication – but syncing the data only. I can see other uses around performance, report distribution, etc as well so set about developing a ‘universal’ process I could use in other applications and using replicationid as the autonumber to avoid potential clashes.

Everything works extremely well so far as the syncing is concerned (at least in a test environment, haven’t reached user testing yet). The problem is with presenting the data on a form. Typically with these things, the issue doesn't present itself until towards the end of the development

In the attached database, Customers have contacts who in turn have notes. To demonstrate the issue, the form which opens (probfmCustomers) has a mainform with customers and a subform with contacts plus a second subform on the mainform for notes which has the linkmaster property set to current record in the contacts subform via a control on the main form. All a standard setup.

The problem is that the notes subform won’t populate with existing data – just as if dataentry was set to yes. I’ve included a third subform based directly on the contacts table – and this will display related records if you click the + to prove the links work.

I’ve also included the same data but with autonumber as a long. Open the form frmCustomers or change the recordsources of the three forms and it works as expected.

If I use a nested subform it works OK – see the altfmCustomers form

So I appear to have two workarounds – one is to use nested subforms, although this does have issues around form layout. The other is to use a random long autonumber with a potential duplication risk when syncing.

So why won't it display as required when using a replicationID? I would rather stick with my original vision if someone can point out what I am missing – any suggestions?

made some progress of sorts, but now have the opposite - now all notes appear regardless of the contact!

changed the linkcontrol controlsource from

=[ContactsSF].[Form].[contactPK]

to

="{guid " & Nz([ContactsSF].[Form].[contactPK],"{000A0000-A0A0-0A0A-AA00-A00A0A000AA0}") & "}"

nz for new records

In addition, when adding new notes, I get the error message

The object doesn't contain the automation object 'contactPK'

-contactPK is the name of the link control

Just discovered the GUIDfromstring function - so will investigate furter

Found an acceptable solution

1. remove values from linkchild/master properties of notesSF
2. remove link field in main table
3. in contacts subform put the following in the form current event


it is a technique I have used in the past to minimise network traffic on large datasets but didn't think I needed it here since the backend will always be local for this application and relatively small.

I had tried it without the stringfromGUID function, but hadn't worked so had abandoned that line of investigation.

As to my original question - why? it is because a GUID is actually an array of bytes and access controls cannot display these as such, so displays as text. It was trying to compare an array of bytes with a different array of bytes in a string - both of which when interpreted as text, look the same.