Query is not updateable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Fenton - 11 Jun 2007 17:44 GMT

We have a query using 3 tables, tClients, tAppointments, and
tClientImprovements. tClients is basic client information, name,
address, date of sale, etc. tAppointments, is all the appointments
for the particular client and tImprovement is the home improvement
that the client is getting. The tables are linked by a unique ID,
tClients being one-to-many to each of the other tables.

This is the SQL:

SELECT DISTINCT tClients.*
FROM (tClients INNER JOIN tAppointments ON tClients.ID =
tAppointments.ID) INNER JOIN tClientImprovements ON tClients.ID =
tClientImprovements.ID
WHERE
(((tClientImprovements.Improvement)=[forms]![frmGetDatesImprovement]![cboImprovement])
AND ((tAppointments.Date) Between
[forms]![frmGetDatesImprovement]![txtStart] And
[forms]![frmGetDatesImprovement]![txtEnd]))
ORDER BY tClients.LName;

We use a form to enter a date range and a single improvement and get
all the records for every client who had an appointment in the date
range for that particular improvement.

This is the code that runs after updating the improvement field:

DoCmd.OpenForm "Browse Client List"

Forms![Browse Client List].RecordSource =_
"qBrowseByImprovementDates"

Forms![Browse Client List].Caption = "Viewing Appointments for " &_
Forms!frmGetDatesImprovement.cboImprovement & " between " &_
Forms!frmGetDatesImprovement.txtStart & " and " &_
Forms!frmGetDatesImprovement.txtEnd

We get all the right records, but can't update anything. What do I
have to change so that we can update the client records?

Paul Fenton

Reply to this Message

fredg - 11 Jun 2007 18:49 GMT

> We have a query using 3 tables, tClients, tAppointments, and
> tClientImprovements. tClients is basic client information, name,
[quoted text clipped - 36 lines]
>
> Paul Fenton

Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Ken Sheridan - 11 Jun 2007 18:58 GMT

As you are returning columns from the tClients table only you could use a
subquery to restrict the rows returned and thus avoid having to use the
DISTINCT option:

PARAMETERS
Forms!frmGetDatesImprovement!txtStart DATETIME,
Forms!frmGetDatesImprovement!txtEnd DATETIME;
SELECT *
FROM tClients
WHERE EXISTS
(SELECT *
FROM tAppointments INNER JOIN tClientImprovements
ON tAppointments.ID = tClientImprovements .ID
WHERE tAppointments.ID = tClients.ID
AND tClientImprovements.Improvement =
Forms!frmGetDatesImprovement!cboImprovement
AND tAppointments.Date BETWEEN
Forms!frmGetDatesImprovement!txtStart
AND Forms!frmGetDatesImprovement!txtEnd)
ORDER BY LNname;

Note that its prudent to declare parameters where the data type is DateTime
as otherwise a date parameter value entered in short date format could be
interpreted as an arithmetic expression and give the wrong results.

Ken Sheridan
Stafford, England

> We have a query using 3 tables, tClients, tAppointments, and
> tClientImprovements. tClients is basic client information, name,
[quoted text clipped - 38 lines]
>
> .

Reply to this Message

Paul Fenton - 12 Jun 2007 00:39 GMT

Thank you Ken. I tried the SQL below and I got a syntax error.
"Syntax error in query expression 'EXISTS (SELECT... etc. And where
do I put the PARAMETERS statements?

Paul Fenton

>As you are returning columns from the tClients table only you could use a
>subquery to restrict the rows returned and thus avoid having to use the
[quoted text clipped - 66 lines]
>>
>> .

Reply to this Message

Ken Sheridan - 12 Jun 2007 10:42 GMT

Paul:

Mea culpa. There was an unwanted space after one of the dot operators; it
should have read:

PARAMETERS
Forms!frmGetDatesImprovement!txtStart DATETIME,
Forms!frmGetDatesImprovement!txtEnd DATETIME;
SELECT *
FROM tClients
WHERE EXISTS
(SELECT *
FROM tAppointments INNER JOIN tClientImprovements
ON tAppointments.ID = tClientImprovements.ID
WHERE tAppointments.ID = tClients.ID
AND tClientImprovements.Improvement =
Forms!frmGetDatesImprovement!cboImprovement
AND tAppointments.Date BETWEEN
Forms!frmGetDatesImprovement!txtStart
AND Forms!frmGetDatesImprovement!txtEnd)
ORDER BY LNname;

The Parameters clause goes immediately before the SELECT statement and is
terminated by a semi-colon. If you paste all of the above SQL into the query
in SQL view it should work. You can also create the Parameters clause in
design view by selecting Parameters from the Query menu item.

BTW, I notice that you've used Date as a column name. This is best avoided
as it could be confused with the built in Date function. A more specific
term like AppointmentDate is better.

Ken Sheridan
Stafford, England

> Thank you Ken. I tried the SQL below and I got a syntax error.
> "Syntax error in query expression 'EXISTS (SELECT... etc. And where
[quoted text clipped - 72 lines]
> >>
> >> .

Reply to this Message

Paul Fenton - 12 Jun 2007 16:42 GMT

Ken, thank you so much. This works perfectly.

Yes, I know about that Date column. I started this app 12 years ago
when I was just learning Access and didn't know about such things. I
keep telling myself I need to get in there and change it but just
haven't had the guts to tackle it yet.

Again, thanks for the help.

Paul Fenton

>Paul:
>
[quoted text clipped - 106 lines]
>> >>
>> >> .

Reply to this Message