By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

MS-ACCESS - Insert Record - Cannot Update Fields Values In Query-Based Form

P: 2
Hi,

I'm an occasional Ms-Access developer, so there is still a lot of stuff that I don't get...

Right now, I'm working on a database that stores contractual information.

One of the form that I created is based on a query that links several tables.

When I try to insert a record in this query-based form, I can only update the fields that are used to build relations with other tables.

The fields that are not involved in the database relations are simply locked.

However, when I am navigating records, I can update all fields.

So basically, I only have problems when I want to insert a new record.

---New Info:
Here is the query behind the form

SELECT Contract_info.Ctrct_Cpy_ID,
Contract_info.Ctrct_ID,
Contract_info.Ctrct_Ref,
Contract_info.Ctrct_Branch_ID,
Contract_info.Ctrct_Start_Date,
Contract_info.Ctrct_Service_ID,
Contract_info.Ctrct_End_Date,
Contract_info.Ctrct_Duration,
Contract_info.Ctrct_TO_month,
Contract_info.Ctrct_Investment_Y_N,
Contract_info.Ctrct_Investment_Amount, Contract_info.Ctrct_Terms_of_Payment,
Contract_info.Ctrct_Lunch_p_Day,
Contract_info.Ctrct_Lunch_u_price,
Contract_info.Ctrct_BF_p_Day,
Contract_info.Ctrct_BF_u_price,
Contract_info.Ctrct_Diner_p_Day,
Contract_info.Ctrct_Diner_u_price,
Contract_info.Ctrct_Management_Fee,
Contract_info.Ctrct_Nb_Staff,
Contract_info.Ctrct_Staff_u_price,
Contract_info.Ctrct_Scope,
Contract_info.Ctrct_Comments,
StatusContract.Status_Name,
Company_site.Cpy_Code,
Company_site.Cpy_Name_EN,
People.Name_EN,
Branch.Branch_Name,
Branch.Branch_Name_Code,
City.City_Name,
Service.Service,
Service.Description,
People.Position

FROM StatusContract
INNER JOIN (Service
INNER JOIN (Company_site
INNER JOIN (City
INNER JOIN (ADEN_People
INNER JOIN (ADEN_Branch
INNER JOIN Contract_info
ON Branch.Branch_ID=Contract_info.Ctrct_Branch_ID)
ON People.Staff_ID=Contract_info.Ctrct_Sales_ID)
ON City.CityID=Branch.Branch_City_ID)
ON Company_site.Cpy_ID=Contract_info.Ctrct_Cpy_ID)
ON Service.ServiceID=Contract_info.Ctrct_Service_ID)
ON StatusContract.Status_Ctrct_ID=Contract_info.Ctrct _Status_ID;

==> I have exactly the same behavior when I try to add a line in the table resulting from this query


Thanks a lot for your help...

Regards,

Ferasse.
Jan 14 '08 #1
Share this Question
Share on Google+
5 Replies


epots9
Expert 100+
P: 1,351
Moved to the Access Forums, where the resident experts can better assist you.

**Moved from Programming Challenges.
Jan 14 '08 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Ferasse

The problem is you can only base a data entry form on an updatable query. Not all queries are updatable.

For example if you join two tables where there is a one to many relationship then this query is not updatable. That is there are many records in the second table which are related to one record in the first table. For instance many invoices in tblInvoices to one customer in tblCustomers.

You can't just join these two tables in a query and base a form on them. You need one main form for Customers and use a sub form or popup form to add invoices.
Jan 14 '08 #3

P: 2
Hi Ferasse

The problem is you can only base a data entry form on an updatable query. Not all queries are updatable.

For example if you join two tables where there is a one to many relationship then this query is not updatable. That is there are many records in the second table which are related to one record in the first table. For instance many invoices in tblInvoices to one customer in tblCustomers.

You can't just join these two tables in a query and base a form on them. You need one main form for Customers and use a sub form or popup form to add invoices.
Thank a lot man...

I could have spent a lot of days in the properties trying all kind of combinations.

Well, thanks again!!!

Ferasse.
Jan 15 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank a lot man...

I could have spent a lot of days in the properties trying all kind of combinations.

Well, thanks again!!!

Ferasse.
You're welcome.

Mary
(Wo)man :D
Jan 15 '08 #5

P: 28
Yes - I was (actually am) facing this problem to - and am trying to rectify it before my partner yells at me for breaking her accounts!

Another good 'trouble-shooter' guide can be found here:

http://allenbrowne.com/ser-61.html

David
Jan 21 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.