Updating Fields On A Datasheet Form

[Guest]

I have a database containing Main records (ID, Name, Org., etc) and Activity
records (ID, date, activity code, arrival time, notes) in a one-to-many
relationship on ID. If I create a datasheet form containing ONLY Activity
records, I can update the Notes field when it is displayed. If I add ANY
field from the Main table to the query so user can alphabetically identity
who owns the Activity record, then the Notes field is no longer updatable.
In fact, Notes is protected, preventing any changes.

I want to be able to update the Notes field from a datasheet view when there
is an alpha-name from the Main table identifying the owner of each Activity
record. Is this possible? Can I code it in an event procedure? If so,
which event?

Earl Phillips
Volunteer Ex-mainframer Learning Access
To Help The Local Food Bank Efficiency

 

[John Spencer]

Do you have a primary key in the Activity table? Your posting did not
show that it did.

You really need to include a primary key from both tables in the query.
Otherwise, you are probably going to have a query that is not updatable.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

The Activity table only has a foreign key matching the main table. It does
not have it's own key. Should I try creating one? I would have to make it
ID+ScheduleDate to make it unique. Will the ID still act as a foreign key to
the main table?

Earl Phillips
Ex-mainframer Learning Access
To Make Food Bank Efficient

 

[John Spencer]

Yes, you can use a two-field primary key based on those two values. And yes
ID can still be the foreign key with no problems.

Or you can be lazy and just add an autonumber field and make it the primary
key.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I found duplicate entries for some ID+ScheduleDate combinations, so I added
an autonumber RecordID to the Activities table (without duplicates). I then
created a similar query to the one giving me trouble. I included the
RecordID and ID from the Activities table and the ID from the Main table and
other needed fields in the design view. When I showed the datasheet view, it
still would not let me change the Notes field. When I again redisplayed the
design view, the Activity.RecordID, Activity.ID and Main.ID were missing
(apparently because I had indicated I didn't want them displayed). I tried
numerous times to add them back, but whenever I showed the datasheet view and
returned to the design view, these keys were missing.

I changed all keys to be visible and they remained on the design view and
the datasheet view, but still the Notes field could not be changed.

Does this mean any attempts are futile? The SQL code selects the correct
rows but nothing can be altered.

 

[John Spencer]

Did you make the autonumber the primary key? That is a separate operation.

Do you have a primary key on the Main table?

If so, post the SQL of your query. (Select View: SQL from the menu)

Queries can be not updatable for a variety of reasons and usually you can
see the reason when the SQL is posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Adding the primary key symbol to each table made the difference. Now I have
to see if this effects any other process in the system.

Each day I learn more. Thank you very much for your help. More hungry
people will be fed with fewer resources because of the help you have been to
me over the last year.

Earl Phillips
Volunteer Ex-mainframer Learning Access
To Help The Local Food Bank