Public Sub AppendSPOT(strUserId As String, dteActivityDate As Date)
Dim strDelete As String, strAppendSPOT As String, strUserId As String
Dim strNoSpotData As String, qdfNoSpotData As QueryDef
Set db = CurrentDb
'Selects data from the production database to see if any spot data
'already exists. If it has all been deleted, either by user or system,
'then there should be no data in the EmpStats table either. The row count
'returned for this query satisfies this criteria of rows existing or not
strNoSpotData = "SELECT tblProductionData.UserID, tblProductionData.SystemUserID, " & _
"tblProductionData.ActivityDate, tblProductionData.WFLWID, tblWorkFlows.DSRID " & _
"FROM tblProductionData INNER JOIN tblWorkFlows ON " & _
"tblProductionData.WFLWID = tblWorkFlows.WFLWID " & _
"WHERE tblProductionData.UserID = '" & strUserId & "' AND " & _
"tblProductionData.ActivityDate = #" & dteActivityDate & "# AND " & _
"tblWorkFlows.DSRID = 'DSR3' "
Set qdfNoSpotData = db.CreateQueryDef("qryNoSpotData & strUser & ", strNoSpotData)
Set rs = CurrentDb.OpenRecordset("qryNoSpotData & strUser & ")
If rs.RecordCount > 0 Then
DoCmd.RunSQL strDelete 'For this example, deleted
DoCmd.RunSQL strAppendSPOT 'For this example, deleted
Else
DoCmd.RunSQL strDelete 'For this example, deleted
End If
DoCmd.DeleteObject acQuery, ("qryNoSpotData & strUser & ")
Set rs = Nothing
Set qdfNoSpotData = Nothing
Set db = Nothing
End Sub
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.