Updating data i multiple tables from a form

[Petterq]

Hi.
I have a form that shows fields from three different tables in a tabular
format.

I am not able to update the tables from this form, how can i achieve this?

Thanks for all help.

 

[Allen Browne]

Presumably you created a query that uses 3 tables, and used this as the
source for your form.

Unless you know what you are doing, this is generally not the way to go:
when you create a new record, which table do you expect the new record to be
created in? There are cases where you would add a lookup table or two to the
query, but if you are going 3 levels deep, you probably need to redesign
your interface. Typically you would use a form and a subform.

For specific details on why a query is not updatable, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

 

[Petterq]

Hi.

No, i did not use any query. I retreive the data directly from the tables
using the SELECT function in the Source field. The select being as follows:

SELECT [Lager-mtrack].[Forhandler ID], [Lager-mtrack].[GSM no],
[Lager-mtrack].[Serie nr], [Lager-mtrack].[Fakturert],
[Lager-mtrack].[Provisjonsberettiget], [mtrack-abonnement].[Fra-dato],
[mtrack-abonnement].[Til-dato], [mtrack-abonnement].[Belastet_til],
[mtrack-abonnement].[Prov-ber-til], [Kundedatabase].[Firma] FROM
(Kundedatabase INNER JOIN [mtrack-abonnement] ON Kundedatabase.[Firma
ID]=[mtrack-abonnement].FirmaID) INNER JOIN [Lager-mtrack] ON
[mtrack-abonnement].[GSM-nummer]=[Lager-mtrack].[GSM no];

It gets rather long, and the wording is in Norwegian, sorry for that.

And since I point directly to each table (there are relationships between
them) I don't see why updating is a problem.

Regards
PetterQ

 

[Douglas J. Steele]

While you may not have saved the SQL as a query, you are, in fact, using a
query. That means Allen's cited article is relevant to you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi.

No, i did not use any query. I retreive the data directly from the tables
using the SELECT function in the Source field. The select being as
follows:

SELECT [Lager-mtrack].[Forhandler ID], [Lager-mtrack].[GSM no],
[Lager-mtrack].[Serie nr], [Lager-mtrack].[Fakturert],
[Lager-mtrack].[Provisjonsberettiget], [mtrack-abonnement].[Fra-dato],
[mtrack-abonnement].[Til-dato], [mtrack-abonnement].[Belastet_til],
[mtrack-abonnement].[Prov-ber-til], [Kundedatabase].[Firma] FROM
(Kundedatabase INNER JOIN [mtrack-abonnement] ON Kundedatabase.[Firma
ID]=[mtrack-abonnement].FirmaID) INNER JOIN [Lager-mtrack] ON
[mtrack-abonnement].[GSM-nummer]=[Lager-mtrack].[GSM no];

It gets rather long, and the wording is in Norwegian, sorry for that.

And since I point directly to each table (there are relationships between
them) I don't see why updating is a problem.

Regards
PetterQ

Presumably you created a query that uses 3 tables, and used this as the
source for your form.

Unless you know what you are doing, this is generally not the way to go:
when you create a new record, which table do you expect the new record to
be
created in? There are cases where you would add a lookup table or two to
the
query, but if you are going 3 levels deep, you probably need to redesign
your interface. Typically you would use a form and a subform.

For specific details on why a query is not updatable, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

 

[Petterq]

OK, thanks.

I checked some indexing, and that solved the problem.

While you may not have saved the SQL as a query, you are, in fact, using a
query. That means Allen's cited article is relevant to you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi.

No, i did not use any query. I retreive the data directly from the tables
using the SELECT function in the Source field. The select being as
follows:

SELECT [Lager-mtrack].[Forhandler ID], [Lager-mtrack].[GSM no],
[Lager-mtrack].[Serie nr], [Lager-mtrack].[Fakturert],
[Lager-mtrack].[Provisjonsberettiget], [mtrack-abonnement].[Fra-dato],
[mtrack-abonnement].[Til-dato], [mtrack-abonnement].[Belastet_til],
[mtrack-abonnement].[Prov-ber-til], [Kundedatabase].[Firma] FROM
(Kundedatabase INNER JOIN [mtrack-abonnement] ON Kundedatabase.[Firma
ID]=[mtrack-abonnement].FirmaID) INNER JOIN [Lager-mtrack] ON
[mtrack-abonnement].[GSM-nummer]=[Lager-mtrack].[GSM no];

It gets rather long, and the wording is in Norwegian, sorry for that.

And since I point directly to each table (there are relationships between
them) I don't see why updating is a problem.

Regards
PetterQ

Presumably you created a query that uses 3 tables, and used this as the
source for your form.

Unless you know what you are doing, this is generally not the way to go:
when you create a new record, which table do you expect the new record to
be
created in? There are cases where you would add a lookup table or two to
the
query, but if you are going 3 levels deep, you probably need to redesign
your interface. Typically you would use a form and a subform.

For specific details on why a query is not updatable, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi.
I have a form that shows fields from three different tables in a
tabular
format.

I am not able to update the tables from this form, how can i achieve
this?

Thanks for all help.