How delete records from 10 different tables from data in a form via VBA code

I have a form that has 1 field.  The user enters information (text data), into the field.  Then on the same form is a command button labeled "Delete Info".  When the command button is clicked I want to delete the records from each of 10 tables that have that data entered in the text field on the form in a field named CustomerID.  

 How can I delete the records from each table that have the information entered in the field on the form in each of the tables via VBA code.  

Another expert helped my with the code that added the records to the table via if this helps:

   For i = 0 To TableListSize - 1

        SQL = Empty
        If DCount("*", TableList(i), "[CustomerID]='" & CustomerID & "'") = 0 Then
	    if i = 0 then
'FIRST TABLE
	        SQL = "INSERT INTO " & TableList(i) & " ([CustomerID],[CompanyName]) VALUES ('" & CustomerID & "','" & CustomerID & "')"
	    else
'NOT FIRST TABLE
		SQL = "INSERT INTO " & TableList(i) & " ([CustomerID]) VALUES ('" & CustomerID & "')"
	    end if
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, dbSeeChanges
            DoCmd.SetWarnings True
        Else
            MsgBox "The customer ID [" & CustomerID & "] already exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i

Open in new window

--Steve
SteveL13Asked:
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.

Anthony BerenguelCommented:
Hi Steve,

try this bit
public sub Delete_Records
    '// get the client name from you form field
    Dim CompanyName As String
    CompanyName = Me.txtClientID01 'NAME_OF_YOUR_CONTROL.VALUE
  
	
    Const TableListSize = 1 'set your table size
    Dim TableList(TableListSize) As String
    
    '// populate the tableList array with your table names
    TableList(0) = "Customers"
    'TableList(1) = "table 2 name"
    'TableList(2) = "table 3 name"
    'TableList(3) = "table 4 name"
    'TableList(4) = "table 5 name"
    'TableList(5) = "table 6 name"
    'TableList(6) = "table 7 name"
    'TableList(7) = "table 8 name"
    
    Dim sql As String
    Dim i As Integer
    
    '// Loop through each table and for each table insert the client into the respective table if it doesn't already exist in the table.
    For i = 0 To TableListSize - 1
        sql = Empty
        If DCount("*", TableList(i), "[CompanyName]='" & CompanyName & "'") > 0 Then
            'delete the client from the table
            sql = "DELETE * FROM " & TableList(i) & " WHERE [CompanyName]='" & CompanyName & "'"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL sql, dbSeeChanges
            DoCmd.SetWarnings True
        Else
			'no client to delete
            MsgBox "The company name [" & CompanyName & "] doesn't exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i
	
End sub

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The only thing I would change on that is:

            DoCmd.SetWarnings False
            DoCmd.RunSQL sql, dbSeeChanges
            DoCmd.SetWarnings True

 To:

  CurrentDB().Execute sql, dbFailOnError

 and add error handling.

 The way it is now, if anything goes wrong, you won't know it.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and BTW, use:

dbengine(0)(0)

or a variable for CurrentDB()

or the procedure here:

http://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 You really don't want to be using CurrentDB() on every call if your just deleting records.

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

Anthony BerenguelCommented:
Jim brought up a good point.

Error handler included
public sub Delete_Records
	on error goto Error_Handler
    
	'// get the client name from you form field
    Dim CompanyName As String
    CompanyName = Me.txtClientID01 'NAME_OF_YOUR_CONTROL.VALUE
  
	
    Const TableListSize = 1 'set your table size
    Dim TableList(TableListSize) As String
    
    '// populate the tableList array with your table names
    TableList(0) = "Customers"
    'TableList(1) = "table 2 name"
    'TableList(2) = "table 3 name"
    'TableList(3) = "table 4 name"
    'TableList(4) = "table 5 name"
    'TableList(5) = "table 6 name"
    'TableList(6) = "table 7 name"
    'TableList(7) = "table 8 name"
    
    Dim sql As String
    Dim i As Integer
    
    '// Loop through each table and for each table insert the client into the respective table if it doesn't already exist in the table.
    For i = 0 To TableListSize - 1
        sql = Empty
        If DCount("*", TableList(i), "[CompanyName]='" & CompanyName & "'") > 0 Then
			if msgbox("Are you sure you want to delete this record?",vbYesNo) = vbYes then
				'delete the client from the table
				sql = "DELETE * FROM " & TableList(i) & " WHERE [CompanyName]='" & CompanyName & "'"
				
				DoCmd.SetWarnings False
				DoCmd.RunSQL sql, dbSeeChanges
				DoCmd.SetWarnings True
			end if
        Else
			'no client to delete
            MsgBox "The company name [" & CompanyName & "] doesn't exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i

Exit_Sub:
	exit sub
	
Error_Handler:
	msgbox ("Error " & err.number & ", " & err.description & " occured. Record was not deleted from table " & tableList(i),vbokonly)
	resume next	
End sub

Open in new window

0
SteveL13Author Commented:
Doesn't seem right.

First of all it asked me if I want to delete the record from all of the tables, one at a time.  It should only ask once.   Then what it did was remove all of the records from the tables.
0
Anthony BerenguelCommented:
oh, this will only ask once,
public sub Delete_Records
	on error goto Error_Handler
    
	'// get the client name from you form field
    Dim CompanyName As String
    CompanyName = Me.txtClientID01 'NAME_OF_YOUR_CONTROL.VALUE
  
	
    Const TableListSize = 1 'set your table size
    Dim TableList(TableListSize) As String
    
    '// populate the tableList array with your table names
    TableList(0) = "Customers"
    'TableList(1) = "table 2 name"
    'TableList(2) = "table 3 name"
    'TableList(3) = "table 4 name"
    'TableList(4) = "table 5 name"
    'TableList(5) = "table 6 name"
    'TableList(6) = "table 7 name"
    'TableList(7) = "table 8 name"
    
    Dim sql As String
    Dim i As Integer

	if msgbox("Are you sure you want to delete this record?",vbYesNo) = vbYes then    
		'// Loop through each table and for each table insert the client into the respective table if it doesn't already exist in the table.
		For i = 0 To TableListSize - 1
			sql = Empty
			If DCount("*", TableList(i), "[CompanyName]='" & CompanyName & "'") > 0 Then

					'delete the client from the table
					sql = "DELETE * FROM " & TableList(i) & " WHERE [CompanyName]='" & CompanyName & "'"
					
					DoCmd.SetWarnings False
					DoCmd.RunSQL sql, dbSeeChanges
					DoCmd.SetWarnings True
				
			Else
				'no client to delete
				MsgBox "The company name [" & CompanyName & "] doesn't exists in table [" & TableList(i) & "]", vbInformation
			End If
		Next i
	end if	
Exit_Sub:
	exit sub
	
Error_Handler:
	msgbox ("Error " & err.number & ", " & err.description & " occured. Record was not deleted from table " & tableList(i),vbokonly)
	resume next	
End sub

Open in new window


did all the records in the tables have the same company name? Because the sql statement i wrote it set up to only delete from the table(s) if the CompanyName in the tables matches the select CompanyName.
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
PatHartmanCommented:
Call me crazy but why would you write code to do something the database engine will do for you?

We discussed this in your previous thread but I'll bring it up again.

Only the "parent" table should have an autonumber as the PK.   For the "child" tables, the PK will be defined as long integer since the only values allowed are ones that already exist in the "parent" table.  The "child" tables are not generating new numbers.  You can make that change without any coding change at all.  

Although, technically you may not have to change the autonumbers to long integer, leaving them as autonumbers will confuse any knowledgeable person who looks at the schema.  Once the autonumbers are converted to long integer, open the relationship window and draw the join lines from the "parent" table to each of the "child" tables.  Select "enforce RI" and "cascade delete".  Then when you delete the "parent" record, the database engine will delete all related "child" records for you.

If you get an error message when attempting to enforce RI, that will indicate that you have orphan data in a table.  Delete the orphan data and then set the RI.  Remember, you are not losing anything by deleting the orphan data.  It isn't linked to a "parent" and so you will never see it in any forms or queries.

Database engines are very good at managing referential integrity, programmers are sloppy.  Show me a system where the programmer thinks he knows better, and I'll show you bad data.  RI is your friend.
0
SteveL13Author Commented:
Ok.  The last code worked.  But I have decided that Pat's suggestions are relevant and have decided to go that direction.  I have to see if we can setup relationships and make it easy on me.
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.