Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Need help on loop code

carolyna131carolyna131 Member Posts: 1
I am new to VB and have some basic understanding. I have a lookup table with the values that I need. I need to generate a workbook that contains 3 files for each group on my lookup table.

Here is what I have so far. I dont want to have to write a code for each group listed which is 120 (I have one example here A1005001). I want the loop to look up each group and generate a corresponding report.

Option Compare Database

Private Sub cmdbut_ImpFiles_Click()
On Error GoTo Err_cmdbut_ImpFiles_Click

Dim stDocName As String
Dim strSQLStmt As String
Dim strXLFile As String
Dim strTxtFile As String

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

'A1005001 SUMMARY'

strSQL = "SELECT * FROM [1_Monthly SUMMARY]" & _
"WHERE ([1_Monthly SUMMARY].[Org ID]) ='A1005001'"


strQDF = "Monthly SUMMARY"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

strXLFile = Me!directory & "A1005001 OCT09 IETS BILLING.xls"
DoCmd.TransferSpreadsheet acExport, , strQDF, strXLFile

dbs.QueryDefs.Delete strQDF


'A1005001 HANDLING'

strSQL = "SELECT * FROM [2_Monthly HANDLING]" & _
"WHERE ([2_Monthly HANDLING].[Customer Org Id]) ='A1005001'"


strQDF = "Monthly HANDLING"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing


strXLFile = Me!directory & "A1005001 OCT09 IETS BILLING.xls"
DoCmd.TransferSpreadsheet acExport, , strQDF, strXLFile

dbs.QueryDefs.Delete strQDF

'A1005001 BACKUP'

strSQL = "SELECT * FROM [3_Monthly BACKUP]" & _
"WHERE ([3_Monthly BACKUP].[Org Id]) ='A1005001'"

strQDF = "Monthly BACKUP"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing


strXLFile = Me!directory & "A1005001 OCT09 IETS BILLING.xls"
DoCmd.TransferSpreadsheet acExport, , strQDF, strXLFile

dbs.QueryDefs.Delete strQDF

intMsgResp = MsgBox("IETS BILLING REPORTS have been created", _
vbOKOnly, "Finished")


dbs.Close
Set dbs = Nothing


DoCmd.Close acForm, "Export Billing Reports"


Exit_cmdbut_ImpFiles_Click:
Exit Sub

Err_cmdbut_ImpFiles_Click:
MsgBox Err.Description
Resume Exit_cmdbut_ImpFiles_Click

End Sub
Sign In or Register to comment.