Access Changes Primary Key to 0 when Foreign Table Record is added

[Yesurbius]

Access 2003 - latest patches. Windows XP

Problem:
Table 1 is linked to Table2 by two fields. A Query is made to include all records from Table1 and only those from Table2 where the values match.
When entering data into one of the Table2 fields that does not have a related record to Table1, the Table1.Employee ID is being changed to zero (as opposed to propagating the Table1 keys into Table2 for the current record)

If I go into Table1 Table, expand the subdatasheet, and add a record into Table2 - it works fine - the keys are correctly propegated.

Design:
I have a two local Tables. One table is a data dump from a query against a SQL Server. The second table records additional information about the first one. It records the primary key information, plus the auxillary information. The design is set up in such a way that Table1 can be pruned and purged and the Auxillary information remains intact. This way we are not storing huge amounts of data in the local access file - only the linking information and the Auxillary data.

Sample DB Attached, or look below:

Implementation:

Table 1: EmployeeHours
EmployeeID - Long Integer
WorkDate - Date
HoursWorked - Single

Table 2: AuxInfo
EmployeeID - Long Integer
WorkDate - Date
Approved - Yes/No
Comments - Text(50)

Relationship:
(
Table1.Employee ID=Table2.Emplo yeeID(+)
AND
Table1.WorkDate =Table2.WorkDat e(+)
)



I must be doing something wrong.

[NeoPa]

To be honest I don't know for sure as I don't use this much.

Having said that, if I found myself in this position I would create something similar using the wizard and compare the options set. In a query (properly built as updatable etc) Access will populate the linked fields as soon as you add data into the Null side of the join. I certainly recommend testing this works with your query first before using the wizard to build the form.

[NeoPa]

@NeoPa
Wow! That was obscure (and a little counter intuitive if I'm honest).

You need to clear the Default value of 0 in your definition of AuditNotes.Empl oyeeID. With that gone I found it worked, in the query at least. I had to look around quite a bit first before I sussed what was going on mind you.

Let us know how you get on.

[Yesurbius]

Thanks for the reply!

That _was_ the issue. I am not suprised it was something so basic - its usually those that are the hardest to find.

Normally I use AutoNumber fields - so I can see why this is suddenly popping up - I'm not using AutoNumber fields. When I created the test database - I did so from scratch .. so it must be a default value that access adds in there.

And it is very strange that it works when entered in a SubDataSheet via the main table.

Thanks again.

Thx again - will try it and let you know if that was the issue.

[NeoPa]

No worries. Glad to be able to help :)