Sub-Subform with identical fields not showing unique data.

[Degs29]

Let me explain. I'm trying to track worklog data, which resulted in a form-->subform-->sub-subform format data-entry system.

WorkLog [Main form] (View=Single):
-Client
-JobNbr [Primary Key]
-InvoiceDate
-OrderStatus

DateLog [Subform] (View=Single):
-JobNbr (Invisible; for linking to form)
-Date [Primary Key]

HourLog [Sub-Subform] (View=Continuous):
-Date (Invisible; for linking to subform)
-ID (autonumber) [Primary Key]
-Employee
-Type (Overtime or Regular)
-HoursLogged

There are many records in the sub-subform for each record of the subform, and likewise many records in the subform for each record in the main form.

What I'm running into is this: when two unique main form records happen to have the same date in their respective subform, the info in the sub-subform is used for both records, when I need them to be unique.

To make it more clear, I have an example. Below, you'll find the exact order of how I entered data into the form:

---

WorkLog:
(1st record)
Client: ABC
JobNbr: 123
InvoiceDate: <remains blank until invoiced>
Status: Incomplete

DateLog:
(1st record)


Date: Apr. 24/13

HourLog:
(1st record)
Employee: AA
Type: REG
Hours: 7

(2nd record)
Employee: BB
Type: REG
Hours: 3

DateLog:
(2nd record)
Date: Apr. 25/13

HourLog:
(1st record)
Employee: BB
Type: REG
Hours: 4

---

WorkLog:
(2nd record)
Client: ABC
JobNbr: 456 <-------------------------- Note: New job number
DateInvoiced: <blank>
Status: Incomplete

DateLog:
(1st record)
-Date: Apr 25/13

As soon as I type in Apr. 25/13, data from the same date, but from the previous job #123 fills in the HourLog. I need this data to be unique for each job #.

Thanks for any help!

[June7]

What causes the data to fill in?

Can you change the data?

Want to provide db for analysis? Follow instructions at bottom of my post.

[Dal Jeanis]

DateLog [Subform] (View=Single):
-JobNbr (Invisible; for linking to form)
-Date [Primary Key]

Something screams problem about this. Primary key is Date? Meaning you can only have one record in that table each day. (unless it's really a timestamp).

Suggestions -

First, change the name of DateLog.Date to DateLog.WorkDate. Avoid using "Date", it's a reserved word.

Second, create an autokey field on DateLog. For grins, we'll call it WorkDateID.

Third, Change HourLog.Date to HourLog.WorkDateID and adjust the foreign key reference.

Finally, fix the queries and the link fields in your forms.

[Missinglinq]

The Link Master Fields/Link Child Fields between DateLog and HourLog needs to be JobNbr and Date. In Properties - Data for the HourLog, if you click on the ellipsis (...) a dialog box will pop up letting you list multiple fields to link on.

BTW, Date is a Reserved Word, in Access, and should never be used as the name of a Field/Control; best change this to something else.

Linq ;0)>