Excel macro in Access -dual conditional search and delete.

Hatcheda

Active Member
Joined
Dec 8, 2005
Messages
354
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" ?
Code:
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 8, 2005
Messages
354
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

Board Regular
Joined
Oct 26, 2005
Messages
128
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Dec 8, 2005
Messages
354
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
You really should combine the tables.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,222
Messages
5,570,969
Members
412,352
Latest member
Radek Z
Top