MUSASHI
11-24-2006, 04:48 PM
Ken helped me with a version of this, but, wondered if I could make another run at it, hopefully with Ken.
I want to execute this module from a worksheet, form button, command button etc..
The temporary worksheet, is just that, it holds the recordset for a moment, then the original sheet, where it is executed from, receives the rst, ie an "update".
With column G, I want to define the status of the employees (5000 plus) as "Current" if the following conditions exist:
a- Termination Date = ""
OR
b- Rehire Date > Termination Date.
Again, with the original worksheet WSOrig
Any help is appreciated.
Thanks
Musashi
Sub GetTimEmp()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'Build SQL String..
sSQL = "SELECT MASTER_PRM_EMPLOYEE.Employee, Employee_Name, Hire_Date, Rehire_Date, Termination_Date, Custom_Sort FROM MASTER_PRM_EMPLOYEE"
Set cnn = New ADODB.Connection
cnn.Open ("Provider=MSDASQL.1;DRIVER={Timberline Data};DBQ=J:\NEW_PSG;UID=dxxx;PWD=mxxx;")
MsgBox "You Are Updating the Employee Data"
Set rst = New ADODB.Recordset
rst.Open sSQL, cnn, adOpenDynamic
' Create the report in a new worksheet
Set WSTemp = Worksheets.Add
'Add Headings
Range("A1:G1").Value = Array("Employee", "Employee_Name", "Hire_Date", "Rehire_Date", "Termination_Date", "Custom_Sort", "Status")
'Copy From Recordset to sheet
Range("A2").CopyFromRecordset rst
'Close the connections
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
'Format the report
FinalRow = Range("A65536").End(xlUp).Row
'If No Records, then shut it down..
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no records"
Exit Sub
End If
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
'Sort the range by last name ascending....
'keeps bombing too, so I removed it.. this will be my next question....
End Sub
I want to execute this module from a worksheet, form button, command button etc..
The temporary worksheet, is just that, it holds the recordset for a moment, then the original sheet, where it is executed from, receives the rst, ie an "update".
With column G, I want to define the status of the employees (5000 plus) as "Current" if the following conditions exist:
a- Termination Date = ""
OR
b- Rehire Date > Termination Date.
Again, with the original worksheet WSOrig
Any help is appreciated.
Thanks
Musashi
Sub GetTimEmp()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'Build SQL String..
sSQL = "SELECT MASTER_PRM_EMPLOYEE.Employee, Employee_Name, Hire_Date, Rehire_Date, Termination_Date, Custom_Sort FROM MASTER_PRM_EMPLOYEE"
Set cnn = New ADODB.Connection
cnn.Open ("Provider=MSDASQL.1;DRIVER={Timberline Data};DBQ=J:\NEW_PSG;UID=dxxx;PWD=mxxx;")
MsgBox "You Are Updating the Employee Data"
Set rst = New ADODB.Recordset
rst.Open sSQL, cnn, adOpenDynamic
' Create the report in a new worksheet
Set WSTemp = Worksheets.Add
'Add Headings
Range("A1:G1").Value = Array("Employee", "Employee_Name", "Hire_Date", "Rehire_Date", "Termination_Date", "Custom_Sort", "Status")
'Copy From Recordset to sheet
Range("A2").CopyFromRecordset rst
'Close the connections
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
'Format the report
FinalRow = Range("A65536").End(xlUp).Row
'If No Records, then shut it down..
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no records"
Exit Sub
End If
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
'Sort the range by last name ascending....
'keeps bombing too, so I removed it.. this will be my next question....
End Sub