CODEWITHSUNDEEP
formsms-accessvba
Mike
Mike

Reputation: 4405

MS Access Form events, VBA

I have 3 fields in an access form.

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

Upvotes: 1

Views: 1639

Answers (3)

Wim van Ede
Wim van Ede

Reputation: 11

This works too:

if Nz(Field1, "") <> "" then 'do null / empty stuff

Upvotes: 1

SeanC
SeanC

Reputation: 15923

another test for empty string or null is

if len(field1 & "")=0 then 'do null stuff

appending an empty string to null or an empty string results in an empty string, which you can then test for length

Upvotes: 3

BIBD
BIBD

Reputation: 15384

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.

Upvotes: 2

Related Questions