John,
Your comprehensive response is most appreciated. The issues you raise are
important and have been addressed, thanx. I simply want to remove the
InmateId value from the old record (the field does not allow dupes or 0
length strings) so it can be placed in the new record. The following code
generates the standard Access error message that it would create duplicate
values (I am retyping here as I cannot copy and paste or save the module
to a disc):
Private Sub Inmate_Id AfterUpdate()
Dim strSQL as String
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '" &
Me.InmateId & "'")) Then
If MsgBox("Blah blah" & vbNewLine _
& "blah blah.", vbInformation + vbYesNo, "Change Lock") = vbYes
Then
strSQL = "UPDATE tblLockAllocations SET InmateId = '""' " _
& " WHERE InmateId = '" & Me.InmateId & "';"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful"
Else: Me.InmateId.Undo
Me.InmateId.SetFocus
End if
Else: DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
MsgBox "Lock Change Successful."
Me.InmateId.SetFocus
End if
End Sub
I swear this code flew once. Once. I typed a new Id into the field on the
form, got the MsgBox, clicked OK and the new vlaue took. I then checked to
see if the old value was deleted from the old record and indeed it was,
but now I can't get it to work. I must've change something, but I don't
know what. Can you see wahere this would generate the standard Access
error message about duplicate values? It looks to me like the line:
strSQL = "UPDATE tblLockAllocations SET InmateId = '""' " _
is goofing it up since I am not allowing 0 length strings, but I don't
know the correct syntax.
Many thanx for you help,
Ripper
John Nurick said:
Hi Rip,
This could be fairly simple to code, but you need to be sure you
understand all the ramifications.
1) Do you want to (a) delete the existing *record* containing the
specified InmateID or (b) delete the InmateID from that record? If (a),
just build the SQL statement for a delete query, e.g.
Dim strSQL As String
strSQL = "DELETE FROM tblLockAllocations WHERE InmateID='" _
& Me.InmateID & "';"
CurrentDB.Execute strSQL, dbFailOnError
If (b), do the same but with an update query:
strSQL = "UPDATE tblLockAllocations SET InmateID = '' " _
& "WHERE ...
2) If (1b), you now have a record in tblLockAllocations with no
InmateID. Does this matter?
3) Does deleting a record or removing InmateID have any implications
elsewhere in the database?
4) The field name "InmateID" suggests that this database relates to some
sort of correctional or educational institution. If so, your
jurisdiction may have particular regulations concerning data protection,
privacy, data integrity, etc. Be sure that the database complies with
these.
I have code that checks a user input value and presents a msgbox if the
value already exists in another record. The msgbox says that the user
must
delete the value from the other record, then return to this record and
insert it. The code is:
If Not IsNull(DLookup("InmateId", "tblLockAllocations", "[InmateId] = '"
&
Me.InmateId & "'")) Then MsgBox "This ID number already exists in
another. .
."
This simply shows that the value exists in the table somewhere else, but
not
where. I would like to programmatically find it, delete it, then insert
it
into the new record like the user wants, with a msgbox informing the user
what is happening ("If you insert this ID number here, then it will be
removed from the other record"). Would this be difficult? I imagine it
would
involve passing a String value between subs but I don't know VB well
enough
to do it. Can anyone help?
Many thanx,
Rip
