Unable to edit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Within Access 2003 I have created a form based on a query which has 3 tables
and I am unable to edit this form.
What I need to do is allocate an Agent to a certain job but it will not let
me do this yet the field is not locked.

Any ideas as to how i could get around this problem?
Thanks
 
Hi -

It's because the query behind the form is not editable. If you open the
query from the database window, you will see the message "Recordset
cannot be updated" (or something like it). Whether or not you can make
the query updatable depends on how the query is structured. If the
query uses "totals", i.e. group by, you probably can't.

Can you post the SQL of the query?

John
 
Ignore the response by "Kevin". It is spam. Your query may not be
updateable. If you open the query directly, can you edit its contents?


Within Access 2003 I have created a form based on a query which has 3 tables
and I am unable to edit this form.
What I need to do is allocate an Agent to a certain job but it will not let
me do this yet the field is not locked.

Any ideas as to how i could get around this problem?
Thanks
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Thanks for the note about Kevin,

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Invoiced?], [All T&M Data].[Agent ID] AS
[Assigned To], [All T&M Data].[Serial ID], [All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP, IIf(IsNull(PO.[Serial
ID]),'N','Y') AS PO
FROM ([All T&M Data] LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial
ID]) LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

Many Thanks,
Matt
 
Hi -

Are IP.[SERIAL ID] and PO.[SERIAL ID] set as the keys of their
respective tables? If not, try setting them.

John


Matt said:
Thanks for the note about Kevin,

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Invoiced?], [All T&M Data].[Agent ID] AS
[Assigned To], [All T&M Data].[Serial ID], [All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP, IIf(IsNull(PO.[Serial
ID]),'N','Y') AS PO
FROM ([All T&M Data] LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial
ID]) LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

Many Thanks,
Matt

:

Ignore the response by "Kevin". It is spam. Your query may not be
updateable. If you open the query directly, can you edit its contents?




Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...
Click to expand...
 
Hi,

They aren't primary keys as one Serial ID can have several problems, and are
all recorded within this one report.

J. Goddard said:
Hi -

Are IP.[SERIAL ID] and PO.[SERIAL ID] set as the keys of their
respective tables? If not, try setting them.

John


Matt said:
Thanks for the note about Kevin,

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Invoiced?], [All T&M Data].[Agent ID] AS
[Assigned To], [All T&M Data].[Serial ID], [All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP, IIf(IsNull(PO.[Serial
ID]),'N','Y') AS PO
FROM ([All T&M Data] LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial
ID]) LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

Many Thanks,
Matt

:

Ignore the response by "Kevin". It is spam. Your query may not be
updateable. If you open the query directly, can you edit its contents?


On Tue, 19 Sep 2006 06:22:02 -0700, Matt Dawson


Within Access 2003 I have created a form based on a query which has 3 tables
and I am unable to edit this form.
What I need to do is allocate an Agent to a certain job but it will not let
me do this yet the field is not locked.

Any ideas as to how i could get around this problem?
Thanks

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...
Click to expand...
Click to expand...
 
Try just putting indexes (Duplicates OK) on the two [Serial_ID] fields.
In an experiment I just tried (linking three tables), that was the
solution.

John


Matt said:
Hi,

They aren't primary keys as one Serial ID can have several problems, and are
all recorded within this one report.

:

Hi -

Are IP.[SERIAL ID] and PO.[SERIAL ID] set as the keys of their
respective tables? If not, try setting them.

John


Matt Dawson wrote:

Thanks for the note about Kevin,

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Invoiced?], [All T&M Data].[Agent ID] AS
[Assigned To], [All T&M Data].[Serial ID], [All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP, IIf(IsNull(PO.[Serial
ID]),'N','Y') AS PO
FROM ([All T&M Data] LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial
ID]) LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

Many Thanks,
Matt

:



Ignore the response by "Kevin". It is spam. Your query may not be
updateable. If you open the query directly, can you edit its contents?


On Tue, 19 Sep 2006 06:22:02 -0700, Matt Dawson



Within Access 2003 I have created a form based on a query which has 3 tables
and I am unable to edit this form.
What I need to do is allocate an Agent to a certain job but it will not let
me do this yet the field is not locked.

Any ideas as to how i could get around this problem?
Thanks

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...
Click to expand...
Click to expand...
 
I am not a query guru. Where many people go to complex Queries, I tend
to go to VBA Functions manipulating output from simpler Queries.
However, the Help documentation says this (among other things), in the
'data can't be updated' dropdown on the 'When can I update data from a
Query (MDB)' page:
(Situation) Query based on three or more tables in which there is a
many-to-one-to-many relationship
(Solution) Though you can't update the data in the query directly, you
can update the data in a form or data access page (data access page: A
Web page, published from Access, that has a connection to a database.
In a data access page, you can view, add to, edit, and manipulate the
data stored in the database. A page can also include data from other
sources, such as Excel.) based on the query if the form's
RecordsetType property is set to Dynaset (Inconsistent Updates).

I hope that this helps.

Thanks for the note about Kevin,

This is the SQL behind the query and even if I just open the query as it is
i can't edit anything and I get the message 'this recordset is not updatable'
at the bottom upon opening:

SELECT [All T&M Data].[Request ID], [All T&M Data].[First Name], [All T&M
Data].Surname, [All T&M Data].[Invoiced?], [All T&M Data].[Agent ID] AS
[Assigned To], [All T&M Data].[Serial ID], [All T&M Data].Country,
IIf(IsNull(IP.[Serial ID]),'N','Y') AS IP, IIf(IsNull(PO.[Serial
ID]),'N','Y') AS PO
FROM ([All T&M Data] LEFT JOIN IP ON [All T&M Data].[Serial ID] = IP.[Serial
ID]) LEFT JOIN PO ON [All T&M Data].[Serial ID] = PO.[Serial ID]
WHERE (((IIf(IsNull([IP].[Serial ID]),'N','Y'))="Y")) OR
(((IIf(IsNull([PO].[Serial ID]),'N','Y'))="Y"));

Many Thanks,
Matt

Peter R. Fletcher said:
Ignore the response by "Kevin". It is spam. Your query may not be
updateable. If you open the query directly, can you edit its contents?




Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
Click to expand...
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top