"After Update" to recognise blanks

[penfold1992]

i have a dropdown menu which has this code set into "After Update"

Me.iOperatorsName = Me.iOperatorsCode.Column(1)

this works fine for the most part, It shows me what i selected in iOperatorsCode and gives me the 2nd column of the table which is the Operators Name.

however I am trying to correct it slightly...
If I remove the operator number and try to just leave it blank, the iOperatorsName doesnt change. I have tried to include this...

If Me.iOperatorsCode = "" Then
Me.iOperatorsName = ""
End If
Me.iOperatorsName = Me.iOperatorsCode.Column(1)

however this also doesnt pick up when something has been removed in the field. Is there any work around?

 

[cpc]

Try the Nz(Me.iOperatorsCode.Column(1),"") instead of " " for your me.iOperatorsName

 

[Pat Hartman]

"" and Null are different values and with form controls, you need to account for both possibilities. The following code takes advantage of the fact that when you concatenate (with &) a null with a ZLS, you end up with a ZLS.

If Me.iOperatorsCode & "" = "" Then

You could also do:

If Len(Me.iOperatorsCode) = 0 Then

 

[penfold1992]

both of these are not working. I understand what you mean Pat however it still doesnt appear to recognise when I select the cell hit backspace and then hit enter.
If a cell is empty and you hit enter does it not trigger the "After Update" event? as that is what appears to be happening.

 

[pr2-eugin]

If a cell is empty and you hit enter does it not trigger the "After Update" event?

No AfterUpdate is triggered every time after the value has been changed.. Try an If Else structure instead of a normal If..

If Len(Me.iOperatorsCode & vbNullString) = 0 Then
    Me.iOperatorsName = ""
Else
    Me.iOperatorsName = Me.iOperatorsCode.Column(1)
End If

Try and see if it works..

 

[Pat Hartman]

both of these are not working

They work fine. You just need to know where to put them. Validation code in a control's BeforeUpdate event will only work correctly if the control is actually changed triggering the event and if the validation involves only the single field itself. So ---- whenever you have to account for empty values or compare values of multiple controls such as needing to require FromDate to be <= ToDate --- you need to use the Form's BeforeUpdate event.

Don't forget to cancel the event to stop the data from being saved.

If Me.iOperatorsCode & "" = "" Then
    Msgbox "Operators Code is Required.",vbokOnly
    Me.iOperatorsCode.SetFocus
    Cancel = True
    Exit Sub
End If

 

[viperman]

try this code for your project



If IsNull(iOperatorsCode) Then
Me.iOperatorsName = ""
End If
Me.iOperatorsName = Me.iOperatorsCode.Column(1)

 

[penfold1992]

this worked for what I want it to do

no need for cancel because it isnt going in anywhere anyway XD

Private Sub iOperatorsCode_AfterUpdate()
If Len(Me.iOperatorsCode & vbNullString) = 0 Then
    Me.iOperatorsName = ""
Else
    Me.iOperatorsName = Me.iOperatorsCode.Column(1)
End If
End Sub