Unanswered: Not creating spreadsheet from Access!!
Can someone explain to me why the below code doesn't work properly.
It doesn't create a spreasheet...sometimes. If i set a breakpoint and work through the code step by step in debug mode then the spreadsheet gets created perfectly. But the next day, the same code (without breakpoints or anything) doesn't create the file at all!!?
Is there any other more stable way to export the code?
Dim projtype As String
Dim qry As QueryDef
Dim avgDay As Integer
Dim rs As Recordset
Dim sql1, sql2, dtString As String
Dim dt As Date
Dim sheet As Object
Set db = CurrentDb()
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryXL"
dt = Date
dtString = Format(dt, "mm") & "/" & Format(dt, "dd") & "/" & Format(dt, "yy")
sql1 = "SELECT [Date Received], [Approval Received], [Design_Time_Calculation] FROM [Tool Order Log] WHERE [Die No] <> 0 AND [Tool ordered] = #" & dtString & "#"
sql2 = "SELECT [Tool ordered], [Section No], [Die No], [Press], [Apertures], [Weld plate], [Expansion], [Die], [Porthole], [Backer], [Bolster], [Designer].[Designer], [tblToolmaker].[Toolmaker], [Date due], [Held / Comments], [Backers], [Die Holder], [Bolsters], [Bolster Insert], [Cep / Exp], [Date Approved], [Layout Drawing Status], [CQI Design Status], [Special Requirements], [Speed], [Billet Temperature], [Design_Time_Calculation] FROM [Tool Order Log], [Designer], [tblToolmaker] WHERE [Designer].ID=[Tool Order Log].Designer AND [tblToolmaker].ID=[Tool Order Log].[Toolmaker] AND [Die No] <> 0 AND [Tool ordered] = #" & dtString & "#"
Set rs = db.OpenRecordset(sql1, dbOpenDynaset)
If rs.EOF = False And rs.BOF = False Then
Do Until .EOF
'avgDay = IIf((DateDiff("ww", rs("[Approval received]"), rs("[Date Received]"), 2) < 1) And (DateDiff("ww", rs("[Approval received]"), rs("[Date Received]"), 2) > -1), rs("[Date Received]") - rs("[Approval received]"), (rs("[Date Received]") - rs("[Approval received]")) - DateDiff("ww", rs("[Approval received]"), rs("[Date Received]"), 2) * 2)
!Design_Time_Calculation = avgDay
'DoCmd.RunMacro "Tooling Requisition"
Else: MsgBox "empty"
your hand must be hurting. First thing I would try is to create an actual error on the on error statement. If the program errors out at least you can get a description and and error number
on error goto errorsection
msgbox(err.description & " " & err.number)
The next thing I would do is to take the sql statements and run them thru the view window get the variable for sql 1 copy this text and then go into a query and create the sql statement and run the query. Make sure your statement is set up correctly. I would do this for the sql 2 line item also.
Also when you are writing to the drive make sure the xls file does not exist already. I know when I have done exports thru macros I would get a prompt to delete the file if it exists. I have not done this thru code that much . My suspicion is there is a flag that checks this. If it is not se tup correctly to the proper value this could be failing without you knowing.