VBA Code for a module

[msyoung01]

OK People, here's one for ya. I Have a table: Stuff Sales, this table has 6 columns: 1) Doc Nums and 3) Type are the ones I am concerned with. Column 1 allows multiple instances of the sane Doc Num, and column 3 is one of three, blank, FT or EA. What I am looking for is the code that will allow me to loop through the table, one row at a time and check if Type equals FT, then if Type does, delete all rows with that doc num.

(IE)

Doc Num Type
11111 EA
11111 EA
11111 FT
11112 EA
11112 EA


after the looping
11112 EA
11112 EA

I Hope you fellas can help.

 

[Ziggy]

You should be able to do this with a delete query. Set up a select query and put FT in the criteria for the column it belongs to. This will return all the records with FT. Now change the "select" query to a "delete query".




Ziggy

 

[bat17]

try

Sub delRecs()
Dim dbs As Database
Dim rstLinesToClear As Recordset
Dim strDelLine As String
Dim strLinesToClear As String

On Error GoTo ErrorHandler

strLinesToClear = "SELECT [stuff sales].[doc num] FROM [stuff sales] WHERE ((([stuff sales].[type])='ft'));"
Set dbs = CurrentDb
Set rstLinesToClear = dbs.OpenRecordset(strLinesToClear, dbOpenDynaset)
If rstLinesToClear.EOF Then GoTo ErrorHandlerExit
rstLinesToClear.MoveLast
While Not rstLinesToClear.BOF
strDelLine = "Delete [stuff sales].[Doc Num] FROM [stuff sales] "
strDelLine = strDelLine & "WHERE ((([stuff sales].[Doc Num])="
strDelLine = strDelLine & rstLinesToClear![Doc Num] & "));"
dbs.Execute strDelLine
rstLinesToClear.MovePrevious
Wend

ErrorHandlerExit:
Set rstLinesToClear = Nothing
Set dbs = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub


HTH

Peter