Updateable Forms (1 Viewer)

[SteveD]

Does anyone know if it is possible to have a form that enables user's to update and amend records-the record source for the form is a query with calculations in it. (I need to have calculation's in it to show the values in different currencies)

 

[Rich]

The simple answer is yes, providing your database design is sound, you'll have to provide more details for detailed replies.
HTH

 

[SteveD]

Hi, thanks for reply..

I have a form where the record source is a query with two tables. There is a one to many relationship between the tables. On the 'one' side there is information on a particular country such as currency, population etc. On the 'many' side there are records relating to particular companies and products that are sold in each country.
I have selected fields from both tables in the query and have also calculated fields which work out the price of a product into different currencies.
I have set 'allow edits' 'deletions' 'additions' to Yes in the forms properties, however it is still not letting me update fields when viewing the form!

 

[SteveD]

Hi, thanks for reply..

I have a form where the record source is a query with two tables. There is a one to many relationship between the tables. On the 'one' side there is information on a particular country such as currency, population etc. On the 'many' side there are records relating to particular companies and products that are sold in each country.
I have selected fields from both tables in the query and have also calculated fields which work out the price of a product into different currencies.
I have set 'allow edits' 'deletions' 'additions' to Yes in the forms properties, however it is still not letting me update fields when viewing the form!

 

[SteveD]

Can anyone hrlp with this?

Thanks,

SteveD

 

[KevinM]

Split your form into two using a main form and subform.
Use linked tables instead of one query.
This is a better way of viewing 'one to many' records than on one form.

HTH

 

[SteveD]

Thanks KevinM,

Sorry, but I'm still a little unclear as to why it's a better way.
The only reason I ask is that I have quite a few forms like this where I need to do a calculation with currencies-the local currency being a field in the 'one' side table and prices of products on the many side. Is it not possibly to have calculations in forms and at the same time be able to update records?

 

[Pat Hartman]

The problem is not with the form. It is with the query. My guess is that if you just run the query, you will not be able to update with it. Queries need to satisfy certain requirements in order to be updateable.

If your query uses domain functions such as Sum(), Avg(), Min(), Max(), etc., it will not be updateable under any conditions.

Download this white paper for an explaination of what makes a query updateable: http://support.microsoft.com/support/kb/articles/Q148/5/81.asp?FinishURL=%2Fdownloads%2Frelease%2Easp%3FReleaseID%3D13605%26redirect%3Dno

 

[SteveD]

Thanks Pat,

As a newcomer to Access, this appears to be a major weakness of the application, although I guess there's some very intelligent reasoning behind it which I havn't figured out yet!-I'll have a look at the white paper, thanks for the info!

 

[Pat Hartman]

The problem isn't a weakness with Jet's implementation of SQL, no RDBMS can directly update the underlying tables using a "totals" query. The "key" data is summarized away. In fact I find Jet more flexible than several other major RDBMS' when it comes to what updates it does allow. Since you shouldn't be storing calculated data in any event, not being able to do updates with a "totals" query should not be a problem.