Macro question, please

  • Thread starter Thread starter Mommio2
  • Start date Start date
M

Mommio2

Hello,
I am working on my very first macro and need some help, please. Here
is what I have:

My screen has fields for Student Addr, Mom Addr, and Dad Addr.

There is also a combo box called Custody with the options M, D, B, G, and O
(mom, dad, both, grandparent, and other).

What I would like to do is:
if M is chosen, what is in Student Address will automatically appear
in Mom Addr.
if D is chosen, what is in Student Addr will automatically appear in
Dad Addr
if B is chosen, Student Addr will appear in both
if G or O is chosen, nothing will appear under Mom and Dad, requiring
manual entry

Is this possible with a macro? I know where to go to create a macro, but
that's about it. Could some kind person please give me some hints?
THANKS!!!
 
Hi (again) Mommio

This would be better in VBA (If [Custody]=â€M†- - Then - -, etc)

But - to use a macro

Create a new text box on your form [txtAddress], save form and close.

Create New Macro
Ensure that “Condition†is visible (if not click the View option)

In the condidtion column set this
[Forms]![NameOfYourForm]![Custody]="M"

In the Action Column set this
Set Value

In the Item Box set this
[Forms]![NameOfYourForm]![txtAddress]

In the Expression Box set this
[Forms]![NameOfYourForm]![1stLineOfAddress] And
[Forms]![NameOfYourForm]![2ndLineOfAddress] And
[Forms]![NameOfYourForm]![3rdLineOfAddress] And
[Forms]![NameOfYourForm]![4thLineOfAddress]

Note if you have stored the address in fields (as above) this will be fine.
If your are displaying the address in a Combo or List you should refer to the
column number.



In the “Next†condidtion column set this
[Forms]![NameOfYourForm]![Custody]="D"

In the Action Column set this
Set Value

In the Item Box set this
[Forms]![NameOfYourForm]![txtAddress]

In the Expression Box set this
[Forms]![NameOfYourForm]![1stLineOfAddress] And
[Forms]![NameOfYourForm]![2ndLineOfAddress] And
[Forms]![NameOfYourForm]![3rdLineOfAddress] And
[Forms]![NameOfYourForm]![4thLineOfAddress]



In the “Next†condidtion column set this
[Forms]![NameOfYourForm]![Custody]="G" Or “Oâ€

In the Action Column set this
Set Value

In the Item Box set this
[Forms]![NameOfYourForm]![txtAddress]

In the Expression Box set this
[Forms]![NameOfYourForm]![1stLineOfAddress] And
[Forms]![NameOfYourForm]![2ndLineOfAddress] And
[Forms]![NameOfYourForm]![3rdLineOfAddress] And
[Forms]![NameOfYourForm]![4thLineOfAddress]



Hope this helps
 
ooops mistake (blond moment)

Just re-read your post - sorry.

With the G or O option where you want to be able to type, set this

[Forms]![NameOfYourForm]![Custody]="G" Or "O"

In the Action Column set this
Set Value

In the Item Box set this
[Forms]![NameOfYourForm]![txtAddress]

In the Expression Box set this
“ â€

This is just 2 "inverted commas"

Sorry about that
 
Wayne-I-M said:
[Forms]![NameOfYourForm]![Custody]="G" Or "O"
Click to expand...

Should be...
[Forms]![NameOfYourForm]![Custody]="G" Or
[Forms]![NameOfYourForm]![Custody] "O"

Well, in fact, all the form qualifiers are not needed, so...
[Custody]="G" Or [Custody] "O"

Alternatively...
[Custody] In("G","O")
In the Item Box set this
[Forms]![NameOfYourForm]![txtAddress]
Click to expand...

What is the purpose of txtAddress? Isn't the purpose here to assign
values to [Mom Addr] and [Dad Addr]?
In the Expression Box set this
""
Click to expand...

But then again, what if the Allow Zero Length property of the field(s)
has been set to No (as it should be!)? In fact, if the requirement is
to not assign any value in the case of "G" and "O", it is probalby not
necessary to even include them in the macro at all?
 
THANKS! I did discover the syntax problems when I tried it. I think you
just answered my next question before I asked it! I got it all to work
except the blanking out the fields if "G" or "O". I will fool around with
the Allow Zero Length property now. What if the field already has
something in it before I change to "G" or "O" and I need to get rid of it
and make it blank? (That is, what if it is not already blank? I would have
to set an expression then, right? Really appreciate everyone's help!


Steve Schapel said:
Wayne-I-M said:
[Forms]![NameOfYourForm]![Custody]="G" Or "O"
Click to expand...

Should be...
[Forms]![NameOfYourForm]![Custody]="G" Or
[Forms]![NameOfYourForm]![Custody] "O"

Well, in fact, all the form qualifiers are not needed, so...
[Custody]="G" Or [Custody] "O"

Alternatively...
[Custody] In("G","O")
In the Item Box set this
[Forms]![NameOfYourForm]![txtAddress]
Click to expand...

What is the purpose of txtAddress? Isn't the purpose here to assign
values to [Mom Addr] and [Dad Addr]?
In the Expression Box set this
""
Click to expand...

But then again, what if the Allow Zero Length property of the field(s) has
been set to No (as it should be!)? In fact, if the requirement is to not
assign any value in the case of "G" and "O", it is probalby not necessary
to even include them in the macro at all?
Click to expand...
 
Mommio,

Ok, if you need to handle the situation of an existing value being
removed in the case of "G" or "O", then in the Expression argument of
the SetValue action put:
Null
 
That works great! Thank you so much for your help! One more question,
please. Is there a way in my macro to check if it is a new record or an
existing record? I may want to do something different if this is the first
time the information is entered on the form as opposed to if it is an old
record which is just being changed.
Mommio2
 
Back
Top