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

[Ferasse]

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.C trct_Cpy_ID,
Contract_info.C trct_ID,
Contract_info.C trct_Ref,
Contract_info.C trct_Branch_ID,
Contract_info.C trct_Start_Date ,
Contract_info.C trct_Service_ID ,
Contract_info.C trct_End_Date,
Contract_info.C trct_Duration,
Contract_info.C trct_TO_month,
Contract_info.C trct_Investment _Y_N,
Contract_info.C trct_Investment _Amount, Contract_info.C trct_Terms_of_P ayment,
Contract_info.C trct_Lunch_p_Da y,
Contract_info.C trct_Lunch_u_pr ice,
Contract_info.C trct_BF_p_Day,
Contract_info.C trct_BF_u_price ,
Contract_info.C trct_Diner_p_Da y,
Contract_info.C trct_Diner_u_pr ice,
Contract_info.C trct_Management _Fee,
Contract_info.C trct_Nb_Staff,
Contract_info.C trct_Staff_u_pr ice,
Contract_info.C trct_Scope,
Contract_info.C trct_Comments,
StatusContract. Status_Name,
Company_site.Cp y_Code,
Company_site.Cp y_Name_EN,
People.Name_EN,
Branch.Branch_N ame,
Branch.Branch_N ame_Code,
City.City_Name,
Service.Service ,
Service.Descrip tion,
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_I D=Contract_info .Ctrct_Branch_I D)
ON People.Staff_ID =Contract_info. Ctrct_Sales_ID)
ON City.CityID=Bra nch.Branch_City _ID)
ON Company_site.Cp y_ID=Contract_i nfo.Ctrct_Cpy_I D)
ON Service.Service ID=Contract_inf o.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.

[epots9]

Moved to the Access Forums, where the resident experts can better assist you.

**Moved from Programming Challenges.

[MMcCarthy]

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.

[Ferasse]

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.

[MMcCarthy]

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

[dozingquinn]

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