Delete Button w/ Subform (A2K)

Topic

New Reply

Ok, again I have a form and a sub form. I have created a delete button on my form down in the footer of the main form. Now what I want this to do when clicked is delete BOTH the information in the main form and the information in the sub form. The help file is almost useless since it is so hard to navigate half the time. Thanks for the help in advance.

Reply | Quote

Replies

Have you established a realtionship between the two tables? If so, take a look at referential integrity and cascade deleted records.

Reply | Quote

ok I set that, but now it’s giving me some wierd errors and will not let me delete anything. This is the error I recieve.

You cannot add or change a record because a related record is required in tblPublishing.

I didn’t do anything except change the relationship. I’ve not seen this errror before and I fill out all the data in both the main form and subfrom. Any idea’s on why this is telling me this? Thanks for the help.

Reply | Quote

Where is tblPublishing in the relationship? When exactly are you getting this error?

Reply | Quote

Well it is on the One side of the relationship. Although the Records side shouldn’t be a many because there can only be one record for every publication when it comes to our record management process. I am getting this error after I enter information in both the main for and sub form and then I click delete. Is it possible that the AutoNumber field could be whats causing it? Meaning that the autonumbers in one table aren’t the same in the second table? Being the primary key that might do something, but I don’t know. Thanks for the help.

Reply | Quote

The primary key in the publisher’s table should be the foreign key in the records table by which the two are related. If this is not the case then you will get this error. Although, if there is only one records record per publisher I guess it could be the primary key in the other table as well. I haven’t worked that much with one-to-one relationships. At any rate, if you have records records (this is rather awkward) with no matching publisher record you will get that error message. Check your relationships and also check the link child and link master fields properties of the subform control.

Reply | Quote

this is all kind of confusing to me. I am not the best with Access. So I don’t understand why Access would recognize my relationship as a One-to-many relationship. But this is how I set up my relationship. I go to Tools => Relationships and add the tables – tblPublishing, and tblRecordMan. Now I drag the ID field from tblPublishing over to the ID field of tblRecordMan. This brings up the relationship edit window. From here I click the Enforce Referential Integrity button and then select the Delete option for I don’t need the update option. Anyway, I save that and then go to my form to test. I enter in some data and all that good stuff on both forms and then hit the delete button to see if it works. This is when the error pops up. Telling me I can’t add or update for some such reaosn.

Reply | Quote

Are you trying to relate on autonumber fields in both tables? This is not a good idea as they may not synch. You should be using an autonumber field in the publisher table and a number long integer field in the recordman table. This way when you enter information into the main form then go to enter info in the subform it should place the value in the autonumber ifeld in the main form to the long integer field in the subform if you have your lik child and link master field properties set up correctly.

Reply | Quote

Here’s a small sample db with 2 tables a main form a subform and a delete button.

Reply | Quote

Thanks Paul . That example helped alot. I changed the Autonumber field in my RecordMan to a number field and the link worked wonders. I appreciate your patience.

Reply | Quote

Hi Paul

Using A2k (9.0.3821) SR-1 when cecking out Testdb1.mdb I get ”

“The current user account dosen’t have permission to convert or enable this database.”

Any suggestions on how I can run your db?

Thanks, John

Reply | Quote

Yeah, it’s a A97 version. I’ll try to post a 2k version fo you tomorrow.

Reply | Quote

Hi Paul

I have a97.

Usually when I open a97 db in a2k it will asked me if I want run it or convert it.

Just suprised it did’nt ask.

John

Reply | Quote

Try this one. It was created in a vanilla nonsecure environment.

Reply | Quote