I have a form that is just for users to go to add new records. But now it
opens blank and it is not possible to insert any new records. I confirmed
the permissions for the query and form to allow insertions and edits. I
verified the Data properties and here is what is selected.

RecordSource: a select query with specific fields from one table (no
calculations)
Recordset Type: Dynaset
Filter on Load: no
Order by on Load: No
Data Entry: yes
Allow Additions: Yes
Allow Deletions: Yes
Allow Edits: yes
Allow Filters: Yes

But I am wondering if the problem is with my query. When I run the query, I
can edit the data, but I am not able to add any new records. As I said, it
is a simple select query, with about 20 fields, all taken directly from one
table. There are no calculations or function run on any fields, just a
criteria filter.

Can you tell me what I am missing? What would prevent me from adding
records to a query and/or form?

Thanks! Myrinda

Myrinda,
That's the key to the problem... the query itself will not allow
Additions.
So therefore... neither will the form.
So also... those properties you indicated aren't involved in this case.
It's in the query...

When you say it's a query against just one table...
Is this a View/Totals query? (shouldn't be)
Is it a Select query? (it should be)

Otherwise... we need to see the SQL statement for the query.
Cut & paste exactly what you have...
(View/View SQL)
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"Myrinda" <(E-Mail Removed)> wrote in message
news:7377A6EE-A5D2-4857-8DBD-(E-Mail Removed)...
>I have a form that is just for users to go to add new records. But now it
> opens blank and it is not possible to insert any new records. I confirmed
> the permissions for the query and form to allow insertions and edits. I
> verified the Data properties and here is what is selected.
>
> RecordSource: a select query with specific fields from one table (no
> calculations)
> Recordset Type: Dynaset
> Filter on Load: no
> Order by on Load: No
> Data Entry: yes
> Allow Additions: Yes
> Allow Deletions: Yes
> Allow Edits: yes
> Allow Filters: Yes
>
> But I am wondering if the problem is with my query. When I run the query,
> I
> can edit the data, but I am not able to add any new records. As I said,
> it
> is a simple select query, with about 20 fields, all taken directly from
> one
> table. There are no calculations or function run on any fields, just a
> criteria filter.
>
> Can you tell me what I am missing? What would prevent me from adding
> records to a query and/or form?
>
> Thanks! Myrinda

If you can't add records to your query, you won't be able to add them to your
form. You probably need to change the design of your table, set the primary
key or change to an ID number or autonumber field. Then run the query again
and see if you can add records. Some queries, like union queries, are never
updateable. In some cases I have used a make-table query to make a table from
an uneditable query and that allowed edits.
--
Milton Purdy
ACCESS
State of Arkansas


"Myrinda" wrote:

> I have a form that is just for users to go to add new records. But now it
> opens blank and it is not possible to insert any new records. I confirmed
> the permissions for the query and form to allow insertions and edits. I
> verified the Data properties and here is what is selected.
>
> RecordSource: a select query with specific fields from one table (no
> calculations)
> Recordset Type: Dynaset
> Filter on Load: no
> Order by on Load: No
> Data Entry: yes
> Allow Additions: Yes
> Allow Deletions: Yes
> Allow Edits: yes
> Allow Filters: Yes
>
> But I am wondering if the problem is with my query. When I run the query, I
> can edit the data, but I am not able to add any new records. As I said, it
> is a simple select query, with about 20 fields, all taken directly from one
> table. There are no calculations or function run on any fields, just a
> criteria filter.
>
> Can you tell me what I am missing? What would prevent me from adding
> records to a query and/or form?
>
> Thanks! Myrinda

Thanks very much for your response!
Here is my SQL statement for my query. (I inherited this db; I would not
have picked such a horrendously long name for my table.)

SELECT SoutheastTeamDrillingScheduleTable.Well_Name,
SoutheastTeamDrillingScheduleTable.Rig,
SoutheastTeamDrillingScheduleTable.Team,
SoutheastTeamDrillingScheduleTable.Status,
SoutheastTeamDrillingScheduleTable.Operator,
SoutheastTeamDrillingScheduleTable.Well_No_Sub,
SoutheastTeamDrillingScheduleTable.MOB_Days,
SoutheastTeamDrillingScheduleTable.Estimated_Days_Drilling,
SoutheastTeamDrillingScheduleTable.Landman,
SoutheastTeamDrillingScheduleTable.Engineer,
SoutheastTeamDrillingScheduleTable.Geologist,
SoutheastTeamDrillingScheduleTable.Geo_Tech,
SoutheastTeamDrillingScheduleTable.Land_Tech,
SoutheastTeamDrillingScheduleTable.Eng_Tech,
SoutheastTeamDrillingScheduleTable.Fiscal_Year,
SoutheastTeamDrillingScheduleTable.Surf_Latitude,
SoutheastTeamDrillingScheduleTable.Surf_Longitude,
SoutheastTeamDrillingScheduleTable.S_T_R,
SoutheastTeamDrillingScheduleTable.Bot_Latitude,
SoutheastTeamDrillingScheduleTable.Bot_Longitude,
SoutheastTeamDrillingScheduleTable.Field,
SoutheastTeamDrillingScheduleTable.Parish_or_County,
SoutheastTeamDrillingScheduleTable.State,
SoutheastTeamDrillingScheduleTable.Location,
SoutheastTeamDrillingScheduleTable.Bottom_hole_location,
SoutheastTeamDrillingScheduleTable.Legal_Description,
SoutheastTeamDrillingScheduleTable.Proposed_Total_Depth,
SoutheastTeamDrillingScheduleTable.Prop_TVD,
SoutheastTeamDrillingScheduleTable.Primary_Objective,
SoutheastTeamDrillingScheduleTable.Secondary_Objective,
SoutheastTeamDrillingScheduleTable.BCP_GWI,
SoutheastTeamDrillingScheduleTable.ACP_BPO_NRI,
SoutheastTeamDrillingScheduleTable.Scoping_Estimate_PreAFE_DHC,
SoutheastTeamDrillingScheduleTable.Scoping_Estimate_PreAFE_Comp_Cost,
SoutheastTeamDrillingScheduleTable.Earliest_Lease_Expiration,
SoutheastTeamDrillingScheduleTable.Lease_Exp_2,
SoutheastTeamDrillingScheduleTable.Acres_Exp1,
SoutheastTeamDrillingScheduleTable.Acres_Exp2,
SoutheastTeamDrillingScheduleTable.Well_Type,
SoutheastTeamDrillingScheduleTable.Type_of_Location
FROM SoutheastTeamDrillingScheduleTable
WHERE (((SoutheastTeamDrillingScheduleTable.Team)="Haynesville"));

The table feeding the query is a linked SQL database. I do have other
forms/queries from the same table that allow insertion and editing. I just
can't figure out why this one won't. I've recreated the query just to be
sure I have all the correct fields (and because it used to work!) but I'm not
sure what the problem is now. What do you think?

Myrinda

"Al Campagna" wrote:

> Myrinda,
> That's the key to the problem... the query itself will not allow
> Additions.
> So therefore... neither will the form.
> So also... those properties you indicated aren't involved in this case.
> It's in the query...
>
> When you say it's a query against just one table...
> Is this a View/Totals query? (shouldn't be)
> Is it a Select query? (it should be)
>
> Otherwise... we need to see the SQL statement for the query.
> Cut & paste exactly what you have...
> (View/View SQL)
> --
> hth
> Al Campagna
> Microsoft Access MVP 2007-2009
> http://home.comcast.net/~cccsolutions/index.html
>
> "Find a job that you love... and you'll never work a day in your life."
>
>
> "Myrinda" <(E-Mail Removed)> wrote in message
> news:7377A6EE-A5D2-4857-8DBD-(E-Mail Removed)...
> >I have a form that is just for users to go to add new records. But now it
> > opens blank and it is not possible to insert any new records. I confirmed
> > the permissions for the query and form to allow insertions and edits. I
> > verified the Data properties and here is what is selected.
> >
> > RecordSource: a select query with specific fields from one table (no
> > calculations)
> > Recordset Type: Dynaset
> > Filter on Load: no
> > Order by on Load: No
> > Data Entry: yes
> > Allow Additions: Yes
> > Allow Deletions: Yes
> > Allow Edits: yes
> > Allow Filters: Yes
> >
> > But I am wondering if the problem is with my query. When I run the query,
> > I
> > can edit the data, but I am not able to add any new records. As I said,
> > it
> > is a simple select query, with about 20 fields, all taken directly from
> > one
> > table. There are no calculations or function run on any fields, just a
> > criteria filter.
> >
> > Can you tell me what I am missing? What would prevent me from adding
> > records to a query and/or form?
> >
> > Thanks! Myrinda
>
>
> .
>

Turn it into a make table query, make the table, change your form
recordsource to the table and see if it is editable.
--
Milton Purdy
ACCESS
State of Arkansas


"Myrinda" wrote:

> Thanks very much for your response!
> Here is my SQL statement for my query. (I inherited this db; I would not
> have picked such a horrendously long name for my table.)
>
> SELECT SoutheastTeamDrillingScheduleTable.Well_Name,
> SoutheastTeamDrillingScheduleTable.Rig,
> SoutheastTeamDrillingScheduleTable.Team,
> SoutheastTeamDrillingScheduleTable.Status,
> SoutheastTeamDrillingScheduleTable.Operator,
> SoutheastTeamDrillingScheduleTable.Well_No_Sub,
> SoutheastTeamDrillingScheduleTable.MOB_Days,
> SoutheastTeamDrillingScheduleTable.Estimated_Days_Drilling,
> SoutheastTeamDrillingScheduleTable.Landman,
> SoutheastTeamDrillingScheduleTable.Engineer,
> SoutheastTeamDrillingScheduleTable.Geologist,
> SoutheastTeamDrillingScheduleTable.Geo_Tech,
> SoutheastTeamDrillingScheduleTable.Land_Tech,
> SoutheastTeamDrillingScheduleTable.Eng_Tech,
> SoutheastTeamDrillingScheduleTable.Fiscal_Year,
> SoutheastTeamDrillingScheduleTable.Surf_Latitude,
> SoutheastTeamDrillingScheduleTable.Surf_Longitude,
> SoutheastTeamDrillingScheduleTable.S_T_R,
> SoutheastTeamDrillingScheduleTable.Bot_Latitude,
> SoutheastTeamDrillingScheduleTable.Bot_Longitude,
> SoutheastTeamDrillingScheduleTable.Field,
> SoutheastTeamDrillingScheduleTable.Parish_or_County,
> SoutheastTeamDrillingScheduleTable.State,
> SoutheastTeamDrillingScheduleTable.Location,
> SoutheastTeamDrillingScheduleTable.Bottom_hole_location,
> SoutheastTeamDrillingScheduleTable.Legal_Description,
> SoutheastTeamDrillingScheduleTable.Proposed_Total_Depth,
> SoutheastTeamDrillingScheduleTable.Prop_TVD,
> SoutheastTeamDrillingScheduleTable.Primary_Objective,
> SoutheastTeamDrillingScheduleTable.Secondary_Objective,
> SoutheastTeamDrillingScheduleTable.BCP_GWI,
> SoutheastTeamDrillingScheduleTable.ACP_BPO_NRI,
> SoutheastTeamDrillingScheduleTable.Scoping_Estimate_PreAFE_DHC,
> SoutheastTeamDrillingScheduleTable.Scoping_Estimate_PreAFE_Comp_Cost,
> SoutheastTeamDrillingScheduleTable.Earliest_Lease_Expiration,
> SoutheastTeamDrillingScheduleTable.Lease_Exp_2,
> SoutheastTeamDrillingScheduleTable.Acres_Exp1,
> SoutheastTeamDrillingScheduleTable.Acres_Exp2,
> SoutheastTeamDrillingScheduleTable.Well_Type,
> SoutheastTeamDrillingScheduleTable.Type_of_Location
> FROM SoutheastTeamDrillingScheduleTable
> WHERE (((SoutheastTeamDrillingScheduleTable.Team)="Haynesville"));
>
> The table feeding the query is a linked SQL database. I do have other
> forms/queries from the same table that allow insertion and editing. I just
> can't figure out why this one won't. I've recreated the query just to be
> sure I have all the correct fields (and because it used to work!) but I'm not
> sure what the problem is now. What do you think?
>
> Myrinda
>
> "Al Campagna" wrote:
>
> > Myrinda,
> > That's the key to the problem... the query itself will not allow
> > Additions.
> > So therefore... neither will the form.
> > So also... those properties you indicated aren't involved in this case.
> > It's in the query...
> >
> > When you say it's a query against just one table...
> > Is this a View/Totals query? (shouldn't be)
> > Is it a Select query? (it should be)
> >
> > Otherwise... we need to see the SQL statement for the query.
> > Cut & paste exactly what you have...
> > (View/View SQL)
> > --
> > hth
> > Al Campagna
> > Microsoft Access MVP 2007-2009
> > http://home.comcast.net/~cccsolutions/index.html
> >
> > "Find a job that you love... and you'll never work a day in your life."
> >
> >
> > "Myrinda" <(E-Mail Removed)> wrote in message
> > news:7377A6EE-A5D2-4857-8DBD-(E-Mail Removed)...
> > >I have a form that is just for users to go to add new records. But now it
> > > opens blank and it is not possible to insert any new records. I confirmed
> > > the permissions for the query and form to allow insertions and edits. I
> > > verified the Data properties and here is what is selected.
> > >
> > > RecordSource: a select query with specific fields from one table (no
> > > calculations)
> > > Recordset Type: Dynaset
> > > Filter on Load: no
> > > Order by on Load: No
> > > Data Entry: yes
> > > Allow Additions: Yes
> > > Allow Deletions: Yes
> > > Allow Edits: yes
> > > Allow Filters: Yes
> > >
> > > But I am wondering if the problem is with my query. When I run the query,
> > > I
> > > can edit the data, but I am not able to add any new records. As I said,
> > > it
> > > is a simple select query, with about 20 fields, all taken directly from
> > > one
> > > table. There are no calculations or function run on any fields, just a
> > > criteria filter.
> > >
> > > Can you tell me what I am missing? What would prevent me from adding
> > > records to a query and/or form?
> > >
> > > Thanks! Myrinda
> >
> >
> > .
> >

On Tue, 13 Apr 2010 08:27:02 -0700, Myrinda
<(E-Mail Removed)> wrote:

>The table feeding the query is a linked SQL database. I do have other
>forms/queries from the same table that allow insertion and editing. I just
>can't figure out why this one won't.

I've seen this problem in two circumstances:

1. The SQL/Server table does not have a Primary Key, or Access doesn't know
what the Primary Key is.
2. There's a trigger or constraint on the SQL table which prevents data being
added.

Can you add records if you open the table in table datasheet view? Can you use
SQL Server Management Console to open the table and edit it there?
--

John W. Vinson [MVP]

Thanks for your suggestions!

I verified that I can add rows directly to the table. I suspect the problem
is with a trigger in the SQL database - I know there were some recent
additions of that sort. I will see if we can use a subquery to replace the
SQL trigger. We'll see if that works. Thanks very much!

Myrinda

"John W. Vinson" wrote:

> On Tue, 13 Apr 2010 08:27:02 -0700, Myrinda
> <(E-Mail Removed)> wrote:
>
> >The table feeding the query is a linked SQL database. I do have other
> >forms/queries from the same table that allow insertion and editing. I just
> >can't figure out why this one won't.
>
> I've seen this problem in two circumstances:
>
> 1. The SQL/Server table does not have a Primary Key, or Access doesn't know
> what the Primary Key is.
> 2. There's a trigger or constraint on the SQL table which prevents data being
> added.
>
> Can you add records if you open the table in table datasheet view? Can you use
> SQL Server Management Console to open the table and edit it there?
> --
>
> John W. Vinson [MVP]
> .
>

On Tue, 13 Apr 2010 15:41:02 -0700, Myrinda
<(E-Mail Removed)> wrote:

>Thanks for your suggestions!
>
>I verified that I can add rows directly to the table. I suspect the problem
>is with a trigger in the SQL database - I know there were some recent
>additions of that sort. I will see if we can use a subquery to replace the
>SQL trigger. We'll see if that works. Thanks very much!

Hrm. Any Insert trigger should affect data entry whether it's from the table
or from Access. Can you in fact add data in the Access linked table? What is
the Primary Key of the table? Is it marked as the Primary Key when you view
the table design in Access?
--

John W. Vinson [MVP]

Yes, it is possible to add and edit records in the linked table and it
updates in the SQL database too. There is a primary key, which shows when
you view the table in design view. It's an Autonumber ID field.

Thank you very much for your continued help.

Myrinda


"John W. Vinson" wrote:

> On Tue, 13 Apr 2010 15:41:02 -0700, Myrinda
> <(E-Mail Removed)> wrote:
>
> >Thanks for your suggestions!
> >
> >I verified that I can add rows directly to the table. I suspect the problem
> >is with a trigger in the SQL database - I know there were some recent
> >additions of that sort. I will see if we can use a subquery to replace the
> >SQL trigger. We'll see if that works. Thanks very much!
>
> Hrm. Any Insert trigger should affect data entry whether it's from the table
> or from Access. Can you in fact add data in the Access linked table? What is
> the Primary Key of the table? Is it marked as the Primary Key when you view
> the table design in Access?
> --
>
> John W. Vinson [MVP]
> .
>

On Tue, 13 Apr 2010 08:27:02 -0700, Myrinda
<(E-Mail Removed)> wrote:

>The table feeding the query is a linked SQL database. I do have other
>forms/queries from the same table that allow insertion and editing. I just
>can't figure out why this one won't. I've recreated the query just to be
>sure I have all the correct fields (and because it used to work!) but I'm not
>sure what the problem is now. What do you think?

If this query isn't updateable, there's something corrupt. I'd suggest copying
and pasting the SQL out to Notepad; delete the query; compact and repair the
database to clean out any residuum; create a new query, go to SQL view without
adding any tables, and copy the SQL back in.
--

John W. Vinson [MVP]