D
David
To clarify the question about referencing checkboxes - I
have loaded a SQL query onto a recordset, and based on the
number of duplicate records, I want to update a
value/column from the query.
Maybe I need to find a different approach - like coding
inside the checkbox or something. Here is the code in the
form's load section:
Private Sub Form_Load()
Dim db As Database
Dim qdf As queryDef
Dim rs As Recordset
Dim OldKey As String
Dim NewKey As String
Dim SQLCall As String
SQLCall = "SELECT [tblLeave].[IsDuplicate], [tblLeave].
[EmployeeID], " _
& "[tblLeave].[DateBeginning], [tblLeave].
[DateEnding], " _
& "[tblLeave].[NumberOfHours], [tblLeave].[UnionID], "
_
& "[tblLeave].[Purpose], [tblLeave].[Agency],
[tblEmployee].[LastName], " _
& "[tblEmployee].[FirstName] FROM [tblEmployee] INNER
JOIN [tblLeave]" & _
"ON [tblEmployee].[EmployeeID] = [tblLeave].
[EmployeeID]" & _
"WHERE ((([tblLeave].[EmployeeID]) In (SELECT
[EmployeeID]" & _
"FROM [tblLeave] As Tmp GROUP BY [EmployeeID],
[DateBeginning], " _
& "[DateEnding],[NumberOfHours] HAVING Count(*)>1" & _
"And [DateBeginning] = [tblLeave].[DateBeginning]" & _
"And [DateEnding] = [tblLeave].[DateEnding]" & _
"And [NumberOfHours] = [tblLeave].[NumberOfHours])))"
& _
"ORDER BY [tblLeave].[EmployeeID], [tblLeave].
[DateBeginning], " _
& "[tblLeave].[DateEnding], [tblLeave].[NumberOfHours]"
'this will read data from the current database UNION into
variable rs
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryDuplicate", SQLCall)
With qdf
Set rs = .OpenRecordset(dbOpenDynamic)
End With
rs.MoveFirst
NewKey = rs.Fields("EmployeeID").Value & "; " & rs.Fields
("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
If rs.RecordCount = 1 Then
OldKey = " "
End If
'compare each record of the table to OldKey
Do While Not rs.EOF
'if oldkey = newkey, then it is a duplicate record, check
the duplicates ONLY, and then
'make oldkey the next record to compare to next record
If OldKey = NewKey Then
With rs
'rs.Edit
rs.Fields("IsDuplicate").Value = True
'rs.Update ("IsDuplicate")
End With
'DoCmd.RunSQL "UPDATE [tblLeave] SET [IsDuplicate] = TRUE"
OldKey = NewKey
rs.MoveNext
If rs.EOF = True Then
Exit Sub
Else
NewKey = rs.Fields("EmployeeID").Value & "; "
& rs.Fields("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
End If
'if oldkey does not equal newkey, then it is not a
duplicate record and must check with
'the rest of the records in the table
Else
OldKey = NewKey
rs.MoveNext
If rs.EOF = True Then
Exit Sub
Else
NewKey = rs.Fields("EmployeeID").Value & "; "
& rs.Fields("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
End If
End If
Loop
End Sub
Any ideas?
have loaded a SQL query onto a recordset, and based on the
number of duplicate records, I want to update a
value/column from the query.
Maybe I need to find a different approach - like coding
inside the checkbox or something. Here is the code in the
form's load section:
Private Sub Form_Load()
Dim db As Database
Dim qdf As queryDef
Dim rs As Recordset
Dim OldKey As String
Dim NewKey As String
Dim SQLCall As String
SQLCall = "SELECT [tblLeave].[IsDuplicate], [tblLeave].
[EmployeeID], " _
& "[tblLeave].[DateBeginning], [tblLeave].
[DateEnding], " _
& "[tblLeave].[NumberOfHours], [tblLeave].[UnionID], "
_
& "[tblLeave].[Purpose], [tblLeave].[Agency],
[tblEmployee].[LastName], " _
& "[tblEmployee].[FirstName] FROM [tblEmployee] INNER
JOIN [tblLeave]" & _
"ON [tblEmployee].[EmployeeID] = [tblLeave].
[EmployeeID]" & _
"WHERE ((([tblLeave].[EmployeeID]) In (SELECT
[EmployeeID]" & _
"FROM [tblLeave] As Tmp GROUP BY [EmployeeID],
[DateBeginning], " _
& "[DateEnding],[NumberOfHours] HAVING Count(*)>1" & _
"And [DateBeginning] = [tblLeave].[DateBeginning]" & _
"And [DateEnding] = [tblLeave].[DateEnding]" & _
"And [NumberOfHours] = [tblLeave].[NumberOfHours])))"
& _
"ORDER BY [tblLeave].[EmployeeID], [tblLeave].
[DateBeginning], " _
& "[tblLeave].[DateEnding], [tblLeave].[NumberOfHours]"
'this will read data from the current database UNION into
variable rs
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryDuplicate", SQLCall)
With qdf
Set rs = .OpenRecordset(dbOpenDynamic)
End With
rs.MoveFirst
NewKey = rs.Fields("EmployeeID").Value & "; " & rs.Fields
("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
If rs.RecordCount = 1 Then
OldKey = " "
End If
'compare each record of the table to OldKey
Do While Not rs.EOF
'if oldkey = newkey, then it is a duplicate record, check
the duplicates ONLY, and then
'make oldkey the next record to compare to next record
If OldKey = NewKey Then
With rs
'rs.Edit
rs.Fields("IsDuplicate").Value = True
'rs.Update ("IsDuplicate")
End With
'DoCmd.RunSQL "UPDATE [tblLeave] SET [IsDuplicate] = TRUE"
OldKey = NewKey
rs.MoveNext
If rs.EOF = True Then
Exit Sub
Else
NewKey = rs.Fields("EmployeeID").Value & "; "
& rs.Fields("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
End If
'if oldkey does not equal newkey, then it is not a
duplicate record and must check with
'the rest of the records in the table
Else
OldKey = NewKey
rs.MoveNext
If rs.EOF = True Then
Exit Sub
Else
NewKey = rs.Fields("EmployeeID").Value & "; "
& rs.Fields("DateBeginning").Value & _
"; " & rs.Fields("DateEnding").Value & "; " & rs.Fields
("NumberOfHours")
End If
End If
Loop
End Sub
Any ideas?