Delete HELP! (1 Viewer)

[djshongo]

ok ive made myself a DELETE query, that should Delete all records in a table that are 'TRUE'....
however i cant add it to a command button in my form...it wont appear in my 'command button wizard' as part of my queries, its not there...

any ideas why?

thanks

 

[Mile-O]

On the command buttons's click event, click the ellipsis (...) and select Code Builder. Copy the relevant code below into the new sub. Change QueryName to the name of your query. It should work now.

Private Sub cmdDelete_Click()
    With DoCmd
        .SetWarnings False
        .OpenQuery "QueryName"
        .SetWarnings True
    End With
End Sub

 

[djshongo]

thats great thanks! it works fine..!

can i just ask you another question...

in the same button...is it possible to MOVE the records i'm about to delete into a new DIFFERENT table...(say called 'old records')

thanks again

 

[Mile-O]

Create an APPEND query.

With the code above you can add another lone before the OpenQuery. This would be another OpenQuery statement, this time naming the append query.

 

[djshongo]

i tried it but it keeps giving me an error msg..."Ambiguous name detected Command24_Click"

whats wrong with this code..





Option Compare Database

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

Dim stDocName As String

stDocName = "x"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub



Option Compare Database

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

Dim stDocName As String

stDocName = "xx"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub





thanks

 

[Mile-O]

You are repeating the exact same function. It's ambigious because Access doesn't know which routine to run when you click the button. You can't have routines in the same module identically named.

What I meant was to do this:

Private Sub cmdDelete_Click()
    With DoCmd
        .SetWarnings False
        .OpenQuery "AppendQuery"
        .OpenQuery "DeleteQuery"
        .SetWarnings True
    End With
End Sub

That's all.

 

[djshongo]

oh ok thanks! lol

i'll try that now!

 

[djshongo]

so should it look like this?




Option Compare Database



Private Sub cmdDelete_Click()
With DoCmd
.SetWarnings False
.OpenQuery "x"
.OpenQuery "xx"
.SetWarnings True
End With
End Sub

Private Sub Command28_Click()

End Sub


this doesnt work etiher......

 

[Mile-O]

Is your command button called cmdDelete?

 

[djshongo]

it is now!

it worked for me once there now...but it wont work again??

 

[Mile-O]

Okay. Comment out the .SetWarnings False line and run the code. Are you given an error message?

 

[djshongo]

yey!

its now working at last! lol

the button Deletes all the 'old' records, and successfully TRANSFERRS them into my 'new' table..!

3months of searching code and getting silly error messages!

i must thank you for your help, its much appreciated, you'ved saved me a LOT of time (and stress because i was close to smashing this computer)..

so thank you again!