blank form, can't insert new record

[Myrinda]

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

 

[Al Campagna]

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."

 

[golfinray]

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.

 

[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

 

[golfinray]

Turn it into a make table query, make the table, change your form
recordsource to the table and see if it is editable.

 

[John W. Vinson]

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?

 

[Myrinda]

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]

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?

 

[Myrinda]

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]

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.

 

[Al Campagna]

John,
I could be way off base here, but this problem has similarity to a post
I currently have in the ...public.access NG. 4/14/10 1:58am
"AdminUser = form works - User = form fails"

I confirmed the permissions for the query and form to allow insertions
and edits.

In an FrontEnd/BackEnd database, one user could not update a table.
No add, edit, delete... but Admin could.

It appears that when we gave that User table permission via the FE, that
did
not work.
When we went to the BE, and worked the Tools/Security permissions
through
the BE... voila... that user could now run the form, and edit and update the
table.
Never ran into that before...

It's like doing realtionships in the FE. They don't have any effect.
The BE realtionships have the control.
Again... apologies if I'm may be way off base on this...

Al Campagna

 

[John W. Vinson]

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.

See Al Campagna's response to me in this thread... does this user have SQL
insert permissions on the table?