Hey Guys,
I'm just playing around with some code I wrote for work and am trying
to minimize the coding as much as possible.
So two questions:
1. What are your best practices for organizing your code, aside from
comments? I typically split my code up into sub procedures and then run
them all from the top of the code. My problem is that my code page is
taking me a while to scroll through and find the code i'm looking for.
2. I have two queries. The first creates a recordset of unique values
of a single field. The second uses that list of unique values as a
variable to loop through another recordset and pull the correct
records. Is there away to write some SQL that would combine these two
queries.
Private Sub Tabs()
Dim db As DAO.Database
Set db = CurrentDb()
db.Execute _
"SELECT DISTINCT[Customer Group] FROM [Customers];", _
dbFailOnError
End Sub
__________________________________________________ _____________________
I hacked this up for privacy, hopefully you can make sense of it! :)
Private Sub Export()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim XLS As DAO.QueryDef
Dim rs1 As String
Dim SQL As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Tabs", dbOpenSnapshot)
Do Until rs.EOF
rs1 = rs.Fields("Customer Group")
SQL = etc.
Set XLS = db.CreateQueryDef(rs1, SQL)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
rs1, "c:\test\loss.xls", True
db.QueryDefs.Delete rs1
rs.MoveNext
Loop
rs.Close
XLS.Close
End Sub
Thanks guys, hope that clear enough