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

tmp76

New member
Local time
Today, 13:59
Joined
Jun 30, 2021
Messages
7
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

I’m here to help
Staff member
Local time
Today, 13:59
Joined
Oct 29, 2018
Messages
16,203
Hi. Welcome to AWF!

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

theDBguy

I’m here to help
Staff member
Local time
Today, 13:59
Joined
Oct 29, 2018
Messages
16,203
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:59
Joined
May 7, 2009
Messages
13,775
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

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2002
Messages
32,890
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

New member
Local time
Today, 13:59
Joined
Jun 30, 2021
Messages
7
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

New member
Local time
Today, 13:59
Joined
Jun 30, 2021
Messages
7
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

New member
Local time
Today, 13:59
Joined
Jun 30, 2021
Messages
7
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’m here to help
Staff member
Local time
Today, 13:59
Joined
Oct 29, 2018
Messages
16,203
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

AWF VIP
Local time
Today, 21:59
Joined
Jul 26, 2013
Messages
8,760
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

New member
Local time
Today, 13:59
Joined
Jun 30, 2021
Messages
7
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

AWF VIP
Local time
Today, 21:59
Joined
Jul 26, 2013
Messages
8,760
Make sure your new form is set to allow edits, and the subforms as well.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Feb 19, 2002
Messages
32,890
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.
 

Users who are viewing this thread

Top Bottom