Deafulting One Form field based on Another

[Jim McColl]

Greetings - In a form which we use for maintaining a membership database, we
have a field related to the member's willingness to receive information via
email rather than postal mail. The values are: "Yes", "No", "Email
Problem", "No Email Address" and "No Response"

When we get a new member with an email address, I want the field value to
default to "Yes" (The actual table value is 1). However if the email address
value still contains a null value after the form has been created, the value
should be set to "No Email Address" (Value 5).

Can anybody suggest an easy way to do this?

Thanks very much

 

[Albert D.Kallal]

Just use the forms before update event...

if isnull(emailaddress) = true then

me.myOptions = 5

end if

 

[Jim McColl]

Just use the forms before update event...

if isnull(emailaddress) = true then

me.myOptions = 5

end if


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

Thanks very much Albert. I am a bit of a dunce however when it come to some
of the more technical aspects of the product, so perhaps you could clarify
your answer for me a bit as follows:

1. I have already assigned the form's befor update event to a macro which
autmomatically enters the curent date in the date updated field. Perhaps I
could move this nacro to the After Update event??

2. I assume the example you gave is an event procedure which is actually a
small VB routine - correct ?

If so here is what I end up with:

Private Sub Combo196_BeforeUpdate(Cancel As Integer)
If IsNull(emailaddress) = True Then

Me.myOptions = 5

End If
End Sub

3. Now assuming (because it is true) that the database is called Test BPG
and the form is also called Test BPG and the field I want update is called
Electronic Newsletter; what is the actual form that the Me.MyOptions
statement would would take.

Sorry to be such a pest and thanks again

 

[Albert D.Kallal]

Gee, my newsreader missed this...

Thanks very much Albert. I am a bit of a dunce however when it come to
some of the more technical aspects of the product, so perhaps you could
clarify your answer for me a bit as follows:

1. I have already assigned the form's befor update event to a macro which
autmomatically enters the curent date in the date updated field. Perhaps I
could move this nacro to the After Update event??

Actalllly, just add some more macor code to the above maco. Or, simply
dump the macro, and add both your date update my suggestion to the one
routine. (macors are great, but when it comes to conditonal sutff, they
are not so good).

So, just put in

me.DateUpatedField = date

if isnull(me.emailaddress) = true then

me.myOptions = 5

end if

And, you can add even more things to this before update event over time.

And, of couse, in the above, I only guessing your field names...you will
have to correct them.

If so here is what I end up with:

Private Sub Combo196_BeforeUpdate(Cancel As Integer)

Hum, you could use the combo AFTER update (no need to messs with things
BEFORE the combo gets updated.

So, yes, you could be the code in the combo box after update event, but what
happens if hte user does NOT touch, or enter anything into the combo box?
That is also why we used the forms before update event.

3. Now assuming (because it is true) that the database is called Test BPG
and the form is also called Test BPG and the field I want update is called
Electronic Newsletter; what is the actual form that the Me.MyOptions
statement would would take.

me![Electronic Newsletter] = 5

 

[Jim McColl]

Gee, my newsreader missed this...

Thanks very much Albert. I am a bit of a dunce however when it come to
some of the more technical aspects of the product, so perhaps you could
clarify your answer for me a bit as follows:

1. I have already assigned the form's befor update event to a macro which
autmomatically enters the curent date in the date updated field. Perhaps
I could move this nacro to the After Update event??

Actalllly, just add some more macor code to the above maco. Or, simply
dump the macro, and add both your date update my suggestion to the one
routine. (macors are great, but when it comes to conditonal sutff, they
are not so good).

So, just put in

me.DateUpatedField = date

if isnull(me.emailaddress) = true then

me.myOptions = 5

end if

And, you can add even more things to this before update event over time.

And, of couse, in the above, I only guessing your field names...you will
have to correct them.

If so here is what I end up with:

Private Sub Combo196_BeforeUpdate(Cancel As Integer)

Hum, you could use the combo AFTER update (no need to messs with things
BEFORE the combo gets updated.

So, yes, you could be the code in the combo box after update event, but
what happens if hte user does NOT touch, or enter anything into the combo
box? That is also why we used the forms before update event.

3. Now assuming (because it is true) that the database is called Test BPG
and the form is also called Test BPG and the field I want update is
called Electronic Newsletter; what is the actual form that the
Me.MyOptions statement would would take.

me![Electronic Newsletter] = 5


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

Thanks Albert - There is a nice little featue where you can convert your
macros to event procedures. I did this and after a litle playing around I
got it working. as part of the same procedure with the form before update
function

I am totally self taught on Access and do not know SQL or VB, so I am
learning bits and pieces here and there. It's a lot of fun and quite
satisfying when you tackle something and get it to work. I find the
resources available on the internet through people like yourself just
amazing

Regards & thanks again


Thanks again for you help.