[AccessD] Delete Problem

I think that is better anyway. I try to limit all possible actions that way as a matter of course.

Regards, Dean


On Oct 3, 2011, at 1:21 PM, Rocky Smolin wrote:

> 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com