Dean
dnod at aol.com
Mon Oct 3 12:22:32 CDT 2011
If you do not have the PK for the accounts anywhere in the parent or the subform, I don't see how hitting the delete key would do it. You don't have any events running a procedure or macro? I am not a SQL statement expert, but that statement does not seem to me able to cause what you describe. I would double check the relationship settings, you said there are no cascade delete settings but that would surely fit. Check your event procedures on both forms. Is something being triggered that you were not expecting, or perhaps you mistakenly referenced the accounts table in code or macro. Regards, Dean On Oct 3, 2011, at 1:10 PM, Rocky Smolin wrote: > 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