Delete Record and Subform Records

[vbaInet]

It was the wizard that you initially used and that failed. JamesMcS gave you the delete syntax. Using what he gave you, your code could look something like:

    dim db as DAO.Database
    
    set db = currentdb
    If (MsgBox("Deleting a supplier record will permanently delete it.  Are you sure you want to delete?", vbYesNo, "Delete Confirmation")) = vbYes Then
        If Me.Dirty Then
            Me.Undo
            ' DoCmd.SetWarnings False
            If Not Me.NewRecord Then
                db.execute "DELETE * from [COLOR=Red][B][tablename][/B][/COLOR] WHERE [COLOR=Red][B][supplier][/B][/COLOR]=" & [COLOR=Red][B]me.supplierno[/B][/COLOR]
            End If
        End If
     
        MsgBox "The supplier was successfully deleted.", , "Delete Confirmation"
        
    End If

Amend the control name/fields highlighted. I haven't changed your logic, just inserted the DELETE statement.

 

[vb9063]

It was the wizard that you initially used and that failed. JamesMcS gave you the delete syntax. Using what he gave you, your code could look something like:

    dim db as DAO.Database
 
    set db = currentdb
    If (MsgBox("Deleting a supplier record will permanently delete it.  Are you sure you want to delete?", vbYesNo, "Delete Confirmation")) = vbYes Then
        If Me.Dirty Then
            Me.Undo
            ' DoCmd.SetWarnings False
            If Not Me.NewRecord Then
                db.execute "docmd.runsql "DELETE * from [COLOR=red][B][tablename][/B][/COLOR] WHERE [COLOR=red][B][supplier][/B][/COLOR]=" & [COLOR=red][B]me.supplierno[/B][/COLOR]
            End If
        End If
 
        MsgBox "The supplier was successfully deleted.", , "Delete Confirmation"
 
    End If

Amend the control name/fields highlighted. I haven't changed your logic, just inserted the DELETE statement.

Ok I changed the tablename to the name of the Supplier table "Suppliers" and the other two to Sup_Name as the text box in the form has the same name as the field in the table...but I get a syntax error..

 

[JamesMcS]

I can see the problem. You're using quote where there qhould be apostrophes. Change the line to

docmd.runsql "DELETE * from [COLOR=red][B][tablename][/B][/COLOR] WHERE [COLOR=red][B][supplier][/B][/COLOR]=" & [COLOR=red][B]me.supplierno[/B][/COLOR]

without the db.execute, see if that works

 

[vbaInet]

fyi: With db.Execute you don't need to turn off and on the warnings. There are no warnings with that method.

 

[vb9063]

Oo I have a development....
I put a messagebox within the code to see whether it was going to the sql sequence and it wasnt until I made the fields "dirty"...i have been entering a new supplier in and saving it and then going back to it so that is not dirty..but when i go to delete it the delete sequence is missed out...so does that mean I have the me.dirties around the wrong way or the me.newrecord wrong?? When I made it dirty it also asked me for a parameter value...

 

[JamesMcS]

That's handy to know VBA - I find them TOTALLY annoying! In that case it would be

db.execute "docmd.runsql 'DELETE * from [COLOR=red][B][tablename][/B][/COLOR] WHERE [COLOR=red][B][supplier][/B][/COLOR]=' & [COLOR=red][B]me.supplierno[COLOR=black]"

[/COLOR][/B][/COLOR]
Do you need the dirties? I'd just go with the not new record IF and get rid of the others... I think, but then I've not used .dirty in code before

 

[vb9063]

Not sure about dirty stuff..am v new to access as you can probably tell!
Why does it ask me for a supplier name parameter...is it not able to get it from the text box??

 

[JamesMcS]

Well, it'll ask you for input if it can't find it where you've told it to look. Just to clarify - the code is in a button on the main form, and the supplier number text box is on the same form yeah?

 

[vb9063]

yep thats it..

 

[JamesMcS]

OK, can you copy your current code in? And let us know the form name and the names of the relevant controls.

 

[vb9063]

OK, can you copy your current code in? And let us know the form name and the names of the relevant controls.

Hi James, I have attached it, the Form name is Suppliers and the supplier name is Sup_Name.
Thanks v much!

 

[vb9063]

The button is..
cmdDeleteSupplier

 

[JamesMcS]

I think I screwed up on the syntax. You can't specify criteria when using delete * in a query. Try:

DoCmd.RunSQL "DELETE Suppliers.Sup_Name, Suppliers.Sup_Building, Suppliers.Sup_Street, Suppliers.Sup_Town, Suppliers.Sup_County, Suppliers.Sup_Postcode, Suppliers.Sup_Tel, Suppliers.Sup_Fax, Suppliers.Sup_Website, Suppliers.Sup_Update FROM Suppliers WHERE (((Suppliers.Sup_Name)='" & Me.Sup_Name & "'));"

instead of the existing SQL statement. I just tried it and it works fine. The msgbox still pops up, blank now there's no sup_name to display.

 

[vb9063]

Cool thanks James, so should i get rid of the dirties??

 

[JamesMcS]

Oh yeah I got rid of all that and the newrecord line too - I didn't save it but as far as I remember there was only the one if - the msgbox one.

 

[vb9063]

Thanks v much James!

 

[JamesMcS]

No worries, all good fun!

 

[vb9063]

fun...hmm not so sure!

 

[JamesMcS]

When it works, I mean... when it doesn't... well there's no emoticon for that is there??

 

[vb9063]

Haha Noo and they dont come with sound or movement!