Subform will not let me add records!

[sdp_tws]

Hi, I hope somebody can help dispell my frustration!

I am designing an employee's training database. I have a form called "UpdateTrainingRecords" which controls the employees table.

In it I have a subform which is in datasheet view. This contains fields emp_id, trainingitem_id, trainingitem_name and the data comes from a query which links two tables (trainingitems and trainingdetails - with only trainingitem_name coming from trainingitems).

The fields are linked my (child and master) emp_id.

The subform shows existing records (that were entered manually directly into the table) fine. But there is no option to add a record. I have tried everything but it will not let me.

Can anybody suggest anything?!

 

[HiTechCoach]

There are a couple of things to check:

1) Is the form's Allow Additions property set to Yes.

2) is the form's recordset updatable? Is the record source based on a single table?

 

[rainman89]

Can you update the tables from the query?

 

[sdp_tws]

1) Is the form's Allow Additions property set to Yes.

Yes it is.

2) is the form's recordset updatable? Is the record source based on a single table?

The master form is based on a single table, the subform is based on a query containing two tables.

Can you update the tables from the query?

Yes you can, and you can also add a record from the subform when you open that directly.

 

[sdp_tws]

Can anybody else help?

 

[WIS]

In it I have a subform which is in datasheet view. This contains fields emp_id, trainingitem_id, trainingitem_name and the data comes from a query which links two tables (trainingitems and trainingdetails - with only trainingitem_name coming from trainingitems).

The fields are linked my (child and master) emp_id. Can anybody else help?

You have a form and a subform. Here are some assumptions and questions:

The form "UpdateTrainingRecords" has the "employee" tbl as its recordsource. The tbl "employee" has the PK emp_id.

The subform "xxx" is set as a continuous form and has a qry generated from the 2 tbls "trainingitems" and "trainingdetails". How are these 2 tbls linked(joined) in the qry. Only one of the joined flds in this qry can be Indexed, Duplicates Allowed. Have you run this qry as a standalone and made sure that it is updateable. The qry must have FK emp_id in it. Which of the 2 tbls in the qry has emp_id as a FK.

 

[sdp_tws]

You have a form and a subform. Here are some assumptions and questions:

The form "UpdateTrainingRecords" has the "employee" tbl as its recordsource. The tbl "employee" has the PK emp_id.

Yes, both true.

The subform "xxx" is set as a continuous form and has a qry generated from the 2 tbls "trainingitems" and "trainingdetails". How are these 2 tbls linked(joined) in the qry.

It is actually set as datasheet view, although it could easily be set as continuous? Would this affect it?

The two tables are joined by field trainingitem_id (FK in "trainingdetails", and PK in "trainingitems").

Only one of the joined flds in this qry can be Indexed, Duplicates Allowed.

PK in trainingitems is indexed, but duplicates NOT allowed.

Have you run this qry as a standalone and made sure that it is updateable.

I have, and it is updatable as a query.

The qry must have FK emp_id in it. Which of the 2 tbls in the qry has emp_id as a FK.

FK emp_id is in the trainingdetails table.

Hope this clears it up? Can you help? Thanks.

 

[WIS]

FK emp_id is in the trainingdetails table.

Perhaps it should be in "trainingitems" as that is where the PK for the qry is.

 

[sdp_tws]

Perhaps it should be in "trainingitems" as that is where the PK for the qry is.

No, it can't be. Fields in my tables are:

trainingitems Table:

trainingitem_id (Primary Key)
trainingitem_description

(plus more, not relevent here)
​

trainingdetails table:

emp_id
trainingitem_id
trained_date

(plus more, not relevent here)
​

employees table:

emp_id (Primary Key)
emp_name

(plus more, not relevent here)
​

The "trainingitems" table is simply a list of items an employee could be trained in. The "employees" table is simply a list of employees. The "trainingdetails" table serves as a link between the two. It's needed because one employee could be training in more than one training item.

So therefore there is no emp_id field in the trainingitems table.

Any ideas what's wrong?

 

[sdp_tws]

Anybody? It's so frustrating!

 

[missinglinq]

Looking at your Main Form, is AllowEdits set to No, perhaps? This exact situation can be reproduced if this is true. The Subform is actually a Control on the Main Form, and if the Main Form's AllowEdits is set to No, then the Subform Control cannot be 'edited,' which is how Access looks at it when you try to add a new record to it.

 

[sdp_tws]

Looking at your Main Form, is AllowEdits set to No, perhaps? This exact situation can be reproduced if this is true. The Subform is actually a Control on the Main Form, and if the Main Form's AllowEdits is set to No, then the Subform Control cannot be 'edited,' which is how Access looks at it when you try to add a new record to it.

It is set to no. I've just changed it and it now works!!! At last, thanks all for your help, and thanks missinglinq for the solution.

I can just set each of the boxes to 'enabled=no' to avoid anybody editing data in the main form.

 

[missinglinq]

Glad we could help! And keep in mind that in Design View you can hold down the <Shift> key then select all the textboxes, then change the Enabled Property for all of them to No in one fell swoop!