Table structure?

[martinhough]

I am trying to use a form which has a tab control on it. There will be 5 or 6 tabbed pages. On each page there will be a subform with 3 columns ('date from', 'date to' and a calculated field of 'days total') as well as other controls on the actual page.

This db currently has three tables. One with the holiday entitlement, staff name (ref to) and total days taken. Another is a table with staff details in (names, pics etc) these two link fine - I have their primary key linked of each one. The other table, 'records' which holds all the instances of dates taken as holidays and is linked from the primary key of the entitlements table to a number field (not primary key) in this table. If I expand the staff table datasheet, I can see all records - despite being set to 'only show records where both fields from each table are equal'. The subdatasheet from the entitlements to the instances (records) is correctly showing the relative data for each staff member - one problem?

Within my subform the query has just the 'records' and has a criteria set against the staff ref to only show that member of staff's dates taken etc. This also works fine, or at least displays okay. The parent and child aspect of this subform is blank.

When I try to add a new record, whilst it allows me to do so, it doesn't know which staff member to apply it to so effectively it disappears from view (as it's being filtered through the query). How do I get the appropriate non primary key field, which is linked to the staff primary key via the primary key of the entitlements PK? The data is there but has no staff id attributed to it. Is this a table structure/relationship issue or something else?

Thanks

 

[Galaxiom]

Use the Link Fields instead of the criteria. The Link fields automatically enter the link field value in the subform.

BTW Recording DaysTaken breaches normalization. They should be calculated from the instance dates.

You should also avoid recording Entitlement but rather calculate it based on the current date and commencment date of the employee.

 

[martinhough]

Thanks Galaxiom - I had tried using the link master and child fields but ended up getting an error message to the effect of "The object doesn't contain the automation object 'Record'". Not sure why or what this is.

Okay, so if I set these fields (Master to the staff primary and child to 'Records' reference?) what 'criteria' is it I need not use that you refer?

Thanks for the tip with days taken - yes, had crossed my mind and you've confirmed my doubt, thanks.

'Entitlement' I shall look at seeing how to incorporate that as you have suggested, I think I get the gist.

Thanks again.

 

[Galaxiom]

Do you have a field or table named Record or Records? These would be reserved words.

Some reserved words will be tolerated if placed in square brackets but are best avoided.

 

[martinhough]

Ah, that'll be it Galaxiom! thank you!

 

[martinhough]

Just changed the names of anything called 'record' - still get the error with the newly named object. Is it something to do with the 'Tabbed Pages' element? I can normally run a subform without hitch on a form, this is the first time I've tried it this way.