VB Code to change Access Form field visibility for that record only

[sharsy]

Hello , I am using MS Access 2003. I have two Tables called 'Members'
and 'CRM Database', which are related via the same memberhsip number.
Basically, the CRM Database records all marketing calls made to
members.

I have created a form for Members, with a sub-form for the CRM
Database. With the CRM Database Sub-form, it displays all of the calls
made to the member. I have tried to implement some VB code so that
when a user chooses a specific drop-down option (Renewal) from a field
(Reason) for a call (an individal record), that another field (Renewal
Outcome) is displayed (and vice versa).

Here is the code I have setup:

If Me.Reason = "Renewal" Then
Me.Renewal_Outcome.Visible = True
Else
Me.Renewal_Outcome.Visible = False
End If

My problem is that when a user chooses a different drop-down option
(one that isn't Renewal) from the 'Reason' field, the 'Renewal
Outcome' field is hidden on all of the calls (even if previous calls
have been a 'Renewal' call and they have a 'Renewal Outcome' field
entry). The same is also true in reverse.

What I want to know is what code I can use so that the form field
visibility only changes on a record-level (i.e. for that call only).
At the moment, it is changing the field visibility on not only all
calls for that member, but for everyone's calls on the whole database.
This is bad because it makes it confusing for the people trying to
enter data and it also means they cannot see data already entered into
the fields for old calls, which have been hidden based on what was
selected in their latest call.

If someone could please help me that would be fabulous!!!

Thanks

 

[Steve Schapel]

Sharsy,

If we can get the formalities out of the way first, we are talking here
about VBA code, not VB code. Which in Access is quite different from a
macro.

And now, the bad news is that what you are trying to do is not possible.
Changing these properties of a control on a contunuous view form necessarily
changes it for all records, and there's nothing you can do about that.

The only way to get something similar, as far as I am aware, is to use
Conditional Formatting. You can use this to set the format of the Renewal
Outcome control such that the back colour and fore colour are the same as
each other, and the same as the background of the form section, so that it
is not seen, which I guess is pretty similar to not visible!