[AccessD] Delete Problem

Rocky Smolin rockysmolin at bchacc.com
Mon Oct 3 12:21:32 CDT 2011


OK, I worked around the problem by putting a delete button on the main form
and using:

Private Sub cmdDelete_Click()

    intReply = MsgBox("Are you certain you want to delete this Receivable
record?", vbYesNo)
    If intReply = vbNo Then Exit Sub

    Set db = CurrentDb
    db.Execute "Delete * FROM tblAccountsReceivable WHERE fldARID = " &
Me.subfrmAccountsReceivable!fldARID
    db.Close
    Me.subfrmAccountsReceivable.Requery
    
End Sub 

And setting AllowDeletions on the subform to false

But it's still a mystery.


Rocky

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Monday, October 03, 2011 10:11 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Delete Problem

Dean:

Actually, the main form has no record source.  It just holds the subform
(among other things.).  SO I am initiating the delete from the subform.  So
the accounts table is only in the record source of the subform.  

Regards, Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dean
Sent: Monday, October 03, 2011 9:59 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Delete Problem

well, pardon me for jumping to conclusions. on further inspections it is
more like what I suggested than what I thought I saw.

I assume you parent form has the accounts table PK. 
Are you sure you are initiating delete from the subform? You may be calling
delete on the main form. I would trap that and disallow any uncontrolled
processes by running all actions through the CBF. This way you provide your
own, more meaningful warning message, verify and run an action query of your
own.

Regards, Dean


On Oct 3, 2011, at 12:45 PM, Rocky Smolin wrote:

> Dean:
> 
> Looks to me like it's a table of Accounts Receivable - the only field 
> from tblAccounts I need is the account Name. No? fldName is the only 
> field from tblAccounts in the query.
> 
> Is there a way to rewrite the query so that it wouldn't delete the 
> Account only the Receivable record?
> 
> TIA
> 
> Rocky
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dean
> Sent: Monday, October 03, 2011 9:34 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Delete Problem
> 
> I think you may have answered your own question there. Your query is 
> essentially a table of accounts, with the receivables pulled in. When 
> you select a record to delete, you are selecting the account. I would 
> rework that query, use the FK to to pull the account in as a lookup
instead.
> 
> Regards, Dean
> 
> 
> On Oct 3, 2011, at 12:25 PM, Rocky Smolin wrote:
> 
>> Dear List:
>> 
>> I have a subform which has as its record source a query which joins 
>> an Account Receivable table to an account table:
>> 
>> SELECT tblAccountsReceivable.fldARID, 
>> tblAccountsReceivable.fldAccountNumber, (*** bunch of other
>> fields***) FROM tblAccountsReceivable LEFT JOIN tblAccounts ON 
>> tblAccountsReceivable.fldAccountNumber = tblAccounts.fldAccountNumber 
>> ORDER BY tblAccountsReceivable.fldARID;
>> 
>> where Account Number is the PK of Accounts and the FK in
> AccountsReceivable.
>> 
>> The subform is a continuous form showing the receivable records.  
>> When I select a record and hit the delete key the receivable record 
>> is deleted but also the Account from tblAccounts!
>> 
>> I get the "You are about to delete..." warning message.  
>> 
>> No cascade delete. 
>> 
>> I can't understand why it would delete the account.  The relationship 
>> in the query is one-to-many: one Account, many Accounts Receivable
> records.
>> 
>> Any clues?  I'm stumped.
>> 
>> MTIA
>> 
>> Rocky
>> 
>> 
>> 
>> 
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list