Configuration: Access 2002 and SQL Server 2000 using a .ADP Project.
I would VERY MUCH appreciate anyone's input into this problem I'm having.
I have a form in Access that does not permit the user to add/change/delete any data, apparently as a result of adding a GROUP BY clause to a View used indirectly as the form's Record Source.
I really don't believe that this restriction needs to be there, and I'm hoping that someone can suggest ANY solution or workaround.
To be specific -- when the user tries to type into a field on the form, Access beeps and displays "This Recordset is not updatable" in the bottom status bar.
I have 3 tables, subjects, studies, and study. These are not ideally named, as evident by their descriptions:
"subjects" is a person table -- one record per person
"study" is a table of studies (events) -- each record describes one study.
"studies" is a 2-column table linking subjects records to study records (many-to-many)
Table: subjects -- one record per person
---------------
subjectId uniqueidentifie r 16 <- PRIMARY KEY
lastName nvarchar 50
firstName nvarchar 30
middleName nvarchar 30
dob datetime 8
...
etc (about 80 fields total)
Table: study -- one record per study
------------
studyNumber nchar 8 <- PRIMARY KEY
startDate datetime 8
endDate datetime 8
Table: studies -- link subjects records to study records (many-to-many)
--------------
subjectId uniqueidentifie r 16
studyNumber nchar 8
In Access, I have a form ("Single Form" - one record displayed at a time) which primarily allows viewing/editing of the subjects table.
Initially, I had the Record Source of the form to be the "subjects" table, and all was fine -- I could edit the records in the form.
I subsequently added the ability to display-only the subject's age on the form.
I did this by creating a View ("viewSubjectsM ain") which simply added a new (calculated) column to those from 'subjects', changing the form's Record Source to "viewSubjectsMa in", and setting the form's Unique Table property to "subjects". I used this View approach so that the end-user can filter/sort on this age field in the form. This also worked fine.
Here's that view code for posterity ("dob" is date-of-birth):
The problem appears now as I'm trying to implement adding 2 new display-only fields to the form: NumberOfStudies and LastStudyDate. Since I want the end-users to be able to filter/sort on these fields as well, I want to create them as columns in a View.
I did this by creating a second View, and modifying my first view to reference it as follows:
Why have one view reference a second view? I'm far from an expert at these things, and my trial-and-error approach took me to this version. It initially made it two views to avoid having to name all 80 subjects columns in the GROUP BY clause, but it's very possible it's not the best or most efficient approach.
Perhaps someone knows some VBA way of setting some flag(s) in Access to allow editing of the form, or maybe how to rewrite the SQL so that it doesn't need the GROUP BY (if that's the only problem) -- maybe with a user function or two, or maybe someone knows how to implement what Microsoft is alluding to in the second-last paragraph of this page:
ANY and ALL input about this is welcome and appreciated. Thank you in advance.
-radio1
I would VERY MUCH appreciate anyone's input into this problem I'm having.
I have a form in Access that does not permit the user to add/change/delete any data, apparently as a result of adding a GROUP BY clause to a View used indirectly as the form's Record Source.
I really don't believe that this restriction needs to be there, and I'm hoping that someone can suggest ANY solution or workaround.
To be specific -- when the user tries to type into a field on the form, Access beeps and displays "This Recordset is not updatable" in the bottom status bar.
I have 3 tables, subjects, studies, and study. These are not ideally named, as evident by their descriptions:
"subjects" is a person table -- one record per person
"study" is a table of studies (events) -- each record describes one study.
"studies" is a 2-column table linking subjects records to study records (many-to-many)
Table: subjects -- one record per person
---------------
subjectId uniqueidentifie r 16 <- PRIMARY KEY
lastName nvarchar 50
firstName nvarchar 30
middleName nvarchar 30
dob datetime 8
...
etc (about 80 fields total)
Table: study -- one record per study
------------
studyNumber nchar 8 <- PRIMARY KEY
startDate datetime 8
endDate datetime 8
Table: studies -- link subjects records to study records (many-to-many)
--------------
subjectId uniqueidentifie r 16
studyNumber nchar 8
In Access, I have a form ("Single Form" - one record displayed at a time) which primarily allows viewing/editing of the subjects table.
Initially, I had the Record Source of the form to be the "subjects" table, and all was fine -- I could edit the records in the form.
I subsequently added the ability to display-only the subject's age on the form.
I did this by creating a View ("viewSubjectsM ain") which simply added a new (calculated) column to those from 'subjects', changing the form's Record Source to "viewSubjectsMa in", and setting the form's Unique Table property to "subjects". I used this View approach so that the end-user can filter/sort on this age field in the form. This also worked fine.
Here's that view code for posterity ("dob" is date-of-birth):
Code:
CREATE VIEW dbo.viewSubjectsMain
AS
SELECT dbo.subjects.*, CASE
WHEN dateadd(year, datediff(year, dob, GetDate()), dob) > GetDate()
THEN datediff(year, dob, GetDate()) - 1
ELSE datediff(year, dob, GetDate())
END
AS age
FROM dbo.subjects
I did this by creating a second View, and modifying my first view to reference it as follows:
Code:
CREATE VIEW dbo.viewSubjectsMain
AS
SELECT dbo.subjects.*, CASE
WHEN dateadd(year, datediff(year, dob, GetDate()), dob) > GetDate()
THEN datediff(year, dob, GetDate()) - 1
ELSE datediff(year, dob, GetDate())
END
AS age, numStudies, lastStudyDate
FROM dbo.subjects
LEFT OUTER JOIN viewSubjectsStudySumm
on viewSubjectsStudySumm.subjectId = subjects.subjectId
--------------------------
CREATE VIEW viewSubjectsStudySumm
AS
SELECT studies.subjectId,
COUNT(*) AS numStudies,
MAX(study.endDate) AS lastStudyDate
FROM studies
JOIN study ON studies.studyNumber=study.studyNumber
GROUP BY studies.subjectId
Perhaps someone knows some VBA way of setting some flag(s) in Access to allow editing of the form, or maybe how to rewrite the SQL so that it doesn't need the GROUP BY (if that's the only problem) -- maybe with a user function or two, or maybe someone knows how to implement what Microsoft is alluding to in the second-last paragraph of this page:
ANY and ALL input about this is welcome and appreciated. Thank you in advance.
-radio1
Comment