Reducing Repititive code

The following code: It looks up in a particular access table and if the subcategory has any particular value then it will transfer the particular table(corresponding to the subcategory_ID) to excel worksheet in a particular workbook. After that it deletes the first row and again looks for another number. I have to write this code as many times as there are subcategory IDs. Is there any way in which I can look into the table and do the necessary actions, without deleting the rows again and again. As I keep on writing the same code hundred times as I have 100 types of subcategory.


Function TableEmail2zzz()

Dim varX As Variant
Dim varY As Variant
Dim varZ As Variant


varX = DLookup("[SUBCATEGORY_ID]", "Show SubCats per manufacturer for module")
varY = DCount("[Model Code]", "[ABC]")

If varX = 1 Then
If varY > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ABC", "My Document\Verification" & "\XYZ.xls", , "ABC"
End If
Call Delete
End If

varX = DLookup("[SUBCATEGORY_ID]", "Show SubCats per manufacturer for module")
varY = DCount("[Model Code]", "[DEF]")

If varX = 2 Then
If varY > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DEF", "My Document\Verification" & "\XYZ.xls", , "DEF"
End If
Call Delete
End If

(And so on till the end of file)

End Function

Sub Delete()

DoCmd.OpenTable "Show SubCats per manufacturer for module", acNormal
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings False
DoCmd.Close acQuery, "Show SubCats per manufacturer for module", acSaveYes

End Sub


Any help would be appreciated.

Thanks

Raj

I can not see what you are actually trying to achieve but I am sure that there is an easier way.

Can you give a brief description of your database structure and what you want to put into the spreadsheet?