Deleting Records...

[waldo7474]

I am pretty new to the whole Access thing, here is my question:

I have a form with a subform. I am trying to make a delete record button. I would like to be able to delete the main record, as well as any underlying record in the subform (not as concerned about the subform, but it would be ideal). I have tried to use the wizard for this, but it won't delete the record once it has been saved.

Does anybody have any suggestions? Any help would be GREATLY appreciated.

Thanks,
Wally

 

[MakeItSo]

Hi Wally.

What you need is to run a DELETE query:
Dim sSQL as String

sSQL="DELETE * FROM your table WHERE Record ID = " & Me!ID

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

You might need to create a similar DELETE query for the record on your subform, if its data is stored in an extra table.

Put this code in the Click() event of your button.


Season Greetings,
MakeItSO

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[waldo7474]

Thanks for your help.

Unfortunately this didn't work. I don't know if it matters, but I used a message box of my own, to make sure that they knew that the record would be lost for ever. Under the "Yes" section of that message box, I put your code. I am using a query instead of a table, as my control source for the form. So this is my sSQL statement:

sSQL = "DELETE * FROM queryname WHERE

.[PartID]

= " & Me! PartID

This did not work. When I try to use the button, the message box comes up and then I click "Yes" and an Access message box opens that says "Could not delete from specified tables."

Is this because of the query? I can delete the record manually when I have the query open, but this is not the way I want to do it. Is it because I have two things labeled "PartID" from each table inside the query (they are not named the same thing in the form)? I think that I am close, I am hoping I just typed something in wrong.

Thanks again for any ideas you may have!
Wally

 

[MakeItSo]

Hi Wally!

Back from XMas... hope you had a nice one, too.
Problem is: sSQL = "DELETE * FROM queryname

You cannot delete from the query since it does not really contain records, it just pulls them from the table.
So you must reference the table in your DELETE statement (even if your record source is a query):

sSQL = "DELETE * FROM table WHERE

.[PartID] = " & Me!PartID

Remember: always keep backing up your db until you are sure you are truly deleting only the desired records

Andy


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos