Repost: unable to delete a record from subform

A

Amit

MS Access, Windows XP
==================
Hi,

I'd posted this yesterday, and am reposting it in case it slipped through
the cracks :)

I have a form with a subform. Depending on how this form is opened, I'm able
to delete records in the subform in one instance, but when I try to delete a
record in the second instance, I get a message in the status bar at the
bottom "Records cannot be deleted. Data is read-only."

I'm totally confused by this, and would love an explanation/solution.

In the second instance when I'm unable to delete the record from the
subform, I am able to delete the same record from the query that is the
record source for the subform, or from the table that the query is based on.
There is only one table for that query.

In the first instance (when I am able to delete the record), the form is
opened by clicking on a "Add New Committee" button in another form. I add the
name of the committee (which is a required field) and then go to the subform
to select the names of people who are members of the committee. The Primary
key for this table (the subform) is (CommitteeID + MemberID).

When I select the members of the newly added committee, the records show up
in the table. I can delete a record from the subform with no problem.

In the second instance (when I'm unable to delete the record), the same form
is opened by double-clicking on the name of the committee in another form.
Now, when I try to delete a record, I get the message "Records cannot be
deleted. Data is read-only.".

It's the same form being opened, and none of the properties of the form or
the subform are changed. The Allow Additions, Allow Edits and Allow Deletions
are set to True. I even checked the value of these properties by using Msgbox
to output them and in both instances, they are True.

There is no other code in any of the events of the form that would cause
this to happen. The query remains the same in both instances.

Oh, another thing is that in the second instance, I'm unable to add new
members to an existing committee.

If you would like more information, please let me know. At this point, I've
given up on brainstorming a solution, and maybe a fresh set of eyes (and
brain) will help.

The other way would be to create a new form (for the second instance) with 2
buttons: "Add member" and "Delete member" with SQL code behind them to
add/delete a member programatically.

Thanks for any help.

-Amit
 
D

Dirk Goldgar

Amit said:
MS Access, Windows XP
==================
Hi,

I'd posted this yesterday, and am reposting it in case it slipped
through the cracks :)

I have a form with a subform. Depending on how this form is opened,
I'm able to delete records in the subform in one instance, but when I
try to delete a record in the second instance, I get a message in the
status bar at the bottom "Records cannot be deleted. Data is
read-only."

I'm totally confused by this, and would love an explanation/solution.

In the second instance when I'm unable to delete the record from the
subform, I am able to delete the same record from the query that is
the record source for the subform, or from the table that the query
is based on. There is only one table for that query.

In the first instance (when I am able to delete the record), the form
is opened by clicking on a "Add New Committee" button in another
form. I add the name of the committee (which is a required field) and
then go to the subform to select the names of people who are members
of the committee. The Primary key for this table (the subform) is
(CommitteeID + MemberID).

When I select the members of the newly added committee, the records
show up in the table. I can delete a record from the subform with no
problem.

In the second instance (when I'm unable to delete the record), the
same form is opened by double-clicking on the name of the committee
in another form. Now, when I try to delete a record, I get the
message "Records cannot be deleted. Data is read-only.".

It's the same form being opened, and none of the properties of the
form or the subform are changed. The Allow Additions, Allow Edits and
Allow Deletions are set to True. I even checked the value of these
properties by using Msgbox to output them and in both instances, they
are True.

There is no other code in any of the events of the form that would
cause this to happen. The query remains the same in both instances.

Oh, another thing is that in the second instance, I'm unable to add
new members to an existing committee.

If you would like more information, please let me know. At this
point, I've given up on brainstorming a solution, and maybe a fresh
set of eyes (and brain) will help.

The other way would be to create a new form (for the second instance)
with 2 buttons: "Add member" and "Delete member" with SQL code behind
them to add/delete a member programatically.

Thanks for any help.

-Amit

In the second instance, the main form is opened by code in the DblClick
event of a text box on another form. Maybe that code is opening the
form in read-only mode.
 
A

Amit

MS Access > 2000 <
(I missed that in my original post)

:

In the second instance, the main form is opened by code in the DblClick
event of a text box on another form. Maybe that code is opening the
form in read-only mode.

Hi Dirk,

Thanks for your response. No, I'm opening the form in edit mode when I
double-click the name in the first form. Here's the code:
==========================================
Dim stDocName As String
Dim stLinkCriteria As String
Dim stWhere As String

stDocName = "frmCommittee"
stWhere = "[CommitteeID] = " & Me![CommitteeID]

DoCmd.OpenForm stDocName, , , stWhere, acFormEdit
===========================================

I've also tried "DoCmd.OpenForm stDocName, , , stWhere" with the same result.

The other code in the "committee" form is:
===========================================
Current
--------
If Me.NewRecord And Not IsNull(CurrentUser()) Then
Me.CommitteeRecordCreateUser = CurrentUser()
End If

Load
-----
If OpenArgs <> "" Then
ProgramID = OpenArgs
Else
Me.NavigationButtons = False
End If
=============================================

-Amit
 
A

Amit

Dirk Goldgar said:
In the second instance, the main form is opened by code in the DblClick
event of a text box on another form. Maybe that code is opening the
form in read-only mode.

Hi Dirk,

I changed the Recordset Type of the subform to "Dynaset (Inconsistent
Updates)" from "Dynaset", and now it allows me to add/delete members in both
instances.

This is a little weird, because from what I read here-
http://support.microsoft.com/?kbid=328828
-this happens when the query is based on more than one table, and there is
many-to-one-to-many relationship, which doesn't seem to be the case here. The
query for the subform is based on a single table. (But, there is a one to
many relationship between the table(query) that is the record source for the
main form, and the table(query) that is the record source for the subform on
it.

Maybe I'm missing something obvious here and you can explain what's going on
here.

Also, will changing the form property to Dynaset (Inconsistent Updates) have
any other "side effects"?

Thanks.

-Amit
 
D

Dirk Goldgar

Amit said:
Hi Dirk,

I changed the Recordset Type of the subform to "Dynaset (Inconsistent
Updates)" from "Dynaset", and now it allows me to add/delete members
in both instances.

This is a little weird, because from what I read here-
http://support.microsoft.com/?kbid=328828
-this happens when the query is based on more than one table, and
there is many-to-one-to-many relationship, which doesn't seem to be
the case here. The query for the subform is based on a single table.
(But, there is a one to many relationship between the table(query)
that is the record source for the main form, and the table(query)
that is the record source for the subform on it.

Maybe I'm missing something obvious here and you can explain what's
going on here.

No, I'm puzzled. I can't reproduce your problem using my own
form/subform in Access 2000.
Also, will changing the form property to Dynaset (Inconsistent
Updates) have any other "side effects"?

I don't think so, but I don't really understand what's going on. If
you'd like to send me a cut-down copy of your database, containing only
the elements necessary to demonstrate the problem, compacted and then
zipped to less than 1MB in size (preferably much smaller) -- I'll have
a look at it, time permitting. You can send it to the address derived
by removing NO SPAM from the reply address of this message. If that
address isn't visible to you, you can get it from my web site, which is
listed in my sig. Do *not* post my real address in the newsgroup -- I
don't want to be buried in spam and viruses.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top