I have looked around a lot, and this seems to be an error that can be hard to pinpoint. I am hoping that someone can provide me with some insights as to why I am having this problem. I am new to programming with DAO (used ADO a fair amount), so maybe its an obvious thing I am missing.
I am testing a desktop database. No linked tables, and everything is local. I am attempting to edit records in a code produced dataset with DAO.
I have a top level function that creates two recordsets.1) The fist recordset is created and returned with a private function. I use a QueryDef to create the set, and It combines two local tables. (Works fine)
2) The second recordset is created by another private function, but takes the recordset from step 1) and attempts to manipulate it. The recordset comes over with 4 fields, only one of which is populated. The rest are NULL by default. These NULL values are meant to be updated within this function by referencing another table. It is when I try to alter a record that I receive the cannot update error. I have tried various ways of manipulating the set. First I tried creating a temporary recordset based off the incoming set, then I tried manipulating the set passed by argument directly. I am currently trying to use the .Edit method for updating a record.
Here is all the code, error producing code comes last.
Top Level Function
Code:
Function GetProposedAliases() As Boolean
GetProposedAliases = False
Dim rs1 As DAO.Recordset
Set rs1 = CheckForNewAliases
Dim rs2 As DAO.Recordset
Set rs2 = MaintainEmployeeAliases(rs1, "Originator") 'Error Producing Function
'Return logic not yet completed
End Function
Step 1)
Code:
Private Function CheckForNewAliases() As DAO.Recordset
Set CheckForNewAliases = Nothing
Dim db As DAO.Database
Set db = CurrentDb
Dim sSQL As String
sSQL = "SELECT Deals.Originator, Aliases.Creator, NULL As FirstName, NULL As LastName, NULL As Email " & _
"FROM tbl_Deals AS Deals LEFT JOIN tbl_EmployeeAliases as Aliases " & _
"ON Deals.Originator = Aliases.Creator " & _
"WHERE Deals.Originator Is Not Null AND Deals.Originator <> '' AND Aliases.Creator Is Null " & _
"GROUP BY Deals.Originator, Aliases.Creator;"
Dim qDef As DAO.QueryDef
Set qDef = db.CreateQueryDef("AliasCheck", sSQL)
Set CheckForNewAliases = qDef.OpenRecordset
End Function
Step 2)
Code:
Private Function MaintainEmployeeAliases(AliasComparisonRS As DAO.Recordset, FieldChecked As String) As DAO.Recordset
'Purpose is to compare database editor credentials to a standard list of employee IDs.
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("tbl_EmployeeCredentials")
With AliasComparisonRS
.MoveFirst
Do Until .EOF
Dim CheckedCleared As Boolean
CheckedCleared = False
With rs
.MoveFirst
Do Until .EOF
If CStr(AliasComparisonRS(FieldChecked).Value) = CStr(![EmpNum]) Then
With tempSet
.Edit 'This is where error occurs
.Fields(1).Value = rs![EmpNum]
.Fields(2).Value = rs![FName]
.Fields(3).Value = rs![LName]
.Fields(4).Value = rs![Email]
.Update
End With
CheckedCleared = True
ElseIf CStr(AliasComparisonRS(FieldChecked).Value) = CStr(![LName]) Then
With tempSet
.Edit 'This is where error occurs
.Fields(1).Value = rs![EmpNum]
.Fields(2).Value = rs![FName]
.Fields(3).Value = rs![LName]
.Fields(4).Value = rs![Email]
.Update
End With
CheckedCleared = True
Else
End If
If FieldCheckedCleared Then Exit Do
.MoveNext
Loop
If FieldCheckedCleared = False Then Debug.Print "Not Found: " & AliasComparisonRS(FieldChecked).Value
End With
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Function
Anyone have ideas why I can't edit the records in this set?