Warn if field already contains data

[TrekBiker]

In a Customer form there's a field for Account Manager.

For any customer record, if an Account Manager has already been assigned I want to warn if someone tries to assign a different Account Manager.

But if the Account Manager field was empty, for example when creating a new customer record, I don't want the warning to appear.

This satisfies the first requirement but incorrectly gives a warning for a new record. So I'm looking for a way of saying if the field starts off empty then go ahead, otherwise warn.

   If Me.AccountManager > 0 Then
        If MsgBox("Sure you want to change the Account Manager?", vbOKCancel + vbExclamation) = vbCancel Then
            Me.Undo
        End If
    End If

 

[MajP]

Check the me.newrecord property as well in you if check.

 

[TrekBiker]

Not quite sure what you mean, sorry. Also, a customer record may have been created in the past but without an Account Manager. Then the Account Manager might be added later.

 

[MajP]

So not only checking a new record, but any time it is empty.
Normally when a field is empty we mean that it is null

If not isnull(Me.accountManager) then
   If Me.AccountManager > 0 Then
        If MsgBox("Sure you want to change the Account Manager?", vbOKCancel + vbExclamation) = vbCancel Then
            Me.Undo
        End If
    End 
end if

A better check than just isnull is this

If not trim(Me.accountManager & " ") = "" then
   If Me.AccountManager > 0 Then
        If MsgBox("Sure you want to change the Account Manager?", vbOKCancel + vbExclamation) = vbCancel Then
            Me.Undo
        End If
    End 
end if

Normally when a field is empty it is null, but it can also appear empty with an empty string "" or just blank spaces. The above handles all although the latter cases are rare.

 

[Andrzejek]

Just a small point – I would re-think the message you display.

"Sure you want to change the Account Manager?" with OK and Cancel to choose from, I would be confused. Plus this sounds to me like a slang and not a professional statement.

I would do something like “Do you want to change the Account Manager?” or "Are you sure you want to change the Account Manager?" with Yes / No

But that may be just me...


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[1DMF]

>> But that may be just me...

Me too

All my interactive questions are written in this style...

If vbYes = MsgBox("This will perform XYZ, are you sure?", vbYesNo) Then
    'Do something      
End If

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music