If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
blank form, can't insert new record
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 |
#2
|
|||
|
|||
blank form, can't insert new record
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" wrote in message ... 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 |
#3
|
|||
|
|||
blank form, can't insert new record
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 |
#4
|
|||
|
|||
blank form, can't insert new record
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_Count y, SoutheastTeamDrillingScheduleTable.State, SoutheastTeamDrillingScheduleTable.Location, SoutheastTeamDrillingScheduleTable.Bottom_hole_loc ation, SoutheastTeamDrillingScheduleTable.Legal_Descripti on, SoutheastTeamDrillingScheduleTable.Proposed_Total_ Depth, SoutheastTeamDrillingScheduleTable.Prop_TVD, SoutheastTeamDrillingScheduleTable.Primary_Objecti ve, SoutheastTeamDrillingScheduleTable.Secondary_Objec tive, SoutheastTeamDrillingScheduleTable.BCP_GWI, SoutheastTeamDrillingScheduleTable.ACP_BPO_NRI, SoutheastTeamDrillingScheduleTable.Scoping_Estimat e_PreAFE_DHC, SoutheastTeamDrillingScheduleTable.Scoping_Estimat e_PreAFE_Comp_Cost, SoutheastTeamDrillingScheduleTable.Earliest_Lease_ Expiration, SoutheastTeamDrillingScheduleTable.Lease_Exp_2, SoutheastTeamDrillingScheduleTable.Acres_Exp1, SoutheastTeamDrillingScheduleTable.Acres_Exp2, SoutheastTeamDrillingScheduleTable.Well_Type, SoutheastTeamDrillingScheduleTable.Type_of_Locatio n FROM SoutheastTeamDrillingScheduleTable WHERE (((SoutheastTeamDrillingScheduleTable.Team)="Hayne sville")); 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" wrote in message ... 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 . |
#5
|
|||
|
|||
blank form, can't insert new record
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_Count y, SoutheastTeamDrillingScheduleTable.State, SoutheastTeamDrillingScheduleTable.Location, SoutheastTeamDrillingScheduleTable.Bottom_hole_loc ation, SoutheastTeamDrillingScheduleTable.Legal_Descripti on, SoutheastTeamDrillingScheduleTable.Proposed_Total_ Depth, SoutheastTeamDrillingScheduleTable.Prop_TVD, SoutheastTeamDrillingScheduleTable.Primary_Objecti ve, SoutheastTeamDrillingScheduleTable.Secondary_Objec tive, SoutheastTeamDrillingScheduleTable.BCP_GWI, SoutheastTeamDrillingScheduleTable.ACP_BPO_NRI, SoutheastTeamDrillingScheduleTable.Scoping_Estimat e_PreAFE_DHC, SoutheastTeamDrillingScheduleTable.Scoping_Estimat e_PreAFE_Comp_Cost, SoutheastTeamDrillingScheduleTable.Earliest_Lease_ Expiration, SoutheastTeamDrillingScheduleTable.Lease_Exp_2, SoutheastTeamDrillingScheduleTable.Acres_Exp1, SoutheastTeamDrillingScheduleTable.Acres_Exp2, SoutheastTeamDrillingScheduleTable.Well_Type, SoutheastTeamDrillingScheduleTable.Type_of_Locatio n FROM SoutheastTeamDrillingScheduleTable WHERE (((SoutheastTeamDrillingScheduleTable.Team)="Hayne sville")); 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" wrote in message ... 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 . |
#6
|
|||
|
|||
blank form, can't insert new record
On Tue, 13 Apr 2010 08:27:02 -0700, Myrinda
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] |
#7
|
|||
|
|||
blank form, can't insert new record
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 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] . |
#8
|
|||
|
|||
blank form, can't insert new record
On Tue, 13 Apr 2010 15:41:02 -0700, Myrinda
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] |
#9
|
|||
|
|||
blank form, can't insert new record
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 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] . |
#10
|
|||
|
|||
blank form, can't insert new record
On Tue, 13 Apr 2010 08:27:02 -0700, Myrinda
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] |
|
Thread Tools | |
Display Modes | |
|
|