Hi Tom!
You're most welcome! ![Smile](images/smilies/smile.png)
I will rollback to the previous sample and I'll try to help you on your existing architecture as you wish. I have to reverse the process of movements from "change member's position" to "change position's members", keeping in mind the traps of this path.
A plain describe of this process could be this:
- Find the target-record with the desired organization and desired billet if exists. If not exists, create a new.
- Check if the job has already assigned to a member. If so, leave the user to decide what will happen to the existing member.
- Set the selected member as the staff member of the target-position.
- Set as "vacant" the current position by setting the staff member's field to 1.
Replace the cmdApply_Click() procedure of the sample v.031 with the code that follows and I hope that will work as you wish:
Code:
Private Sub cmdApply_Click()
Dim rs As Recordset
Dim lngNewRec As Long
'Move the selected staff member into a new organization and/or position.
'Assign the current position to a "vacant" member.
With Me
If IsNull(.cboNewOrg) Then
Beep
MsgBox "You have to choose the new organization!", vbInformation
.cboNewOrg.SetFocus
Else
.Dirty = False
On Error GoTo ErrH
Set rs = CurrentDb.OpenRecordset("Q001_SYS_Billets_Extended", dbOpenDynaset)
'Try to find the desired position to move the current staff member.
rs.FindFirst "OrganizationIDfk=" & .cboNewOrg & " AND BilletIDfk=" & Nz(.cboNewRA_BIN, Nz(.BilletIDfk, "Null"))
If rs.NoMatch Then
'Not found. Create a new.
rs.AddNew
rs!OrganizationIDfk = .cboNewOrg
rs!BilletIDfk = Nz(.cboNewRA_BIN, Nz(.BilletIDfk, "Null"))
rs!StaffMemberIDfk = 1
lngNewRec = rs!RecordIDpk
rs.Update
rs.FindFirst "RecordIDpk=" & lngNewRec
Else
If (Not IsNull(rs!StaffMemberIDfk) And rs!StaffMemberIDfk <> 1) Then
'The position has already assigned to other member.
'So, the user has the chance to move her/him to an other position or just overwrite her/him with the selected member.
If MsgBox("This job has already asigned to " & Trim(rs!All_LastName & " " & rs!FirstName) & "." & vbCrLf _
& "Whould like to move her/him to an other position first?", vbQuestion + vbYesNo) = vbYes Then
'Set the target record as current on the form.
Me.cboStaffMembers = rs!StaffMemberIDfk
cboStaffMembers_AfterUpdate
'And cancel the assignment.
GoTo ExitHere
End If
End If
End If
'Edit the existing record.
rs.Edit
'Try to move employee.
rs!StaffMemberIDfk = Me.cboStaffMembers
rs!Record_Modified_Date = Now()
rs.Update
'Movement completed.
'Set the current position as vacant.
Me!StaffMemberIDfk = 1
'Refresh the data on form.
Me.cboStaffMembers.Requery
Form_Load
End If
End With
ExitHere:
On Error Resume Next
rs.Close
Set rs = Nothing
Me.cboNewOrg = Null
Me.cboNewRA_BIN = Null
Exit Sub
ErrH:
MsgBox Err.Description, vbExclamation, "Move Staff Member Error(" & Err & ")"
Resume ExitHere
End Sub
I just need to figure out how to not keep the positions unique vs. duplicating them
I'm sorry, I didn't understand the above.
Do you want to allow duplicates or not? If you do, you have to create a new record into the OBS's table for the current staff member if the target-position is not "vacant" and the user choose "No" at the relevant message.
Cheers,
John
Edit: The code has updated because I had messed up the "AddNew" block.