MS Access Form events, VBA

Question

I have 3 fields in an access form.

Field 1 - is a text field pre-populated with some data. There are no null records
Field 2 - is a text field pre-populated with some data. It can either have a value or be null.
Field 3 - Is a text field. The user will enter information into the field.

There is a rule. If there is a null value in field 2, then field 3 will equal the value from field 1 and so I want it to automatically populate field 3 from field 1. if the value is not null, then the user will populate the field record manually.

I have set up a form load event to:

If Me.field2 = "" Then
   Me.field3 = Me.field1

The problem I am having is, the user may want to change the pre-populated value of field 1 and if the corresponding field 2 record is blank, I want field 3 to be updated with the same value the user changed in field 1. I don't want to have to reload the form all the time for this update to occur. I've tried the above syntax in a After_Update event and a change event, but it's not updating.

Can anyone suggest what I am doing wrong?

Thanks, Mike

One way to go is to put your check mechanism into a little sub, similar to what you have already. Call this sub from form_load() and maybe even from form_current if needed. You can then call it in the field1_AfterUpdate event. — ExternalUse
I'm not sure who entered an answer a few minutes ago, but the 'If (IsNull(Me.DEV_TYPE) Or Me.DEV_TYPE = "") Then' worked for me. It disappeared as an answer, but thanks. — Mike
Although you already have an answer, you could also just check the length rather than testing for null or an empty string: If Len(Me.Field2.Value) = 0 Then — markblandford
@creamyegg - doesn't Len(null) return null ? Maybe: If Len(Nz([Me.Field2.Value],"")) = 0 — Tim Williams

BIBD BIBD · Accepted Answer · 2012-05-02T16:47:32

Mike's already got his answer, but I'll give a fuller answer in the form of an explanation.

The problem here is that you are trying to compare a field that is null to an empty string. It's like you've done

if null = "" then
    'do stuff
end if

The problem is when null is used in a comparison, the result is always null - which causes the if to evaluate to false. You can't even do this:

if not(null) then
    'do stuff
end if

or

if not (null <> "") then
    'do stuff
end if

The conventional solution is to us isnull(x), which evaluates to true if x is null.

As Tim Williams indicate, you can use:

IsNull(x) or x=""

Some might consider the x="" to be redundant if x can only return null for an empty string.

MS Access Form events, VBA

3 Answers