In Access 97, I have a Payees table in a one-to-many relationship with a Trans table, linked to each other on a Social Security Number field (the link is Payees.SSN --> Trans.TransSSN). Both fields are Text, FieldSize 11, InputMask "000\-00\-0000;0;_". Payees.SSN is a key field, while Trans.TransSSN is not the key field but is indexed as "Yes (Duplicates OK)".

When I use DAO objects in VBA code to add a record to the Trans table, using an SSN value that already exists in the Payees table, everything works fine.

When I try to do the same thing through the user interface, I get an error message that says there is no matching record in the Payees table. This happens whether I copy and paste the SSN from the Payees table to the new reocrd in the Trans table, or enter the same number manually.

Does anyone know why this is happening and how I can prevent it? Thanks.

I would say that some of the SSN values include dashes and some do not. By using 0 as the second part of the input mask, you are telling Access to actually store the dash in the table.

But how could it be that some contain dashes and some do not. Since both fields are set to store the dashes, it would seem that ALL records in both tables would have dashes stored in these fields.

OTOH, is it possible that when a record is created via VBA code, one could make an assignment like this:

MySSN = "123456789"

and it would be stored without the dashes, despite the setting of the InputMask, while records created from the user interface would have the dashes? As I write, this is seeming more and more likely, since during the data conversion (where these records were created via VBA code), a references to these fields using the Mid$ function produced results that never included the dashes, even though they were supposedly present.