How can I change a form field's visible setting at a RECORD-level only

[gothebroncos]

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_Outc ome.Visible = True
Else
Me.Renewal_Outc ome.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

[TheSmileyCoder]

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_Outc ome.Visible = True
Else
Me.Renewal_Outc ome.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

If you subform is Continues, then thats simply how they work, you can't make record lvl adjustment on continues forms.

IF its not continues, then its a matter of placing the code in the right places. Where is your code currently placed?
Is the dropdown field (reason) placed in the main form or subform?

[gothebroncos]

it turns out i could use conditional formatting on my form to solve this one (disable/enable field)! Thanks though!