calculated field means not being able to change subform fields?

[Peter]

Hello everyone,



I'm trying to use a calculated field in a query and therefore the form that
is based on this query.

This form is used as a sub form on the main form which I mean to use for the
actual task for which it was developed.

I have an entry problem, though.

It seems that you cannot change any field on any row of the sub form (with
data sheet view) if it contains a calculated field.

I need to be able to multiply a number of items by a price. I tried it by
adding a column called Quantity to the Articles table

in order to be able to enter the proper quantities for the delivered
articles.

I also added the calculated field which should give the product of Quantity
multiplied by Price.

I soon found out that the sub form did not permit me to enter any trial
quantity.

After removing the calculated field from the appropriate query and form, it
suddenly was possible to enter quantities.

This was of no use of course. But it is why I concluded that the presence of
a calculated field is what caused this behaviour.



Can anyone help me with this?



Greetings,

Peter

 

[Dirk Goldgar]

in message

Hello everyone,

I'm trying to use a calculated field in a query and therefore the form
that is based on this query.

This form is used as a sub form on the main form which I mean to use for
the actual task for which it was developed.

I have an entry problem, though.

It seems that you cannot change any field on any row of the sub form (with
data sheet view) if it contains a calculated field.

I need to be able to multiply a number of items by a price. I tried it by
adding a column called Quantity to the Articles table

in order to be able to enter the proper quantities for the delivered
articles.

I also added the calculated field which should give the product of
Quantity multiplied by Price.

I soon found out that the sub form did not permit me to enter any trial
quantity.

After removing the calculated field from the appropriate query and form,
it suddenly was possible to enter quantities.

This was of no use of course. But it is why I concluded that the presence
of a calculated field is what caused this behaviour.

This should not be the case. The calculated field itself will not be
editable, naturally, but other fields should be editable so long as the
query itself is updatable. How did you change the query to support the
calculated field? Please post the SQL of the query.

 

[Peter]

This is the sql of the query. Be advised: most names are in Dutch !

SELECT [T20 Objecten en Artikelen].Objectnr, [T00 Objecten].[Naam klant /
pand], [T20 Objecten en Artikelen].Crediteur, [T05
Leveranciers].Leverancier, [T20 Objecten en Artikelen].Artikelnr, [T10
Artikelen].[Artikel Omschrijving], [T10 Artikelen].Brutoprijs, [T20 Objecten
en Artikelen].Kortingsperc, [T20 Objecten en Artikelen].Aantal,
Round(([Brutoprijs]*[Aantal])*(1-[Kortingsperc]),2) AS [Te betalen]
FROM ([T00 Objecten] INNER JOIN ([T20 Objecten en Artikelen] INNER JOIN [T05
Leveranciers] ON [T20 Objecten en Artikelen].Crediteur = [T05
Leveranciers].Crediteur) ON [T00 Objecten].Object = [T20 Objecten en
Artikelen].Objectnr) INNER JOIN [T10 Artikelen] ON ([T10
Artikelen].Artikelnr = [T20 Objecten en Artikelen].Artikelnr) AND ([T10
Artikelen].Crediteur = [T20 Objecten en Artikelen].Crediteur) AND ([T05
Leveranciers].Crediteur = [T10 Artikelen].Crediteur);

Translation:
From Dutch: To English (what is meant in Dutch)
Objecten en Artikelen Locations and Articles
Naam klant / pand Client name / locations
Crediteur Number of supplier
Leverancier Name of supplier
Artikelnr Article number
Artikel Omschrijving Article description
Aantal Quantity
Brutoprijs Price (before discountpercentage
is applied)
Kortingsgperc Discount percentage
Te betalen To be paid for x times article A
minus discount (VAT not included)

Peter

 

[Dirk Goldgar]

"Peter" wrote in message

This is the sql of the query. Be advised: most names are in Dutch !

SELECT [T20 Objecten en Artikelen].Objectnr, [T00 Objecten].[Naam klant /
pand], [T20 Objecten en Artikelen].Crediteur, [T05
Leveranciers].Leverancier, [T20 Objecten en Artikelen].Artikelnr, [T10
Artikelen].[Artikel Omschrijving], [T10 Artikelen].Brutoprijs, [T20
Objecten en Artikelen].Kortingsperc, [T20 Objecten en Artikelen].Aantal,
Round(([Brutoprijs]*[Aantal])*(1-[Kortingsperc]),2) AS [Te betalen]
FROM ([T00 Objecten] INNER JOIN ([T20 Objecten en Artikelen] INNER JOIN
[T05 Leveranciers] ON [T20 Objecten en Artikelen].Crediteur = [T05
Leveranciers].Crediteur) ON [T00 Objecten].Object = [T20 Objecten en
Artikelen].Objectnr) INNER JOIN [T10 Artikelen] ON ([T10
Artikelen].Artikelnr = [T20 Objecten en Artikelen].Artikelnr) AND ([T10
Artikelen].Crediteur = [T20 Objecten en Artikelen].Crediteur) AND ([T05
Leveranciers].Crediteur = [T10 Artikelen].Crediteur);

Translation:
From Dutch: To English (what is meant in
Dutch)
Objecten en Artikelen Locations and Articles
Naam klant / pand Client name / locations
Crediteur Number of supplier
Leverancier Name of supplier
Artikelnr Article number
Artikel Omschrijving Article description
Aantal Quantity
Brutoprijs Price (before
discountpercentage is applied)
Kortingsgperc Discount percentage
Te betalen To be paid for x times article
A minus discount (VAT not included)

Am I right in thinking it's the [Te betalen] field that you added? Did you
also add another table to the original query, in order to calculate this
field?

If you open this query in datasheet view, is it updatable?

If you delete the [Te betalen] field, without removing any of the tables,
does the query become updatable? (I wouldn't expect so.)

There are a variety of conditions governing when a query is updatable. You
may want to review the help topic, "When can I update data from a query?",
which you may find at this link:

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051880011033


Since your query joins a number of tables, you may find it that setting your
form's RecordsetType property to "Dynaset (Inconsistent Updates)" will make
it updatable. However, I can't guarantee that, because I don't know the
relationships among these tables.

 

[Peter]

Hello Dirk,

Am I right in thinking it's the [Te betalen] field that you added? Did
you also add another table to the original query, in order to calculate
this field?

1. Yes, you are right. this is the added field. 2. To the best of my
knowlegde I only put in the necessary tables after which I added the calc
field.

side note: Before making the query now being used (of which I posted the
sql lines), I added the Aantal field (=quantity field) on purpose to the T20
Locations and Articles table, so that (I thought) I could put in the
quantities through the query data sheet view and the calculated field in the
query would perform the calculations on them. But that failed as described
in my first post. Is it perhaps possible to remove the Quantity field from
the table and put in another field in the query in which numbers can be
typed that are subsequently used in the calc.field? The numbers used in the
calculation (Aantal or Quantity) are not meant to remain in table T20. Maybe
they could be used to a different end later on.

If you open this query in datasheet view, is it updatable?

No, it is not updatable.

If you delete the [Te betalen] field, without removing any of the tables,
does the query become updatable? (I wouldn't expect so.)

No, it does not.

"Peter" wrote in message

This is the sql of the query. Be advised: most names are in Dutch !

SELECT [T20 Objecten en Artikelen].Objectnr, [T00 Objecten].[Naam klant /
pand], [T20 Objecten en Artikelen].Crediteur, [T05
Leveranciers].Leverancier, [T20 Objecten en Artikelen].Artikelnr, [T10
Artikelen].[Artikel Omschrijving], [T10 Artikelen].Brutoprijs, [T20
Objecten en Artikelen].Kortingsperc, [T20 Objecten en Artikelen].Aantal,
Round(([Brutoprijs]*[Aantal])*(1-[Kortingsperc]),2) AS [Te betalen]
FROM ([T00 Objecten] INNER JOIN ([T20 Objecten en Artikelen] INNER JOIN
[T05 Leveranciers] ON [T20 Objecten en Artikelen].Crediteur = [T05
Leveranciers].Crediteur) ON [T00 Objecten].Object = [T20 Objecten en
Artikelen].Objectnr) INNER JOIN [T10 Artikelen] ON ([T10
Artikelen].Artikelnr = [T20 Objecten en Artikelen].Artikelnr) AND ([T10
Artikelen].Crediteur = [T20 Objecten en Artikelen].Crediteur) AND ([T05
Leveranciers].Crediteur = [T10 Artikelen].Crediteur);

Translation:
From Dutch: To English (what is meant in
Dutch)
Objecten en Artikelen Locations and Articles
Naam klant / pand Client name / locations
Crediteur Number of supplier
Leverancier Name of supplier
Artikelnr Article number
Artikel Omschrijving Article description
Aantal Quantity
Brutoprijs Price (before
discountpercentage is applied)
Kortingsgperc Discount percentage
Te betalen To be paid for x times article
A minus discount (VAT not included)

Am I right in thinking it's the [Te betalen] field that you added? Did
you also add another table to the original query, in order to calculate
this field?

If you open this query in datasheet view, is it updatable?

If you delete the [Te betalen] field, without removing any of the tables,
does the query become updatable? (I wouldn't expect so.)

There are a variety of conditions governing when a query is updatable.
You may want to review the help topic, "When can I update data from a
query?", which you may find at this link:

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051880011033


Since your query joins a number of tables, you may find it that setting
your form's RecordsetType property to "Dynaset (Inconsistent Updates)"
will make it updatable. However, I can't guarantee that, because I don't
know the relationships among these tables.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hello Dirk,

Am I right in thinking it's the [Te betalen] field that you added? Did
you also add another table to the original query, in order to calculate
this field?

1. Yes, you are right. this is the added field. 2. To the best of my
knowlegde I only put in the necessary tables after which I added the calc
field.

side note: Before making the query now being used (of which I posted the
sql lines), I added the Aantal field (=quantity field) on purpose to the
T20 Locations and Articles table, so that (I thought) I could put in the
quantities through the query data sheet view and the calculated field in
the query would perform the calculations on them. But that failed as
described in my first post. Is it perhaps possible to remove the Quantity
field from the table and put in another field in the query in which
numbers can be typed that are subsequently used in the calc.field? The
numbers used in the calculation (Aantal or Quantity) are not meant to
remain in table T20. Maybe they could be used to a different end later on.

If you open this query in datasheet view, is it updatable?

No, it is not updatable.

If you delete the [Te betalen] field, without removing any of the tables,
does the query become updatable? (I wouldn't expect so.)

No, it does not.

Did you look at the help topic for which I posted the link?

Did you try setting the RecordsetType to "Dynaset (Inconsistent Updates)"?