Delete Record button, suppress secondary

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?

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.

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?

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).

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.

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?

If cascading is NOT used then yes, you are correct. Only the records you specify will be deleted.

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....

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?

If LocationBadgeID is a textbox you will probably need to use "Me.LocationBadgeID.Value" instead.

changed it to that, but now set on the

strSQL

for Object Required?

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

Select allOpen in new window

Also if I remove

Set
from strSQL

I get a syntex error:  in string in query expression 'LocationBadgeID=101'
Runtime Error 3075

Can you post the code again, as it currently looks?

OK, I finally got it...


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

two separate lines....

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

No more errors?

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!

Sure,  you're welcome.