Query is not updateable

P

Paul Fenton

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
 
F

fredg

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
Click to expand...

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
 
G

Guest

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
 
P

Paul Fenton

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
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

Paul Fenton said:
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

(((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



.
Click to expand...
Click to expand...
 
G

Guest

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

Paul Fenton said:
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
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

Paul Fenton said:
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

(((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



.
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Fenton

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:

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

Paul Fenton said:
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
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,
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

(((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



.
Click to expand...
Click to expand...
Click to expand...
 
Top