After Update/Change Events Not Working

[hhzyousafi]

I am trying to update values in an unbound text box based on a selection in the combo box on the same form; however whilst I am easily able to display the information for the first selection the values do not update when I change the value in the drop down. What is going on here!?

 

[pbaldy]

What is your code, and where exactly is it?

 

[hhzyousafi]

Hi Paul,

The code I am using is as follows:

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)

If Len(cboEmployeeID.Value) <> 0 And DCount("EmployeeID", "tblAccessRights", "EmployeeID = '" & cboEmployeeID.Value & "'") = 0 Then
imgGreenCheckcboEmployeeID.Visible = True
imgRedCrosscboEmployeeID.Visible = False
Else
imgGreenCheckcboEmployeeID.Visible = False
imgRedCrosscboEmployeeID.Visible = True
MsgBox "Please note that is an invalid entry for one the following reasons:" & vbNewLine & vbNewLine _
& "*This field cannot be left empty." & vbNewLine _
& "*That Employee ID already has access rights applied to it." & vbNewLine & vbNewLine _
& "Please make the necessary corrections and try again.", vbCritical
End If

End Sub

Private Sub cboEmployeeID_AfterUpdate()

txtWindowsNTUserID.Undo
txtWindowsNTUserID.Value = DLookup("WindowsNTUserID", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtFirstName.Undo
txtFirstName.Value = DLookup("FirstName", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtLastName.Undo
txtLastName.Value = DLookup("LastName", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtDepartment.Undo
txtDepartment.Value = DLookup("Department", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtDesignation.Undo
txtDesignation.Value = DLookup("Designation", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

Dim UpdatedOn As Date
Dim UpdatedBy As String

UpdatedOn = Now()
txtUpdatedOn.Value = UpdatedOn
UpdatedBy = fOSUserName()
txtUpdatedBy.Value = UpdatedBy

End Sub

Private Sub cboEmployeeID_Change()

txtWindowsNTUserID.Undo
txtWindowsNTUserID.Value = DLookup("WindowsNTUserID", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtFirstName.Undo
txtFirstName.Value = DLookup("FirstName", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtLastName.Undo
txtLastName.Value = DLookup("LastName", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtDepartment.Undo
txtDepartment.Value = DLookup("Department", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

txtDesignation.Undo
txtDesignation.Value = DLookup("Designation", "tblBasicEmployeeInformation", "cboEmployeeID.Value")

Dim UpdatedOn As Date
Dim UpdatedBy As String

UpdatedOn = Now()
txtUpdatedOn.Value = UpdatedOn
UpdatedBy = fOSUserName()
txtUpdatedBy.Value = UpdatedBy

End Sub​

I have a combo box named cboEmployeeID and I have put code in the combo box's BeforeUpdate, AfterUpdate, and Change events. The BeforeUpdate is simply validation whereas the AfterUpdate and the Change events are the events I want triggered each and every single time I make a selection from the cboEmployeeID. What is currently happening is I am making a selection and the AfterUpdate event triggers absolutely perfectly the first time; however if I go back and change the selection my understanding, based on my code, is that the unbound text boxes I have will automatically update their values but that is what is not happening. I hope that clarifies what I am looking to do.

Regards,

Zohair Yousafi

 

[pr2-eugin]

Try this code..

Private Sub cboEmployeeID_BeforeUpdate(Cancel As Integer)
    If Me.cboEmployeeID.ListIndex <> -1 And DCount("EmployeeID", "tblAccessRights", "EmployeeID = '" & Nz(Me.cboEmployeeID,0) & "'") = 0 Then
   [COLOR=SeaGreen] 'If Me.cboEmployeeID.ListIndex <> -1 And DCount("EmployeeID", "tblAccessRights", "EmployeeID = " & Nz(Me.cboEmployeeID,0)) = 0 Then[/COLOR]
        imgGreenCheckcboEmployeeID.Visible = True
        imgRedCrosscboEmployeeID.Visible = False
    Else
        imgGreenCheckcboEmployeeID.Visible = False
        imgRedCrosscboEmployeeID.Visible = True
        MsgBox "Please note that is an invalid entry for one the following reasons:" & vbNewLine & vbNewLine _
                & "*This field cannot be left empty." & vbNewLine _
                & "*That Employee ID already has access rights applied to it." & vbNewLine & vbNewLine _
                & "Please make the necessary corrections and try again.", vbCritical
    End If
End Sub

Private Sub cboEmployeeID_AfterUpdate()
    Dim rsObj As DAO.Recordset
    
    Set rsObj = CurrentDB.OpenRecordset("SELECT WindowsNTUserID, FirstName, LastName, Department, Designation " & _
                                        "FROM tblBasicEmployeeInformation WHERE EmployeeID = '" & Me.cboEmployeeID & "'")
    
    [COLOR=SeaGreen]'Set rsObj = CurrentDB.OpenRecordset("SELECT WindowsNTUserID, FirstName, LastName, Department, Designation " & _
                                        "FROM tblBasicEmployeeInformation WHERE EmployeeID = " & Me.cboEmployeeID)[/COLOR]
    If rsObj.RecordCount <> 0 Then
        Me.txtWindowsNTUserID = rsObj.Fields("WindowsNTUserID")
        Me.txtFirstName = rsObj.Fields("FirstName")
        Me.txtLastName = rsObj.Fields("LastName")
        Me.txtDepartment = rsObj.Fields("Department")
        Me.txtDesignation = rsObj.Fields("Designation")
    End IF
    
    Me.txtUpdatedOn = Now()
    Me.txtUpdatedBy = fOSUserName()
    Set rsObj = Nothing
End Sub

Is your ComboBox for EmployeeID actually returning String? If not, or is returning the Number try the commented Code in the above code.

 

[hhzyousafi]

Hi Paul,

Thank you very much for the quick response. That worked like a charm. If it wouldn't be too much trouble could you actually tell me what you did and what I did wrong in the first place? You wrote less code and got the desired result whereas I feel I use too much code and I was getting nowhere. Yes the combo boxes are actually returning string - is that a bad thing? The application I have developed is now quite large and I have been using this as a standard throughout the application.

Regards,

Zohair Yousafi

 

[pr2-eugin]

Glad you have it working.

If it wouldn't be too much trouble could you actually tell me what you did and what I did wrong in the first place?

Sure, not a problem. The main bits of problem you had were, your DLookup was wrong.. You had,

DLookup("someField", "tblBasicEmployeeInformation", "[B]cboEmployeeID.Value[/B]")

You missed the Field of criteria you are trying to refer to.. It should have been..

DLookup("someField", "tblBasicEmployeeInformation", "EmployeeID = '" & cboEmployeeID.Value & "'")

You could have gone ahead with the same strucutre of using multiple DLookup's but what you have to understand is that any Domain function is a simplified Query, in other words if you use DLookup once, you are Querying the whole table. So 5 DLookup means querying the table 5 times. When you use a Recordset, you only Query the table once and still get the same result. If you want to learn about using Recordset look into : Recordsets for Beginners

I feel I use too much code and I was getting nowhere.

Its all about efficieny and optimisation of Code, similary you do not need to declare variables for updating txtUpdatedOn or txtUpdatedBy, they are not required.

Yes the combo boxes are actually returning string - is that a bad thing?

Not exactly a bad thing, but your namin convention suggest otherwise. Any normal user would imagine the EmployeeID to be a Number as it is an ID. If you stick to a goon naming convention it would be easy to help you out. MS Access Naming Conventions.

Hope this helps !

 

[hhzyousafi]

Paul sir you are a gem! Thank you for those pointers. I have never taken proper training and actually, by profession, I am not a developer; however I have been putting applications together in Access for my teams for the past 4 years. Whilst I feel the applications have been extremely helpful pointers, such as yours, as very important as they will help take those applications to the next level and optimize efficiency. I really and truly appreciate it!

 

[pr2-eugin]

Most Welcome. I am glad to be of help !