Rocky Smolin
rockysmolin at bchacc.com
Mon Oct 3 12:10:44 CDT 2011
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