Need code to delete records from certain tables applying a filter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Help... I can't make this work.

I have 12 tables titled 'ActJan', 'ActFeb', 'ActMar' etc. that have
financial data loaded into them.

The first column in these tables is called 'Flex'. It contains an accounting
string for example 'AA-123-4567-A1-890-ABCD123'

I need to create some code that can select the appropriate table when a
month is selected in a combo box. I've done this in the past by trimming the
value to the first three figures, i.e. January is Jan and concatenating it
with 'Act'.

Once I've selected the correct table I then want to delete any records where
the first 2 characters of the 'Flex' field match the value of a second combo
box or are included in a range under the value of the combo box.

I.E. If the combo value was AA then I'd want to delete all records where the
flex started with AA. If the combo value was AZ then I'd want to delete all
records where the flex started with AA, AB, AC or AD.

I'm running into a brick wall doing this. Any help is very much appreciated.

If you need more info please let me know.
 
Why do you have 12 separate tables? Combine them all into one table with an
additional Month column.
 
The reason for the 12 separate tables is unclear. It is a legacy issue. I
don't want to change them because the database is doing a lot more things and
I don't want to risk some other function I've not been aware of. It works
fine as it is.
 
Hope the following VBA leads you in the right direction.


Dim strTableName As String, strSQL As String, strFlex2 As String

Dim dbs As ADODB.Connection

Set dbs = CurrentProject.Connection

strTableName = "Act" & [Forms]![MyForm].[MonthComboName]

strFlex2 = [Forms]![MyForm].[SecondComboName]

strSQL = "DELETE * FROM [" & strTableName & "] WHERE [FLEX] Like '" &
strFlex2 & "'*"

dbs.Execute strSQL

Set dbs = Nothing
 
Back
Top