Unable to delete subform records

Hi all,

I have a main form which inside contains a sub-form A and inside this subform
A there is a subform B.
Subform A is a datasheet view while subformB is a form.
After I delete the records in subform B, the records are gone. But once I
close and open the main form again, the records still appears in the subform
B.

Anyone able to help me?

Syphonics wrote:
Hi all,

I have a main form which inside contains a sub-form A and inside this subform
A there is a subform B.
Subform A is a datasheet view while subformB is a form.
After I delete the records in subform B, the records are gone. But once I
close and open the main form again, the records still appears in the subform
B.
I couldn't delete the records in subform B using the main form. But I can do
it when I open only the subform A and delete the records of subform B.
Please do help me in this. I am stuck with this for days.

Thank you.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200912/1

On Wed, 23 Dec 2009 12:58:58 GMT, "Syphonics via AccessMonster.com"
u25414@uwe wrote:

Hi all,

I have a main form which inside contains a sub-form A and inside this subform
A there is a subform B.
Subform A is a datasheet view while subformB is a form.
After I delete the records in subform B, the records are gone. But once I
close and open the main form again, the records still appears in the subform
B.
I couldn't delete the records in subform B using the main form. But I can do
it when I open only the subform A and delete the records of subform B.
Please do help me in this. I am stuck with this for days.

Thank you.

I'm confused. In my experience you can't HAVE a subform within a Datasheet
subform! Are you using Subdatasheets, with the + icon at the left of the
datasheet, or what?

What are the Recordsource properties of A and B? Could you post the SQL? How
are you deleting the records?
--

John W. Vinson [MVP]

Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
+icon, it will open the sub form B, which is a form view.

In my main form, there is a combo box which allows me to choose the customer.
Upon choosing the customer, it will filtered and shows the list of orders of
the customer in subform A.
Subform A record source is from my "Order Query".
On clicking on the +icon of subfrom A, it will open subform B and show the
Account Receivables details. Subform B record source is from my table
"Account Receivables".

I left click on the record of the subform B and did a record cut. After
closing the main form and reopening it, the record which i have just deleted
appears once again. I couldn't delete the record unless I do a manual delete
on my table "Account Receivables".

SQL
Subfrom A:
SELECT Order.*
FROM [Order];

Subform B:
SELECT
FROM [Account Receivables];



John W. Vinson wrote:
Hi all,

[quoted text clipped - 9 lines]

Thank you.

I'm confused. In my experience you can't HAVE a subform within a Datasheet
subform! Are you using Subdatasheets, with the + icon at the left of the
datasheet, or what?

What are the Recordsource properties of A and B? Could you post the SQL? How
are you deleting the records?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200912/1

On Mon, 28 Dec 2009 01:26:40 GMT, "Syphonics via AccessMonster.com"
u25414@uwe wrote:

Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
+icon, it will open the sub form B, which is a form view.

In my main form, there is a combo box which allows me to choose the customer.
Upon choosing the customer, it will filtered and shows the list of orders of
the customer in subform A.
Subform A record source is from my "Order Query".
On clicking on the +icon of subfrom A, it will open subform B and show the
Account Receivables details. Subform B record source is from my table
"Account Receivables".

I left click on the record of the subform B and did a record cut. After
closing the main form and reopening it, the record which i have just deleted
appears once again. I couldn't delete the record unless I do a manual delete
on my table "Account Receivables".

SQL
Subfrom A:
SELECT Order.*
FROM [Order];

Subform B:
SELECT
FROM [Account Receivables];

How are your tables related? Do you have Referential Integrity enforced on the
relationship? What about Cascade Deletes? What's the Master and Child Link
Field of B?
--

John W. Vinson [MVP]

John W. Vinson wrote:
Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
[quoted text clipped - 21 lines]
SELECT
FROM [Account Receivables];

How are your tables related? Do you have Referential Integrity enforced on the
relationship? What about Cascade Deletes? What's the Master and Child Link
Field of B?

There is a field name AcountID in the "Account Recievables" table which is
link to the Order ID in the "Order Table" both are primary key. I have tried
with and without "Referential Integrity and Cascade Deletes" but it still
never work.
Subform A is link with master and child link using Customer ID, for filtering
of the customer.
Subform B is link with master link: OrderID of the "Order Query" and the
child link is the AccountID of the "Account Recieveable Table"

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200912/1

On Mon, 28 Dec 2009 03:46:22 GMT, "Syphonics via AccessMonster.com"
u25414@uwe wrote:

John W. Vinson wrote:
Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
[quoted text clipped - 21 lines]
SELECT
FROM [Account Receivables];

How are your tables related? Do you have Referential Integrity enforced on the
relationship? What about Cascade Deletes? What's the Master and Child Link
Field of B?

There is a field name AcountID in the "Account Recievables" table which is
link to the Order ID in the "Order Table" both are primary key. I have tried
with and without "Referential Integrity and Cascade Deletes" but it still
never work.
Subform A is link with master and child link using Customer ID, for filtering
of the customer.
Subform B is link with master link: OrderID of the "Order Query" and the
child link is the AccountID of the "Account Recieveable Table"

I'm wondering about your data structure. Does each order have one and only one
Account Recievable record? That would imply a one to one relationship: if
that's the case, why have two tables? What's the direction of the relationship
(even one to one relationships have a parent and a child table)?
--

John W. Vinson [MVP]

Each order may have more than 1 account recievable records.
I have just realised that I am able to delete the records if I open the
Subform A with Subform B in it.
But if I open the main form with SubformA and inside of SubformA is SubformB,
I am not able to delete the record.


John W. Vinson wrote:
Hi John,
My subform A is a datasheet with a + icon at the left.Upon clicking on the
[quoted text clipped - 14 lines]
Subform B is link with master link: OrderID of the "Order Query" and the
child link is the AccountID of the "Account Recieveable Table"

I'm wondering about your data structure. Does each order have one and only one
Account Recievable record? That would imply a one to one relationship: if
that's the case, why have two tables? What's the direction of the relationship
(even one to one relationships have a parent and a child table)?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200912/1

On Mon, 28 Dec 2009 06:58:37 GMT, "Syphonics via AccessMonster.com"
u25414@uwe wrote:

Each order may have more than 1 account recievable records.
I have just realised that I am able to delete the records if I open the
Subform A with Subform B in it.
But if I open the main form with SubformA and inside of SubformA is SubformB,
I am not able to delete the record.

That would appear to be a problem with A2007 and subdatasheets - neither of
which I use routinely! Consider using a true Subform rather than a datasheet.
--

John W. Vinson [MVP]

John W. Vinson wrote in
:

In my experience you can't HAVE a subform within a Datasheet
subform!

Open a form with a child form and then change the view to Datasheet
view. You'll see that you get a datasheet with the child form
embedded like a subdatasheet. In this way, you can actually have a
child form with child forms, including continuous forms.

I've been harping on how useful this is for a really long time, but
nobody seems to notice that it's possible!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/