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