Delete Record button, suppress secondary

Hello,

I have my own coding for deleting a record, and wanted to suppress the Access msgbox after mine:

If MsgBox("Are you sure you want to delete this record?" & vbCrLf & "If you do, you will not be able to recover the record.", vbYesNo, "Delete Record") = vbYes Then
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.Close acForm, "frmVGRecordSrchResult", acSaveNo
Else
    If MsgBox("Do you want to close this form?" & vbCrLf & "If you close, no changes will be saved," & vbCrLf & "or click no and save the record", vbYesNo, "Return to form") = vbYes Then
        DoCmd.Close acForm, "frmVGRecordSrchResult", acSaveNo
    Else
        Cancel = True
        Exit Sub
    End If
End If
Ernest GroggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David L. HansenProgrammer AnalystCommented:
Instead of using the DoCmd (which carries with it the system warning messages) you can use the "action query" execute approach instead (which does not involve the warnings which you want to avoid).

For example:
Dim dbs As DAO.Database
Set dbs = CurrentDb

' Execute runs both saved queries like this
dbs.Execute acCmdDeleteRecord, dbFailOnError

'or it can do SQL strings like this
dbs.Execute "DELETE FROM tblMyTable WHERE etc", dbFailOnError

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ernest GroggAuthor Commented:
Sorry,  I don't know how to use this?

If I use the first one from the saved queries, I guess from the query that this pulls from would be put where?
0
David L. HansenProgrammer AnalystCommented:
First let's just use the SQL string method first and see if that avoids the system message. Then, if you'd like, we'll get the saved query to work.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ernest GroggAuthor Commented:
Actually this is based off of a search form:  stLinkCriteria = "[ID]=" & Me![SearchResults] (the DblClick) and the form comes up with the associated record

the form is using a qry that has 2 tables associated.  (one to many relationship).  so, with only one table here I would need to delete the associated record from the secondary table?  Or would this automatically delete the associated record?  the form does not have a subform, just one form based on the query using the two tables (one to many)

In my relationships, I have this already setup based on the tables so...not sure then.

what r ur thoughts?
0
David L. HansenProgrammer AnalystCommented:
Well if you have the tables linked and cascading turned on they you'll just need to delete from the "one" side and the "many" table will automatically clean up itself. The other way (if we stick with the SQL text method) is to save the id of the row being deleted in the "one" table and then use that id to remove the rows in the "many" table. That's obviously more work though. So, I suggest cascading with the linked tables. Just be sure that cascading is what you really want (example: deleting a customer but wanting to keep their transaction data. In such a case cascading would be bad).
0
PatHartmanCommented:
DoCmd.Close acForm, "frmVGRecordSrchResult", acSaveNo
This statement does not cancel any pending data changes.  It closes the form without saving DESIGN changes but your users should NEVER be making design changes to forms or other objects under any conditions.  If you want to cancel any pending data updates, you must use.

Me.Undo

Then, you can close the form and the data won't be changed.
0
Ernest GroggAuthor Commented:
so if the cascading then is turned off, you could essentially use the Id fields per what you want on the table to delete the appropriate.  So if the Name address info I want to keep but delete the associated data for the purchase order etc I could just delete that information.

OK.  

so I could use:

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim strSQL As String

If MsgBox("Are you sure you want to delete this record?" & vbCrLf & "If you do, you will not be able to recover the record.", vbYesNo, "Delete Record") = vbYes Then
     Set strSQL = "DELETE FROM VGSponserLocationBadge WHERE LocationBadgeID=" & Me!LocationBadgeID
     dbs.Execute strSQL, dbFailOnError    
     DoCmd.Close acForm, "frmVGRecordSrchResult", acSaveNo
 Else
     If MsgBox("Do you want to close this form?" & vbCrLf & "If you close, no changes will be saved," & vbCrLf & "or click no and save the record", vbYesNo, "Return to form") = vbYes Then
         DoCmd.Close acForm, "frmVGRecordSrchResult", acSaveNo
     Else
         Cancel = True
         Exit Sub
     End If
 End If


So since I am using the "Many" table the "One" table record is left alone?  Am I right?
0
David L. HansenProgrammer AnalystCommented:
If cascading is NOT used then yes, you are correct. Only the records you specify will be deleted.
0
Ernest GroggAuthor Commented:
Thanks Pat!

I was using the Me.Undo, but was thinking like David was saying.  Say if they wanted to keep the Customer Information but delete the PO#, etc, then like I have it setup (the form that is) would be bad.

I know, you have told me in the past to get it right and I am working to recreate everything....
0
Ernest GroggAuthor Commented:
David,

I keep getting getting the error on the strSQL:

Set strSQL = "DELETE FROM VGSponserLocationBadge WHERE LocationBadgeID='" & Me.LocationBadgeID '

Object required: set on LocationBadgeID

but that field is on the form?
0
David L. HansenProgrammer AnalystCommented:
If LocationBadgeID is a textbox you will probably need to use "Me.LocationBadgeID.Value" instead.
0
Ernest GroggAuthor Commented:
changed it to that, but now set on the

strSQL

for Object Required?
0
David L. HansenProgrammer AnalystCommented:
That's odd. Your code looks like strSQL is dimensioned correctly. Well, let's move the "Dim strSQL As String" to the line where you fill it. Like this:
Dim strSQL As String = "DELETE FROM VGSponserLocationBadge WHERE LocationBadgeID= " & Me.LocationBadgeID.Value

Open in new window

0
Ernest GroggAuthor Commented:
Also if I remove

Set
from strSQL

I get a syntex error:  in string in query expression 'LocationBadgeID=101'
Runtime Error 3075
0
David L. HansenProgrammer AnalystCommented:
Can you post the code again, as it currently looks?
0
Ernest GroggAuthor Commented:
OK, I finally got it...


Dim strSQL As String
strSQL = "DELETE FROM VGSponserLocationBadge WHERE LocationBadgeID= " & Me.LocationBadgeID.Value

two separate lines....
0
Ernest GroggAuthor Commented:
Dim dbs As DAO.Database
 Set dbs = CurrentDb
Dim strSQL As String
strSQL = "DELETE FROM VGSponserLocationBadge WHERE LocationBadgeID= " & Me.LocationBadgeID.Value



If MsgBox("Are you SURE you want to DELETE this record?" & vbCrLf & "If you do, you WILL NOT BE ABLE TO RECOVER the record.", vbYesNo, "Delete Record") = vbYes Then
'Dim strSQL As String = "DELETE FROM VGSponserLocationBadge WHERE LocationBadgeID= " & Me.LocationBadgeID.Value
      dbs.Execute strSQL, dbFailOnError
    DoCmd.Close acForm, "frmVGRecordSrchResult"
Else
    If MsgBox("Do you want to close this form?" & vbCrLf & "If you close, no changes will be saved," & vbCrLf & "or click no and save the record", vbYesNo, "Return to form") = vbYes Then
        DoCmd.Close acForm, "frmVGRecordSrchResult", acSaveNo
    Else
        Cancel = True
        Exit Sub
    End If
End If
0
David L. HansenProgrammer AnalystCommented:
No more errors?
0
Ernest GroggAuthor Commented:
nope.  Deletes the record in the Many table just fine.  Leaves the record in the One Table alone.

Perfect!

Awesome!  That is great!  

I can actually use this in other applications throughout my job(s).

Thanks David!
0
David L. HansenProgrammer AnalystCommented:
Sure,  you're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.