Results 1 to 5 of 5

Creating a delete script to delete a record from multiple tables

  1. #1
    Suttoa is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5

    Creating a delete script to delete a record from multiple tables

    Hi guys, this is a bit complex.. but also simple at the same time. I have a database that has separate tables keeping track of various aspects of a customers data, each customer is assigned to an associateID. I want to create a delete script to remove a customers that are assigned to a particular associatesID from all tables that reference the customersID. I tried with a delete query but didn't have much luck so moved into VBA/SQL... I have a delete script that I know clears out the records of the customerID From all tables, but now I need it to check for associatesID, then match all customerIDs that have the same associatedID's assigned, then send those customerID's to the delete function :




    Here is the code.. the cmdDeleteBlock_Click() is the one that is not working.. when I click the button I get no errors.. it just does not do anything at all.. but I think its close..
    ----------------------


    Code:
    Private Sub cmdDeleteBlock_Click()    ---Its a button they click to delete the associateID that is selected from a list of associates (lstassociate.value)
       Dim AssociateNo As Long
       Dim rst As DAO.Recordset
       Dim CustomerNo As String
       
       CustomerNo  ="init"
       AssociateID = lstassociate.Value
       
        While CustomerNo  <> Null
             'create a SQL statement to delete block information from tblBlockInfo
             strSQL = "Select CustomerNo FROM tblAssociateCustomers WHERE AssociateNo = " & AssociateNo
               
            'run it against the database
            CurrentDb.Execute strSQL
            
           Set rst = CurrentDb.OpenRecordset(strSQL)
           CustomerNo = rst!CustomerNo
           MsgBox ("the current CustomerNo: '" & CustomerNo)
           DeleteCustomers(CustomerNo)
           rst.close
           Set rst = Nothing
       Wend
       
    End Sub
    ---------------------------------------


    Code:
    Private SubDeleteCustomers(CustomerNo As String)
    lCustomerNo = CustomerNo
        
     If IsNull(lCustomerNo) Then
            MsgBox "No more lCustomerNo assigned to Associate, exiting delete script!", vbCritical
        Else
        If MsgBox("Are you sure you want to remove CustomerID #" & lCustomerNo & " from the system?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
            DoCmd.SetWarnings False
        
            Dim db As DAO.Database
            Set db = CurrentDb
        
            db.Execute "DELETE * FROM tbl1 Where CustomerNo = " & lCustomerNo, dbFailOnError
            db.Execute "DELETE * FROM tbl2 Where CustomerNo= " & lCustomerNo, dbFailOnError
            db.Execute "DELETE * FROM tbl3 Where CustomerNo = " & lCustomerNo, dbFailOnError
            db.Execute "DELETE * FROM tbl4 Where CustomerNo= " & lCustomerNo, dbFailOnError
            db.Execute "DELETE * FROM tbl5 Where CustomerNo = " & lCustomerNo, dbFailOnError
            db.Execute "DELETE * FROM tblassociatetoCustomer Where lCustomerNo = " & lCustomerNo, dbFailOnError
    
            db.Close
            MsgBox ("Customer#" & lCustomerNo & " Has been successfully removed from the local database")
            Me.Refresh
        
            Set db = Nothing
            DoCmd.SetWarnings True
        End If
    
    End If
    end sub
    ----------------------------

    I guess my main issue is, I am not sure exactly how to select the customerID from the tblAssociateCustomers, then send each customerID to the delete function.. because an associate can have multiple customers..I want to do one at a time until all are deleted..
    Last edited by Suttoa; 08-23-2019 at 12:16 PM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    In future, please post lengthy code between CODE tags to retain indentation and readability. You could edit your post.

    Deleting records really should be a rare event. Why do you want to delete?

    Cannot 'Execute' a SELECT query. Action queries (DELETE, INSERT, UPDATE) are executed. That line should be removed.

    Do you really want user to confirm each customer? Sounds tedious.

    Step debug.

    Don't need to use SetWarnings with Execute method.

    How can a Function have an End Sub?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Suttoa is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5
    thanks, most msgbox's are just for testing purposes, I just want to make sure its going through the customer records properly

    the delete customers is working.. do you know how I could pull the customerID from the tblassociatecustomers..
    Assuming the table is simple like 3 columns, I just need to pull out where associateID matches, and do it one at a time: recordnumber associateID CustomerId

    Yeah, this database is not really live data.. it takes a copy of live data but doesn't edit it.. its just used to play with numbers basically.. so if they delete a record they can always pull it in again from the main database..

    actually.. I guess I can just create a simple query from query wizard to pull the top value where a customerID has the related associates ID and pass that back as a variable?
    with the DoCmd.OpenQuery "Query Name" Hummm.. then how would I get that result into a variable.. maybe public variable in the module?.. Sorry I am a new programmer.. not sure why I was stuck with the project to begin with lol

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,842
    Not really understanding issue.

    You already have code that opens a recordset of customers associated with selected associate and passes customerID to delete function 1 at a time, although why you need to do 1 at a time escapes me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Suttoa is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    5
    thank you for the tips, I was able to solve this issue working on your clues, with code looking similar to this:

    Code:
            While CustomerNo <> "finished"
                'create a SQL statement select customerId associated to AssociateID
                strSQL = "Select customerId FROM tblone WHERE associateID = " & associateID
               
                'run it against the database
                Set rst = db.OpenRecordset(strSQL)
            
                If rst.EOF Then
                    customerID = "finished"
                    MsgBox ("This Block has no Policy's associated with it to delete")
                        Else:
                        customerID = rst!customerID
                        MsgBox ("the current customer number is: " & customerId)
                        Call DeletePolicy(customerId)
                End If
                rst.Close
                Set rst = Nothing
            Wend

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-29-2017, 04:06 PM
  2. Replies: 3
    Last Post: 10-18-2017, 04:01 AM
  3. Replies: 4
    Last Post: 12-13-2016, 09:48 AM
  4. Replies: 2
    Last Post: 12-11-2014, 02:29 PM
  5. Trying to Delete record using delete query
    By MooseOTL in forum Access
    Replies: 13
    Last Post: 10-04-2011, 02:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums