Field on subform cannot be updated

[Beemerang]

Hi All

I am back to using Access after many years and I am shocked at how much I have forgotten! I have searched both this forum and the web but none of the proposed solutions that I have seen have fixed my problem so I'm going to rely on the gurus here!

I have created tables to capture information from a form (see attachment for relationship diagram).

I have created a form with a subform based directly on the tables. Entering data in the main form works fine but when I try to add a record on the subform I receive the error: "Field cannot be updated". Clearly I have made a mistake creating the table relationships but I cannot for the life of me figure out where I went wrong.

Any help would be appreciated as I am under massive deadline pressure.

Thanks!
Beem

 

[CJ_London]

How are the recordsources for your main form and subform constructed?

And can you clarify the nature of the relationships? At the moment it looks like you can have a detail record assigned to more than one MonthH table via the activity table - is this the intention?

 

[Beemerang]

How are the recordsources for your main form and subform constructed?

And can you clarify the nature of the relationships? At the moment it looks like you can have a detail record assigned to more than one MonthH table via the activity table - is this the intention?

Hi CJ

Thank you so much for taking the time to respond!

I managed to get it sorted in a rather inelegant way I think (See attached screenshot)

To answer your question, yes, the idea was that one month table record could be linked to several activity table records (One employee could have several different instances of activities in the same month to report on).

I had thought that I would need a junction table to link the month table with the activities table but instead I added the primary key from the month table as a lookup field in the activities table which seems to get filled correctly from the main and subform data entry as I create a new record in the subform. I hope this makes sense, I'm not explaining very clearly, sorry.

Thanks again for the reply!

Beem

 

[CJ_London]

Depending on your answer, that was what I was going to suggest - however I don't think you have it right yet - as far as I can see you don't need the activity table.

Also, I suspect your structure is not yet normalised- for example, you probably need a table of personnel and change the name fields in the MonthH table to family keys to this table.

You also may need to consider what happens if a facilitator has a different supervisor

 

[Beemerang]

Thanks CJ

I agree with you on not needing the activity junction table.

I do have a different table with personnel from which I look up the employee and supervisor name.

How would you suggest a handle a scenario in which the employee's supervisor changes?

 

[CJ_London]

Probably just have the one table (Detail) with the addition of facilitatorID and Activity Date fields (and lose the monthlyreportid field)

Then in another table - we'll call it tblOrg have the following

tblOrg
ID autonumber PK
FacilitatorID Long FK - link to employee table
SupervisorID Long FK - link to employee table
DateFrom Date - date facilitator started working for supervisor e.g

ID  Facilitator Supervisor DateFrom (UK dates)
1   1             2            01/01/2013
2   1             3            05/06/2013
3   1             2            10/11/2013

so reports dated between 01/01/2013 and 04/06/2013 would have supervisor 2.

You can use the year and month functions to determine year and month

 

[Beemerang]

Excellent idea, thanks mate!

Again, I appreciate the time you took to help.

 

[CJ_London]

No problem,

Note that you will probably need to have a DateLeft field in your employee table so you can 'stop' the employee - otherwise whoever is the last supervisor would remain so for all time!