How to delete a record and assign an existing record in its place

I'm wondering if anyone could help me out here.

Is it possible when deleting a record t开发者_开发知识库o assign all instances of the deleted record to a new record?

For example:

For each project in a time planning database, i can assign a worker as team leader for each project. If there was a duplicate entry for a team leader i would need to delete one of the instances of these details. If you did this you would then be left with empty values for the records assigned to this 'team leader'. This would be a problem.

I appreciate that you would want to nip this in the bud, so to speak, and not allow duplicate entries. However, if you would need to delete a record is it possible to assign a separate record in its place?

Hope that makes sense, if you could help me out it would be greatly appreciated.

Cheers

Noel

Cascade update is a possibility, but probably overkill for something that will only happen occasionally and should not happen at all. I suggest that you run a query to update the relevant records before you delete the team leader:

Update Projects Set TeamLeaderID=123 
Where TeamLeaderID=456

Yes this is possible. It is a matter of knowing, where your worker is referenced, finding all such locations and replacing the reference through a new reference to another worker. Once all references to the old worker have been removed, you can delete the worker.

Option Compare Database

Private Sub test()
Dim db As Database
Dim rs As Recordset
Dim sql As String


Set db = CurrentDb
sql = "select worker_id from worker_ref_table"
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

While Not rs.EOF
    rs.Edit
    rs!worker_id = "new value"
    rs.Update
    rs.MoveNext
Wend

rs.Close

sql = "select worker_id from worker_table"
Set rs = db.OpenRecordset(sql, dbOpenDynaset, dbSeeChanges)

While Not rs.EOF
    rs.delete
Wend

rs.Close

End Sub

See also VB Database Programming for further help on connecting and operating on a database. Please note, that VB and VBA are not the same. You should be able to use the above code inside Access VBA to accomplish this task.