Excel macro in Access -dual conditional search and delete.

[Hatcheda]

Below is a code that I have for Excel. I would like to use it in an Access database to clear rows that match both the Client number and specific date. It works well in excel. -no errors. Can this be placed in Access and called by a button to open a table and preform the same as calling it as a macro in excel? If so how would I do that? -open a page with the button then run the code. What would the open and run command be for a table named "#comments" ?

Sub ClearImports()
Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

Dim ClientNumber As String, EntryDate As String
Dim FilterRange As Range, DeleteRange As Range

Client = InputBox("Enter Client Number", "Client")
Entry = InputBox("Enter Entry Date", "Date")


Application.ScreenUpdating = False
ActiveSheet.AutoFilterMode = False

Set FilterRange = Range(("A1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 5))
Set DeleteRange = Range(("A2"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 5))
With FilterRange
.AutoFilter Field:=1, Criteria1:=Client
.AutoFilter Field:=4, Criteria1:=Entry, Operator:=xlAnd
End With
On Error Resume Next
DeleteRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
Set FilterRange = Nothing
Set DeleteRange = Nothing
End Sub

 

[Norie]

To do this sort of thing in Access you would use a Delete query.

You could use parameters in the query to specify the criteria for the records to delete.

 

[Hatcheda]

I have multiple records. about 10k+ I need to delete them based off of an exact client # and Date. The Client # must still exist with other dates. Entries are made only once a month and are all dated mm/1/yyyy. How do I delete all client# 11111 and date 11/1/2005 and still leve all Client 11111 and date 12/1/2005. format is ref, client, varies, varies, date. any ideas? I would like this to happen when I type the client # in textbox1 and date in textbox2 then click a button. there are 6 different tabels that this needs to work in. so it would need to delete the same # and date in all of them. will I need more than one button? there are also 20+ client #'s, 24+ monthly entries, and 10+ rows per entries. Meaning 11111 and 11/1/2005 may exsist in ten rows. the infor between them in the row varies every time, but still needs deleted with the row. -Any Ideas?

 

[pritcham]

The best thing to bear in mind with things like this is to firstly design/develop the query as a SELECT query. Add the relevant constraints in the query design view (in your case something along the lines of: Where ClientID = Forms!MyFormName.MyTextBox AND MyDateField = Forms!MyFormName.MyDateTextBox). Run the query to make sure it's picking up the items you want/need to delete and if all is ok then simply alter the query type to a DELETE query.

Hope that helps
Martin

 

[Norie]

Hatcheda

Why do you have 6 different tables with what sounds like basically the same data?

Could you not combine them into a single table with an extra field that indicates which table they came from?

 

[Hatcheda]

The tables are different because they contain very different information. the only similarity is that they all contain dates and client numbers. these are just required to pull the rest of the info each entry contains. I export sheets based off of the rest of the info. However, on import, if I have an error the tables must be cleared of all matching a certain date and client number. (the same as the failed import.) I have figured out a query that will pull sheet by sheet. Can I do them all at once? one query that will search 6 tables and list them under eachother? -to view the delete after verified?

 

[Norie]

You really should combine the tables.