Can't enter anything into subform for new record (SQL Linked Tables)

[tmp76]

I have an access database that I recently converted over to use linked SQL Server tables.

I have a form that is used to create a new record that contains two sub-forms. This worked perfectly when using tables in Access but now I can not enter anything into the subforms. There is no error, I just can't click to enter a new record (and no '*' is visible signifying that you can enter a new record). One my other form for editing existing records, the subforms work fine.

I have searched around and tried a few things and verified the following:
--If I open the tables that the subform is using, I can enter records so it isn't permission related.
--SQL tables have auto-incrementing identity fields with primary key constraints added.
--The subform has allow additions set to true

I am guessing that is has to do with the record id not being entered in the SQL server table yet when adding a new record so the subform is unable to link to the main form, however I have not been able to find a resolution for this.

 

[theDBguy]

Hi. Welcome to AWF!

Are the subforms bound to a linked table or a view?

 

[tmp76]

Hi. Welcome to AWF!

Are the subforms bound to a linked table or a view?

Yes they are bound to a linked table.

 

[theDBguy]

Yes they are bound to a linked table.

Normally, subforms bound to a linked SQL Server table does not have any problems with adding new records. Have you tried creating a test mainform/subform based on the same tables just to see if it will act differently?

 

[arnelgp]

you need to Remove the PK from your identity column (only make it autoincrement) from the child table and only Retain the FK field.
Create the relationship in MSAccess.

 

[Pat Hartman]

Is the form bound?
Does the table for the subform have a primary key?
Do you have permission to add rows to that table?
Does the form allow additions?

 

[tmp76]

Is the form bound?
Does the table for the subform have a primary key?
Do you have permission to add rows to that table?
Does the form allow additions?

The form is bound.
Both the tables linked to the main form and subform have primary keys
Permissions on the table are good. When I am in my form I created to edit records, I can insert using that same subform when there is already an existing record.
All forms allow additions.

 

[tmp76]

you need to Remove the PK from your identity column (only make it autoincrement) from the child table and only Retain the FK field.
Create the relationship in MSAccess.

I did this and it didn't change anything.

 

[tmp76]

Normally, subforms bound to a linked SQL Server table does not have any problems with adding new records. Have you tried creating a test mainform/subform based on the same tables just to see if it will act differently?

I created a brand new form linked to the main table with a subform linked to the child table and it is having the same problems.

 

[theDBguy]

I created a brand new form linked to the main table with a subform linked to the child table and it is having the same problems.

Now, try the same experiment using a different table, maybe even a new FE.

 

[Minty]

If you have a form for editing records that works, and links the sub-forms correctly, it sounds as if you haven't set the subform up correctly on the New record form.

Why not simply have an "Add New" button on the editing form?

 

[tmp76]

Now, try the same experiment using a different table, maybe even a new FE.

There are two subforms I need on this one, I have tried it with both tables with no luck.

 

[tmp76]

If you have a form for editing records that works, and links the sub-forms correctly, it sounds as if you haven't set the subform up correctly on the New record form.

Why not simply have an "Add New" button on the editing form?

The edit form was simply a copy of the new form with the properties changed to not being data entry and allowing edits. The only difference being is that I added a second subform (which works also) and some filters to search. That is why I was initially thinking the problem is that their is not yet a primary key in SQL for the other forms to link to when entering the new one.

 

[Minty]

Make sure your new form is set to allow edits, and the subforms as well.

 

[Pat Hartman]

Also, do you have the master/child links set on the subforms? Access sets these for you automagically if you have created proper relationships using the Relationship window. If you skipped that step (poor practice), you're on your own.