EzGoingKev
Registered User.
- Local time
- Today, 16:16
- Joined
- Nov 8, 2019
- Messages
- 199
I have eleven tables that have data appended via queries. All the table names start with "LOAD_". All the tables have a field named "Part Number".
The queries build data for all our part numbers. Sometimes I need data for all part number and sometimes I only want data for specific part numbers. I made a PN_Keep table I add the part numbers I want to keep when dealing with only select part numbers.
I wanted to use this code to run through all the tables and delete the data where the part numbers do not match what I have in my PN_Keep table:
I run it and it does not do anything. I have tested the SQL statement using a query and putting in one of the table names and it works fine.
As always, any assistance is greatly appreciated.
The queries build data for all our part numbers. Sometimes I need data for all part number and sometimes I only want data for specific part numbers. I made a PN_Keep table I add the part numbers I want to keep when dealing with only select part numbers.
I wanted to use this code to run through all the tables and delete the data where the part numbers do not match what I have in my PN_Keep table:
Code:
Function ClearPartNos()
On Error GoTo ErrorHandler
Dim T As TableDef
For Each T In CurrentDb.TableDefs
If T.Name Like "LOAD_*" Then
CurrentDb.Execute "DELETE FROM " & T.Name & " WHERE [Part Number] NOT IN (SELECT [PartNo] FROM PN_Keep)"
End If
Next T
Error_Exit:
Exit Function
ErrorHandler:
MsgBox Error$
Resume Error_Exit
End Function
I run it and it does not do anything. I have tested the SQL statement using a query and putting in one of the table names and it works fine.
As always, any assistance is greatly appreciated.