Advertisement
Advertisement
| 02.21.2008 at 08:15AM PST, ID: 23181541 |
|
[x]
Attachment Details
|
||
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66: 67: 68: 69: 70: 71: 72: 73: 74: 75: 76: 77: 78: 79: 80: |
Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim stDocName As String
'new code starts here
Dim xlApp As Object
Dim xlwb As Object
Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Dim rs As DAO.Recordset
Dim strSQL As String
'End New Code
If Me.StartDate = "" Or IsNull(Me.StartDate) Then
MsgBox "You must supply a starting date."
Me.StartDate.SetFocus
Exit Sub
End If
If Me.EndDate = "" Or IsNull(Me.EndDate) Then
MsgBox "You must supply a ending date."
Me.EndDate.SetFocus
Exit Sub
End If
If Not (IsDate(Me.StartDate)) Then
MsgBox "Starting Date is not in a date format."
Me.StartDate.SetFocus
Exit Sub
End If
If Not (IsDate(Me.EndDate)) Then
MsgBox "Ending Date is not in a date format."
Me.EndDate.SetFocus
Exit Sub
End If
'If Me.Company = "" Or IsNull(Me.Company) Then
'MsgBox "You must select a company."
'Me.Company.SetFocus
'Exit Sub
'End If
stDocName = "rptMSFinfo"
DoCmd.OpenReport stDocName, acPreview
Set db = CurrentDb
'New Code Here
Set db = CurrentDb
strSQL = "Select * from qryMSFinfo;"
Set rs = CurrentDb.OpenRecordset(strSQL)
Set xlApp = CreateObject("Excel.Application")
Set xlwb = xlApp.Workbooks.Open("C:\test\MSF.xls", True)
'This is optional
xlApp.Visible = "True"
'Delete all data in template before moving data in
Set xlws = xlwb.Worksheets("actual")
xlws.Rows("4:65536").ClearContents
xlws.Rows("4:65536").ClearFormats
With xlws
.Range("A4").CopyFromRecordset rs 'Copies the recordset into the worksheet
End With
'End New Code
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
---here is the query that it uses:
SELECT T_EVC_UE.[Event#], T_EVC_UE.[Event Date & Local Time], T_EVC_UE.Site, T_EVC_UE.[Description of Event], T_EVC_UE.[Summary of Investigation], tblManagementSystemFailures.MSFID, tblManagementSystemFailures.MSF, T_EVC_UE.ProcessArea1, T_EVC_UE.[1st Cause], T_EVC_UE.[2nd Cause], T_EVC_UE.[3rd Cause], T_EVC_UE.[Site RE Contact]
FROM T_EVC_UE INNER JOIN tblManagementSystemFailures ON T_EVC_UE.[Event#] = tblManagementSystemFailures.[Event#]
WHERE (((T_EVC_UE.[Event Date & Local Time]) Between GetStartDate() And GetEndDate()) AND ((T_EVC_UE.Site)= Forms!frmMSFRpt!Site Or IsNull(Forms!frmMSFRpt!Site)))
ORDER BY T_EVC_UE.[Event#];
|