Unncessary Repition of Code

[rajltd]

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

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

 

[rajltd]

Can anyone pls reply to this post for any solution

 

[Peo Sjoblom]

What post?

 

[Myrna Larson]

Sounds like you should be using variables instead of hard-coded literal text. I don't know which
items are variable and which are constant, but if you put the variable items in an array (you'll
have to write the code to fill the array, or retrieve it from a database), then you can use a
For/Next loop. Assuming it's the [ABC] that changes, it would look something like this:

For i = 1 To UBound(TheList)
varX = DLookup("[SUBCATEGORY_ID]", "Show SubCats per manufacturer for module")
varY = DCount("[Model Code]", TheList(i))

If varX = 1 Then
If varY > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, TheList(i), _
"My Document\Verification" & "\XYZ.xls", , TheList(i)
End If
Call Delete
End If
Next i