Deleting a single field of a single record

[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?

 

[Tom van Stiphout]

On Fri, 18 Jul 2008 05:48:43 -0700 (PDT), martinmike2

I stopped reading at your "Set rst" line. It would create a
sqlstatement like:
SELECT * FROM tblAMD WHERE 123-456-7890
which is not a valid sql statement.
Rather try this:
varWhere = "MySsnField='" & Me.SSN.Value & "'"
which would generate a sqlstatement like:
SELECT * FROM tblAMD WHERE MySsnField='123-456-7890'
(of course you have to check the field name, and I am assuming it is a
text field.

-Tom.
Microsoft Access MVP

 

[martinmike2]

On Fri, 18 Jul 2008 05:48:43 -0700 (PDT), martinmike2


I stopped reading at your "Set rst" line. It would create a
sqlstatement like:
SELECT * FROM tblAMD WHERE 123-456-7890
which is not a valid sql statement.
Rather try this:
varWhere = "MySsnField='" & Me.SSN.Value & "'"
which would generate a sqlstatement like:
SELECT * FROM tblAMD WHERE MySsnField='123-456-7890'
(of course you have to check the field name, and I am assuming it is a
text field.

-Tom.
Microsoft Access MVP

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?- Hide quoted text -

- Show quoted text -

sorry about the lond delay. That worked worked. Now my problem is
that it isn't removing the SSN from the "old" record and placing it in
the "new" record.

Here is my code to attempt the update.

varWhere = Me.SSN.Value
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblAMD WHERE
[SSN] = " & varWhere)
rst.Edit
rst!SSN = Null
rst.Update
rst.Close
Set varWhere = Nothing
Set rst = Nothing

 

[John W. Vinson/MVP]

sorry about the lond delay. That worked worked. Now my problem is
that it isn't removing the SSN from the "old" record and placing it in
the "new" record.

You will need two steps to do so - one for each record.

Here is my code to attempt the update.

varWhere = Me.SSN.Value
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblAMD WHERE
[SSN] = " & varWhere)
rst.Edit
rst!SSN = Null
rst.Update
rst.Close
Set varWhere = Nothing
Set rst = Nothing

This replaces the SSN in the record found by the criteria in varWhere
with a NULL value. It doesn't do anything about any "new" record, and
it's not obvious from your post how the "new" record should be
defined. I haven't followed this whole thread and may have missed part
of the discussion.

I'd recommend an action query rather than a recordset action in any
case:

Dim strSQL As String
strSQL = "UPDATE tblAMD SET tblAMD.SSN = Null WHERE [SSN] = '" _
& varWhere & "';"
CurrentDb.Execute strSQL, dbFailOnError

and similarly for the new record (if you're actually creating a new
record in the table use an INSERT query).