Change combo box to original value?

[BonnieG]

Hi everyone

I maintain a large database of employees. I have a form with a combo box (cmb_department) which I can use to select a particular department. I have written an After Update event procedure which updates the relevant fields on the form after I've selected a department - e.g. their new manager's name, phone number, etc. based on my departments table.

Here is my code, which works well:

Private Sub cmb_department_AfterUpdate()
If (Me.is_leaver = "" Or IsNull(Me.is_leaver)) And (Not IsNull(Me.employee_number)) Then
    MsgBox "This member of staff is an internal HEY employee. Their departmental details will not change to that of the department, however they can still be set up with GP Browser access."
    GoTo end_script:
End If
Dim Confirmation As Integer
Confirmation = MsgBox("WARNING!" & vbCrLf & vbCrLf & "You are about to assign this member of staff to a new department. This will overwrite their existing details in the following fields:" & vbCrLf & vbCrLf & Chr(149) & " Phone" & vbCrLf & Chr(149) & " Department" & vbCrLf & Chr(149) & " Site" & vbCrLf & Chr(149) & " Organisation" & vbCrLf & Chr(149) & " Manager" & vbCrLf & vbCrLf & "Do you wish to continue?", vbYesNo, "Do you wish to continue?")
If Confirmation = vbYes Then
    new_department_id = Trim(Replace(Me.cmb_department, vbCrLf, ""))
    If (Me.cmb_department = "" Or IsNull(Me.cmb_department)) Then
        
        MsgBox "You must select a department."
    Else
        Dim MyDB As Database
        Dim MyRS As Recordset
        Dim SQL As String
        Set MyDB = CurrentDb()
    
        SQL = "SELECT name, manager, code, non_trust_address, organisation, phone_number from dbo_departments WHERE department_id = " & new_department_id
        Set MyRS = MyDB.OpenRecordset(SQL)
        Recordset_Empty = False
        
        If MyRS.BOF And MyRS.EOF Then
            Recordset_Empty = True
        End If
        
        Me.Department = MyRS.Fields(0)
        Me.Site = MyRS.Fields(3)
        Me.Manager = MyRS.Fields(1)
        Me.organisation = MyRS.Fields(4)
        Me.ext_no = MyRS.Fields(5)
        Me.txt_manager_verification_timestamp = Date
        Me.is_leaver = Null
        Me.notes = Null
        
    End If
        
    MyRS.Close
    
    Else
        Me.cmb_department = Null
        GoTo end_script:
        
End If
DoCmd.RunCommand acCmdSaveRecord
Forms![frm_new_user].Refresh
end_script:
End Sub

However, I'd like to do two things which I'm currently unable to get working...

1. If I delete the department name from the box, or select a NULL value, I get a VBA error - run time error 94, invalid use of Null. I will sometimes want to select a Null value to "unassign" a person from this particular type of department but I'd like a way of selecting a Null value and having it simply set to Null which if the form's default value for this field. Any ideas?

2. As you can see I have a MsgBox which checks to ensure you want to overwrite the existing data for the member of staff. If I select Yes then it overwrites. If I select No I'd like for it to set the value of cmb_department back to the PREVIOUS value. At the moment my script sets it to NULL which doesn't really work - for example, say someone is currently set to the Sales department, and I accidentally click to change them to the Warehouse department, I'd like clicking "No" to revert them back to Sales...

Any advice gratefully received!

 

[BlueIshDan]

Is this the code you were using when it worked. Or is this the code that is all wonky?

 

[BonnieG]

Sorry, I realise my post sounds confusing. This code is working as in it doesn't give me any weird errors with normal use, only when I try to select Null values or when I try to cancel the change.

 

[BlueIshDan]

Could you tell me which line it is that throws the error?

I'm thinking this line

new_department_id = Trim(Replace(Me.cmb_department, vbCrLf, ""))

Could you breakpoint and step through to let me know?

 

[BonnieG]

Yes it is this line causing the Null error:

new_gp_practice_id = Trim(Replace(Me.cmb_gp_practice, vbCrLf, ""))

 

[gemma-the-husky]

if you have a combo box, then why not simply add extrra columns to the combo box as required.

then you can either push the values

textbox1 = mycombobox.column

or pull the values. control source for textbox1

=mycombobox.column

without all the special coding.