Unable to modify fields on a form which uses a query

  • Thread starter Thread starter Jonathan Fisher
  • Start date Start date
J

Jonathan Fisher

Two newbie questions about queries:

(1) Using Access 2000, I'm trying to create a standard timesheet-type
DB, including a field which is equal to the sum of the activity entries
for a given person on a given day. I created a SQL query:

SELECT DISTINCTROW tblActivity.PersonDayKey, Sum(tblActivity.TimeSpent)
AS [Sum Of TimeSpent]
FROM tblActivity
GROUP BY tblActivity.PersonDayKey;

Now, whenever I try to create a form which includes the second field
from that query (ie, the "Sum(....)" field), none of the fields on the
form are modifiable. For example, I create a form based on the table
tblPersonDay (which has a primary key of PersonDayKey), relating to the
above query via PersonDayKey (the join type of the relationship is "only
include rows where the joined fields from both tables are equal", which
I assume is a standard inner-join). I then include some other fields
from tblPersonDay, which would normally be modifiable -- but on this
form they're not.

Can anyone tell me why this should be? (I'm a little hazy on exactly
how Access treats relationships between tables, but I can't see any
reason the fields should not be modifiable.)

(2) One other question -- Can anyone tell me how to change the name of
the database file to which an append-query will do its appending? If I
try reference myQuery.DestinationDB or myQuery.DestConnectStr (as
described in the help reference) I get an error to the effect of
"Property not found in this object".
 
Question one.

Any query that uses an SQL aggregate function is by definition NOT updatable.
Think about it, if you combine (group) several records into one row, how does
the program know which record or records to update. The result returned by the
aggregate query does not know the specific records which were used to create
each row in the aggregate response.

Also, Distinct and Distinct Row will exhibit this same behavior.

Question two.
Can you post the SQL code you are using? Perhaps someone can help you then.
 
Sorry, I guess I should have been a little clearer -- I'm not trying to
modify fields in the query (I'm not quite *that* newbie :-), but other
fields on the form. For example: The form is based on the table
tblPersonDay, which contains columns PersonDayKey and Date. The table
tblPersonDay is related to tblActivity by PersonDayKey (the join type of
the relationship is "only include rows where the joined fields from both
tables are equal", which I assume is a standard inner-join), and the
query below is defined on the relationship. If I create a form with
just the field Date from tblPersonDay, that field is modifiable. If I
take that same form and add [Sum Of TimeSpent] from the query below,
then the Date field (still from tblPersonDay) on the form is no longer
modifiable. I don't get any error-messages; Access just refuses to
allow me to edit the text in the Date field on the form.

The other response to my original posting referred me to KnowledgeBase
article 328828; that article was informative, but the only part of it
that seemed relevant to my current problem is:

"You cannot update data in a form if the form is based on a
stored procedure with more than one table."

Is a SQL query considered a "stored procedure" in this instance? -- if
so, I can see that I might have a problem, but I'm not quite sure why
Access should have that limitation....


As for Question 2, any Visual Basic statement that I try which contains
one of those two references gives me the error-message. For example,

Set foobar = myQuery.DestinationDB

(I've tried that both with and without the "Set", just in case I was
missing something....)

TIA for any help,
Jonathan

John Spencer (MVP) said:
Question one.

Any query that uses an SQL aggregate function is by definition NOT updatable.
Think about it, if you combine (group) several records into one row, how does
the program know which record or records to update. The result returned by
the
aggregate query does not know the specific records which were used to create
each row in the aggregate response.

Also, Distinct and Distinct Row will exhibit this same behavior.

Question two.
Can you post the SQL code you are using? Perhaps someone can help you then.

Jonathan said:
Two newbie questions about queries:

(1) Using Access 2000, I'm trying to create a standard timesheet-type
DB, including a field which is equal to the sum of the activity entries
for a given person on a given day. I created a SQL query:

SELECT DISTINCTROW tblActivity.PersonDayKey, Sum(tblActivity.TimeSpent)
AS [Sum Of TimeSpent]
FROM tblActivity
GROUP BY tblActivity.PersonDayKey;

Now, whenever I try to create a form which includes the second field
from that query (ie, the "Sum(....)" field), none of the fields on the
form are modifiable. For example, I create a form based on the table
tblPersonDay (which has a primary key of PersonDayKey), relating to the
above query via PersonDayKey (the join type of the relationship is "only
include rows where the joined fields from both tables are equal", which
I assume is a standard inner-join). I then include some other fields
from tblPersonDay, which would normally be modifiable -- but on this
form they're not.

Can anyone tell me why this should be? (I'm a little hazy on exactly
how Access treats relationships between tables, but I can't see any
reason the fields should not be modifiable.)

(2) One other question -- Can anyone tell me how to change the name of
the database file to which an append-query will do its appending? If I
try reference myQuery.DestinationDB or myQuery.DestConnectStr (as
described in the help reference) I get an error to the effect of
"Property not found in this object".
Click to expand...
Click to expand...
 
And is the form's record source a query? And does that query contain the Sum
function? It seems like it does from what you have said.

Perhaps you can get around this by using the VBA DSum function.

Jonathan said:
Sorry, I guess I should have been a little clearer -- I'm not trying to
modify fields in the query (I'm not quite *that* newbie :-), but other
fields on the form. For example: The form is based on the table
tblPersonDay, which contains columns PersonDayKey and Date. The table
tblPersonDay is related to tblActivity by PersonDayKey (the join type of
the relationship is "only include rows where the joined fields from both
tables are equal", which I assume is a standard inner-join), and the
query below is defined on the relationship. If I create a form with
just the field Date from tblPersonDay, that field is modifiable. If I
take that same form and add [Sum Of TimeSpent] from the query below,
then the Date field (still from tblPersonDay) on the form is no longer
modifiable. I don't get any error-messages; Access just refuses to
allow me to edit the text in the Date field on the form.

The other response to my original posting referred me to KnowledgeBase
article 328828; that article was informative, but the only part of it
that seemed relevant to my current problem is:

"You cannot update data in a form if the form is based on a
stored procedure with more than one table."

Is a SQL query considered a "stored procedure" in this instance? -- if
so, I can see that I might have a problem, but I'm not quite sure why
Access should have that limitation....

As for Question 2, any Visual Basic statement that I try which contains
one of those two references gives me the error-message. For example,

Set foobar = myQuery.DestinationDB

(I've tried that both with and without the "Set", just in case I was
missing something....)

TIA for any help,
Jonathan

John Spencer (MVP) said:
Question one.

Any query that uses an SQL aggregate function is by definition NOT updatable.
Think about it, if you combine (group) several records into one row, how does
the program know which record or records to update. The result returned by
the
aggregate query does not know the specific records which were used to create
each row in the aggregate response.

Also, Distinct and Distinct Row will exhibit this same behavior.

Question two.
Can you post the SQL code you are using? Perhaps someone can help you then.

Jonathan said:
Two newbie questions about queries:

(1) Using Access 2000, I'm trying to create a standard timesheet-type
DB, including a field which is equal to the sum of the activity entries
for a given person on a given day. I created a SQL query:

SELECT DISTINCTROW tblActivity.PersonDayKey, Sum(tblActivity.TimeSpent)
AS [Sum Of TimeSpent]
FROM tblActivity
GROUP BY tblActivity.PersonDayKey;

Now, whenever I try to create a form which includes the second field
from that query (ie, the "Sum(....)" field), none of the fields on the
form are modifiable. For example, I create a form based on the table
tblPersonDay (which has a primary key of PersonDayKey), relating to the
above query via PersonDayKey (the join type of the relationship is "only
include rows where the joined fields from both tables are equal", which
I assume is a standard inner-join). I then include some other fields
from tblPersonDay, which would normally be modifiable -- but on this
form they're not.

Can anyone tell me why this should be? (I'm a little hazy on exactly
how Access treats relationships between tables, but I can't see any
reason the fields should not be modifiable.)

(2) One other question -- Can anyone tell me how to change the name of
the database file to which an append-query will do its appending? If I
try reference myQuery.DestinationDB or myQuery.DestConnectStr (as
described in the help reference) I get an error to the effect of
"Property not found in this object".
Click to expand...
Click to expand...
Click to expand...
 
Back
Top