I have a form I am trying to develop with an subform which has a datasheet that is updateable but some of the fields in the datasheet are display only. I created a query to populate the datasheet but now I cannot update the fields. How do I acomplish this. Thanks in advance you any advise you can give. I also have to filter the subform data sheet from selections from 3 combo boxes from the form.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Problem with multi-table subform datasheet not updateable 2
- Thread starter vzjfgm
- Start date
Hi,
In the properties of the fields in question, is the Locked Property set to No/False?
Leigh Moore
Solutions 4 MS Office Ltd
In the properties of the fields in question, is the Locked Property set to No/False?
Leigh Moore
Solutions 4 MS Office Ltd
-
1
- Thread starter
- #3
-
1
- #4
Hi!
Post the SQL that the subform is based on, that will help us find the problem.
In general the main reasons that a recordset is not updatable are:
1.) The query contains calculated values.
2.) The query does not contain enough information for Access to determine which record in each table to update.
3.) The query is too complex for Access.
The standard solutions are:
1.) Remove calculated values from the query and move them to the form.
2.) Make sure the primary key from each table is part of the select statement even if it doesn't need to be displayed.
3.) Try nesting subforms or change the recordset type to Dynaset(Inconsistant Update).
hth
Jeff Bridgham
bridgham@purdue.edu
Post the SQL that the subform is based on, that will help us find the problem.
In general the main reasons that a recordset is not updatable are:
1.) The query contains calculated values.
2.) The query does not contain enough information for Access to determine which record in each table to update.
3.) The query is too complex for Access.
The standard solutions are:
1.) Remove calculated values from the query and move them to the form.
2.) Make sure the primary key from each table is part of the select statement even if it doesn't need to be displayed.
3.) Try nesting subforms or change the recordset type to Dynaset(Inconsistant Update).
hth
Jeff Bridgham
bridgham@purdue.edu
- Thread starter
- #5
Hi jeff,
Thanks for your suggestions!! I reviewed them and I still can't get it to work. Here is the SQL for the dataform.
SELECT [last]+", "+[first] AS empname, Tbl_Employees.Shift, Tbl_Employees.Area, Tbl_Employees.Clock, Tbl_Cumm_OT_Hrs.[Total Cumm OT Hrs], Tbl_weekly_Manpower.Mon_E_Sch, Tbl_weekly_Manpower.Mon_O_Sch, Tbl_weekly_Manpower.Tue_E_Sch, Tbl_weekly_Manpower.Tue_O_Sch, Tbl_weekly_Manpower.Wed_E_Sch, Tbl_weekly_Manpower.Wed_O_Sch, Tbl_weekly_Manpower.Thr_E_Sch, Tbl_weekly_Manpower.Thr_O_Sch, Tbl_weekly_Manpower.Fri_E_Sch, Tbl_weekly_Manpower.Fri_O_Sch, Tbl_weekly_Manpower.Sat_E_Sch, Tbl_weekly_Manpower.Sat_O_Sch, Tbl_weekly_Manpower.Sat_8hr_Sch, Tbl_weekly_Manpower.Sun_E_Sch, Tbl_weekly_Manpower.Sun_O_Sch, Tbl_weekly_Manpower.Sun_8hr_Sch, Tbl_weekly_Manpower.Mon_E_Act, Tbl_weekly_Manpower.Mon_O_Act, Tbl_weekly_Manpower.Tue_E_Act, Tbl_weekly_Manpower.Tue_O_Act, Tbl_weekly_Manpower.Wed_E_Act, Tbl_weekly_Manpower.Wed_O_Act, Tbl_weekly_Manpower.Thr_E_Act, Tbl_weekly_Manpower.Thr_O_Act, Tbl_weekly_Manpower.Fri_E_Act, Tbl_weekly_Manpower.Fri_O_Act, Tbl_weekly_Manpower.Sat_E_Act, Tbl_weekly_Manpower.Sat_O_Act, Tbl_weekly_Manpower.Sat_8hr_Act, Tbl_weekly_Manpower.Sun_E_Act, Tbl_weekly_Manpower.Sun_O_Act, Tbl_weekly_Manpower.Sun_8hr_Act, Tbl_weekly_Manpower.Wk_End_dt
FROM (Tbl_Employees LEFT JOIN Tbl_weekly_Manpower ON Tbl_Employees.Clock = Tbl_weekly_Manpower.empclock) LEFT JOIN Tbl_Cumm_OT_Hrs ON Tbl_Employees.Clock = Tbl_Cumm_OT_Hrs.clock;
Thank you!!! Karen
Thanks for your suggestions!! I reviewed them and I still can't get it to work. Here is the SQL for the dataform.
SELECT [last]+", "+[first] AS empname, Tbl_Employees.Shift, Tbl_Employees.Area, Tbl_Employees.Clock, Tbl_Cumm_OT_Hrs.[Total Cumm OT Hrs], Tbl_weekly_Manpower.Mon_E_Sch, Tbl_weekly_Manpower.Mon_O_Sch, Tbl_weekly_Manpower.Tue_E_Sch, Tbl_weekly_Manpower.Tue_O_Sch, Tbl_weekly_Manpower.Wed_E_Sch, Tbl_weekly_Manpower.Wed_O_Sch, Tbl_weekly_Manpower.Thr_E_Sch, Tbl_weekly_Manpower.Thr_O_Sch, Tbl_weekly_Manpower.Fri_E_Sch, Tbl_weekly_Manpower.Fri_O_Sch, Tbl_weekly_Manpower.Sat_E_Sch, Tbl_weekly_Manpower.Sat_O_Sch, Tbl_weekly_Manpower.Sat_8hr_Sch, Tbl_weekly_Manpower.Sun_E_Sch, Tbl_weekly_Manpower.Sun_O_Sch, Tbl_weekly_Manpower.Sun_8hr_Sch, Tbl_weekly_Manpower.Mon_E_Act, Tbl_weekly_Manpower.Mon_O_Act, Tbl_weekly_Manpower.Tue_E_Act, Tbl_weekly_Manpower.Tue_O_Act, Tbl_weekly_Manpower.Wed_E_Act, Tbl_weekly_Manpower.Wed_O_Act, Tbl_weekly_Manpower.Thr_E_Act, Tbl_weekly_Manpower.Thr_O_Act, Tbl_weekly_Manpower.Fri_E_Act, Tbl_weekly_Manpower.Fri_O_Act, Tbl_weekly_Manpower.Sat_E_Act, Tbl_weekly_Manpower.Sat_O_Act, Tbl_weekly_Manpower.Sat_8hr_Act, Tbl_weekly_Manpower.Sun_E_Act, Tbl_weekly_Manpower.Sun_O_Act, Tbl_weekly_Manpower.Sun_8hr_Act, Tbl_weekly_Manpower.Wk_End_dt
FROM (Tbl_Employees LEFT JOIN Tbl_weekly_Manpower ON Tbl_Employees.Clock = Tbl_weekly_Manpower.empclock) LEFT JOIN Tbl_Cumm_OT_Hrs ON Tbl_Employees.Clock = Tbl_Cumm_OT_Hrs.clock;
Thank you!!! Karen
Hi Karen!
This query does contain a calculated value:
[last] + "," + [first] As empname
Try just pulling last and first separately and then putting them together in the text box to see if that helps.
Also, what are the Primary keys for Tbl_weekly_Manpower and Tbl_Cumm_OT_Hrs? Is the relationship between these table and Tbl_Employees one-many? What is the primary key for Tbl_Employees?
hth
Jeff Bridgham
bridgham@purdue.edu
This query does contain a calculated value:
[last] + "," + [first] As empname
Try just pulling last and first separately and then putting them together in the text box to see if that helps.
Also, what are the Primary keys for Tbl_weekly_Manpower and Tbl_Cumm_OT_Hrs? Is the relationship between these table and Tbl_Employees one-many? What is the primary key for Tbl_Employees?
hth
Jeff Bridgham
bridgham@purdue.edu
- Thread starter
- #7
Hi Karen!
Yes it can make a difference. It makes it important to include the primary key from the many table so Access knows what row to update. Without the PK there could be more than one row in the query that is identical and Access won't know what to do if an update occurs so it just doesn't allow them.
Jeff Bridgham
bridgham@purdue.edu
Yes it can make a difference. It makes it important to include the primary key from the many table so Access knows what row to update. Without the PK there could be more than one row in the query that is identical and Access won't know what to do if an update occurs so it just doesn't allow them.
Jeff Bridgham
bridgham@purdue.edu
- Thread starter
- #9
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question