Combo Box Problems

[zashmore]

Hi all,

Thanks to the help previously I have managed to create a Combo Box to select information. I am getting good at this!!!

On my form I have details of customers and my combo box allows me to update their progress. Once updated a new text box appears, which remains not visible, to allow me to input more info.

The problem - once I input the new data into the text box, I want it to stay visible, not disappear when I close the form down.

Here is my code -

Private Sub Referral_Outcome_Code_AfterUpdate()
If Me.[Referral Outcome Code].Value = "No Contact" Then
Me.Label91.Visible = True
Me.WhyNoContact.Visible = True
Me.Label90.Visible = False
Me.WhySupportRefusedbyTenant.Visible = False
Me.Label89.Visible = False
Me.WhySupportRefusedbyAgency.Visible = False
Me.Label88.Visible = False
Me.SupportWorkerName.Visible = False
End If

If Me.[Referral Outcome Code].Value = "Support Refused by Tenant" Then
Me.Label91.Visible = False
Me.WhyNoContact.Visible = False
Me.Label90.Visible = True
Me.WhySupportRefusedbyTenant.Visible = True
Me.Label89.Visible = False
Me.WhySupportRefusedbyAgency.Visible = False
Me.Label88.Visible = False
Me.SupportWorkerName.Visible = False
End If
If Me.[Referral Outcome Code].Value = "Support Refused by Agency" Then
Me.Label91.Visible = False
Me.WhyNoContact.Visible = False
Me.Label90.Visible = False
Me.WhySupportRefusedbyTenant.Visible = False
Me.Label89.Visible = True
Me.WhySupportRefusedbyAgency.Visible = True
Me.Label88.Visible = False
Me.SupportWorkerName.Visible = False
End If
If Me.[Referral Outcome Code].Value = "Support Commenced" Then
Me.Label91.Visible = False
Me.WhyNoContact.Visible = False
Me.Label90.Visible = False
Me.WhySupportRefusedbyTenant.Visible = False
Me.Label89.Visible = False
Me.WhySupportRefusedbyAgency.Visible = False
Me.Label88.Visible = True
Me.SupportWorkerName.Visible = True
End If
End Sub

How do I make it stay on screen once updated??

Thanks

 

[MarkK]

When you create a textbox on a form, it has a label by default. When you set the visibility of the textbox, the visibility of the label is also affected. My guess is you don't need to explicitly set the visibility of all those labels.

But your main issue is you need to trigger this visibility logic more often than just when the Referral Outcome is updated. You also need to apply this logic everytime you load a new record in the form. To do this you encapsulate this logic in a single routine, and then call that routine from the Current event of the form and the AfterUpdate event of the control. Code might look like...

Private Sub Referral_Outcome_Code_AfterUpdate()
   [COLOR="Green"]'set this for any updates to this referral outcome[/COLOR]
   SetControlVisibility
End Sub

Private Sub Form_Current()
   [COLOR="green"]'set this for any new record in the form[/COLOR]
   SetControlVisibility
End Sub
   
Private Sub SetControlVisibility()
   Dim ctlToShow As Access.Control
   [COLOR="green"]'hide all the controls[/COLOR]
   Me.WhyNoContact.Visible = False
   Me.WhySupportRefusedbyTenant.Visible = False
   Me.WhySupportRefusedbyAgency.Visible = False
   Me.SupportWorkerName.Visible = False
   [COLOR="green"]'select the single control that should be visible, given the value of ROC[/COLOR]
   Select Case Me.[Referral Outcome Code]
      Case "No Contact"
         Set ctlToShow = Me.WhyNoContact
      Case "Support Refused By Tenant"
         Set ctlToShow = Me.WhySupportRefusedbyTenant
      Case "Support Refused By Agency"
         Set ctlToShow = Me.WhySupportRefusedbyAgency
      Case "Support Commenced"
         Set ctlToShow = Me.SupportWorkerName
   End Select
   [COLOR="green"]'show a single control as determined above [/COLOR]
   ctlToShow.Visible = True
End Sub

And I've shortened your visibility logic a little.
Cheers,

 

[zashmore]

Still got problems

Thanks for that.

I still have problems. when I input your code and try to run the form an error message appears "run time error '91' Object Variable or With Block Variable not set.

When entering code screen "ctlToShow.Visible = True" is highlighted.

Any ideas??

Cheers

 

[Luddite Lad]

You will need to change the ctlToShow of ctlToShow.Visible to the name of your control that you wish to show.

 

[zashmore]

Confused!!

Sorry, having a dim moment but i'm now very confused!!

 

[zashmore]

I don't know what I'm changing to what. What's the name of the control that I need to change.

Thanks

 

[MarkK]

Try changing...

Dim ctlToShow as Access.Control

...to...

Dim ctlToShow as Access.TextBox

Lemme know...

 

[MarkK]

You could also do this in the Case block...

   Select Case Me.[Referral Outcome Code]
      Case "No Contact"
         Me.WhyNoContact.visible = True
      Case "Support Refused By Tenant"
         Me.WhySupportRefusedbyTenant.visible = True
      ...
   End Select

...and eliminate the whole ctlToShow variable.

 

[zashmore]

Unfortunately not

No, just tried that and same error appears.

Any other ideas or shall I give up and try again!!!

Cheers

 

[zashmore]

Will i still need this bit? -

Private Sub SetControlVisibility()
Dim ctlToShow As Access.TextBox
Me.WhyNoContact.Visible = False
Me.WhySupportRefusedbyTenant.Visible = False
Me.WhySupportRefusedbyAgency.Visible = False
Me.SupportWorkerName.Visible = False

Cheers

 

[MarkK]

Try this:
Make sure that at the very top of your module you see this...

Option Compare Database
Option Explicit

If you don't see Option Explicit, then type it in there, and run your code again. See if you get different errors. There's no reason you should get this error from this code if your variables are explicitly declared.

If this helps, then, in a code window, goto Menu->Tools->Options->Editor tab->Code Settings section and check "Require Variable Declaration" and Option Explicit will appear by default in all new modules.

What might be happening is you've got a typo in a variable name, and without Option Explicit, Access implicity creates the variable, you reference it, and *DOH*: the object is not set.

 

[zashmore]

Thanks For Your Reply

Thanks for replying but nothing seemed to be working so I changed the format completely.
I changed from a combo box to yes/no ticks and this is now the code which works A1 -

Private Sub AgencyRefused_AfterUpdate()
If Me.AgencyRefused = True Then
Me.WhySupportRefusedbyAgency.Visible = True
Else
Me.WhySupportRefusedbyAgency.Visible = False
End If
End Sub
Private Sub CompletedSupport_AfterUpdate()
If Me.CompletedSupport = True Then
Me.SupportCompletionDate.Visible = True
Else
Me.SupportCompletionDate.Visible = False
End If
End Sub
Private Sub Form_Current()
If Me.CompletedSupport = True Then
Me.SupportCompletionDate.Visible = True
Else
Me.SupportCompletionDate.Visible = False
End If
If Me.SupportCommenced = True Then
Me.SupportWorkerName.Visible = True
Else
Me.SupportWorkerName.Visible = False
End If
If Me.NoContact = True Then
Me.WhyNoContact.Visible = True
Else
Me.WhyNoContact.Visible = False
End If
If Me.TenantRefused = True Then
Me.WhySupportRefusedbyTenant.Visible = True
Else
Me.WhySupportRefusedbyTenant.Visible = False
End If
If Me.AgencyRefused = True Then
Me.WhySupportRefusedbyAgency.Visible = True
Else
Me.WhySupportRefusedbyAgency.Visible = False
End If
End Sub
Private Sub NoContact_AfterUpdate()
If Me.NoContact = True Then
Me.WhyNoContact.Visible = True
Else
Me.WhyNoContact.Visible = False
End If
End Sub
Private Sub SupportCommenced_AfterUpdate()
If Me.SupportCommenced = True Then
Me.SupportWorkerName.Visible = True
Else
Me.SupportWorkerName.Visible = False
End If
End Sub
Private Sub TenantRefused_AfterUpdate()
If Me.TenantRefused = True Then
Me.WhySupportRefusedbyTenant.Visible = True
Else
Me.WhySupportRefusedbyTenant.Visible = False
End If
End Sub

Now though I cannot run the report which I created which allowed a calculation broken down into individual outcomes.

How do I run this report now? How do I count yes/no boxes and group into outcomes on the same page?

Thanks