Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 21st, 2005, 10:45 AM
Registered User
 
Join Date: Sep 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2000 to 2002 conversion issues

I have designed a database in 2000. Have a form that views records and allows you to drill down to joined table records. In 2000 when I use the record selector to delete the main record, I have a macro that goes through and deletes the records from the joined table, then lets Access delete main record like normal. When database is opened in 2002 (not converted), and the same process is attempted, the subrecords are deleted and it 'appears' that the main record is deleted. When you close and reopen the form, however, the main record is still there.

Any ideas on why this happens? I have been programming in 2000 for years, but this is my first attempt at working across platforms.

 
Old September 22nd, 2005, 07:24 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Do you have cascade deletes allowed?
If not, perhaps when your code gets to the point where it is trying to delete the parent record, it still sees the child records and won't continue. Since you can delete child records at any time, that is not an issue. But perhaps it is saving the parent thinking there are still orphaned records.
I am not sure if this is the right relationship since you use the terms main and subrecord. Which is the parent record and which the child?

HTH

mmcdonal
 
Old September 27th, 2005, 10:51 AM
Registered User
 
Join Date: Sep 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I do have cascade deletes allowed. The form that I am trying to delete from is based on the main table. The scenario is a student table that has a one to many relationship with a relation table that has a many to one relationship with a guardian table. I had to set it up this way because there can be more than one student assigned to more than one parent. I used the term subrecord, but generally speaking because I don't have any true "subforms" in this scenario. The first form shows the student information and there is a button that allows you to open up the guardian form that displays all of the guardians for that student. The delete macro on the student form checks to see if there are any other students assigned to the guardians for the student you are deleting. If there are it doesn't delete the guardians, just the student. If there aren't it deletes the guardians first and then the student. And, as I said, it works fine in 2000, but not in 2002. I have to do the delete a second time after I re-open the form for it to truly delete the student.

 
Old September 27th, 2005, 11:20 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Since you have the main form open during this operation, the record is locked, and so can't be deleted. Try doing a freeze screen, or just closing the main form before the delete occurs, then reopening it after the main delete on a different record. I am not sure how 2000 would let you do this while the main form is open.


mmcdonal
 
Old September 27th, 2005, 12:28 PM
Registered User
 
Join Date: Sep 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The macro that checks whether or not to delete the guardians is activated by the On Delete event on the student form, when I click the record selector for that student and hit the 'delete' key, so the form has to be open doesn't it?






Similar Threads
Thread Thread Starter Forum Replies Last Post
Access97 local forms in Access 2000/2002 vance696 Access 2 October 14th, 2005 10:26 AM
Excel 2000 vs Excel 2002 Macro Issue williadn Excel VBA 1 July 14th, 2005 09:09 AM
Access 2000 & 2002 upgrade problem brianharding Access 4 May 3rd, 2004 04:55 AM
Issues Connecting to an Access 2000 Database... Jonny ADO.NET 1 February 11th, 2004 09:54 AM
Access XP VBA compatibility issues w/ Access 2000 bourgeois02 Access VBA 1 August 19th, 2003 04:14 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.