Filter On Empty Master not working when the Master / Child linking field is a Replication ID - connected to SQL Server backend

Question

When the linking field in the parent form is empty, the subform continues to display the previous subform record rather than showing a blank entry. This only happens when the linking field is a replication id (in Access) and a uniqueidentifier (in SQL Server). i.e. if the form uses a Long / Int combination the subform filtering works as expected.

Currently using:

Microsoft® Access® for Microsoft 365 MSO (Version 2406 Build 16.0.17726.20160) 64-bit

Microsoft SQL Server Developer (64-bit) Version 16.0.4125.3

Answer 1

Can you provide a reproducible scenario that demonstrates the problem? That would include the SQL Server driver you are using and forms where the problem occurs? Also scripts for the SQL Server table(s) involved? Thank you.

George

Answer 2

Thanks for the response Grover however, I don't think that reference is relevant. I think it was written in the context of working with Access as the backend database, simply because it would be an unnecessary waste of space to use replication id's if you didn't need to. It doesn't say a form wouldn't work if you use replication id's because it IS a valid scenario to use them when either:

1. you are using the replication features of Access, or
2. you are connecting to a SQL Service Backend database that uses GUIDs

I think this is a bug unless someone can explain otherwise

Answer 3

It might be a good idea NOT to use this replication ID this way. [Emphasis added to this description.]

Supported field properties

Property

Use

Field Size

Determines the amount of space that is allocated for each value. For AutoNumber fields, only two values are allowed:<br><ul style=""><li style="margin-left: 0"><p style="" data-prewrap="true">The Long Integer field size is used for AutoNumber fields that are not used as replication IDs. This is the default value. You should not change this value unless you are creating a replication ID field.</p><div style=""><p style="" data-prewrap="true"><b>Note: </b>Replication is not supported in databases that use a new file format, such as .accdb.</p></div><p style="" data-prewrap="true">This setting makes AutoNumber fields compatible with other Long Integer Number fields when they are used in relationships or joins. Each field value requires 4 bytes of storage.</p></li><li style="margin-left: 0"><p style="" data-prewrap="true">The <b>Replication ID</b> field size is used for AutoNumber fields that are used as replication IDs in a database replica. <strong><em>Do not use this value unless you are working in or implementing the design of a replicated database.</em></strong></p><p style="" data-prewrap="true">Each field value requires 16 bytes of storage.</p></li></ul>

Answer 4

Yes it's a GUID. In SQL Server that has a data type of uniqueidentifer and the equivalent data type in Access is a Number / Replication ID

Answer 5

Are you saying that the Primary Key field in the table is a ReplicationID?