M
martinmike2
Hello,
I am trying to remove an employees SSN from a record while leaving the
rest of the record intact, and then reassign the employee to another
record in the same field based on a query derived from combo boxs. My
code is as follows.
Private Sub cmdAssign_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
varWhere = Me.SSN.Value
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblAMD WHERE" &
varWhere)
rst.Edit
rst!SSN = Null
rst.Update
rst.Close
Set varWhere = Nothing
Set rst = Nothing
If Not IsNothing(Me.cboAUIC) Then
varWhere = "[auic] = '" & Me.cboAUIC & "'"
End If
If Not IsNothing(Me.cboBSC) Then
varWhere = (varWhere + " AND ") & "[bsc] LIKE '" & _
Me.cboBSC & "*'"
End If
If IsNothing(varWhere) Then
MsgBox "All Fields are required.", vbInformation, gstrAppTitle
Exit Sub
End If
Me.Visible = False
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblAMD " & _
"WHERE " & varWhere)
rst.Edit
rst!SSN = gSSN
rst.Update
rst.Close
End Sub
For some reason it isn't setting the "new" record.SSN field to gSSN (a
global variable to store the SSN across forms). It keeps giving me
the duplicate data error. I have the SSN field of the table set the
No Duplicates to avoid having the same person in multiple fields.
Where did I go wrong?
I am trying to remove an employees SSN from a record while leaving the
rest of the record intact, and then reassign the employee to another
record in the same field based on a query derived from combo boxs. My
code is as follows.
Private Sub cmdAssign_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
varWhere = Me.SSN.Value
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblAMD WHERE" &
varWhere)
rst.Edit
rst!SSN = Null
rst.Update
rst.Close
Set varWhere = Nothing
Set rst = Nothing
If Not IsNothing(Me.cboAUIC) Then
varWhere = "[auic] = '" & Me.cboAUIC & "'"
End If
If Not IsNothing(Me.cboBSC) Then
varWhere = (varWhere + " AND ") & "[bsc] LIKE '" & _
Me.cboBSC & "*'"
End If
If IsNothing(varWhere) Then
MsgBox "All Fields are required.", vbInformation, gstrAppTitle
Exit Sub
End If
Me.Visible = False
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblAMD " & _
"WHERE " & varWhere)
rst.Edit
rst!SSN = gSSN
rst.Update
rst.Close
End Sub
For some reason it isn't setting the "new" record.SSN field to gSSN (a
global variable to store the SSN across forms). It keeps giving me
the duplicate data error. I have the SSN field of the table set the
No Duplicates to avoid having the same person in multiple fields.
Where did I go wrong?