Multiple DoCmd to set properties not working

[staciann]

Hi. I have a form with multiple pages and about a hundred or so fields. Based upon the selection of one combo box, I want to make different fields and pages visible or not visible.

I got one of the pages (CurrentResources) to switch from visible to not visible using an onchange macro on the combo box (txtOpportunityStatus) and an oncurrent macro on the actual form (form). However, when I tried to add more controls to show/hide, it would still only work on that first page I put in. Is is possible to add multiple control names to one macro since multiple macros wouldn't work? I'm not sure if it matters, but I have tried making the original 'visible' property of the controls I'd like to change both true and false.

I also tried changing the macros into VBA (I work with a lot of Access forms, but usually don't write much VBA), but I still can't get it to work. I'm not getting any errors so I don't know if my coding is incorrect or if I'm just way off base. The VBA code I tried for the onchange is below (I would just repeat for the oncurrent of the form):

Private Sub txtOpportunityStatus_Change()
On Error GoTo txtOpportunityStatus_Change_Err

    If (txtOpportunityStatus = "Closed") Then
        DoCmd.SetProperty "ContractResources", acPropertyVisible, "-1"
        DoCmd.SetProperty "Contract#", acPropertyVisible, "-1"
        DoCmd.SetProperty "Label174", acPropertyVisible, "-1"
        DoCmd.SetProperty "Solicitation#", acPropertyVisible, "0"
        DoCmd.SetProperty "Label71", acPropertyVisible, "0"
    End If
    
    If (txtOpportunityStatus = "Active") Then
        DoCmd.SetProperty "ContractResources", acPropertyVisible, "-1"
        DoCmd.SetProperty "Contract#", acPropertyVisible, "-1"
        DoCmd.SetProperty "Label174", acPropertyVisible, "-1"
        DoCmd.SetProperty "Solicitation#", acPropertyVisible, "0"
        DoCmd.SetProperty "Label71", acPropertyVisible, "0"
    End If
    
    If (txtOpportunityStatus = "New") Then
        DoCmd.SetProperty "ContractResources", acPropertyVisible, "0"
        DoCmd.SetProperty "Contract#", acPropertyVisible, "0"
        DoCmd.SetProperty "Label174", acPropertyVisible, "0"
        DoCmd.SetProperty "Solicitation#", acPropertyVisible, "-1"
        DoCmd.SetProperty "Label71", acPropertyVisible, "-1"
    End If
    
    If (txtOpportunityStatus = "Pending") Then
        DoCmd.SetProperty "ContractResources", acPropertyVisible, "0"
        DoCmd.SetProperty "Contract#", acPropertyVisible, "0"
        DoCmd.SetProperty "Label174", acPropertyVisible, "0"
        DoCmd.SetProperty "Solicitation#", acPropertyVisible, "-1"
        DoCmd.SetProperty "Label71", acPropertyVisible, "-1"
    End If


txtOpportunityStatus_Change_Exit:
    Exit Sub

txtOpportunityStatus_Change_Err:
    MsgBox Error$
    Resume txtOpportunityStatus_Change_Exit

End Sub

Thanks so much for your help,
Staci

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

I would first change the code to use SELECT CASE rather than multiple IFs.

Public Function StatusChange()
 SELECT CASE Me.txtOpportunityStatus
   Case "Closed"
      Me.ContractResources.Visible = True
      Me.[Contract#].Visible = True
      ' etc
   Case "Active"
      Me.ContractResources.Visible = True
      Me.[Contract#].Visible = True
      ' etc
   Case "New"
      Me.ContractResources.Visible = False
      Me.[Contract#].Visible = False
      ' etc
 ' more cases
   Case Else
      ' etc
 End Select
End Function

Call this function from the On Current event of the form and the On Change of txtOpportunityStatus.

This code assumes the controls are not on subforms and the code will fire if someone changes the status or you move to another record in the form.

The best solution might be to any combination of C A N P into the tag property of these controls with code like:

Public Function StatusChange()
  Dim ctl as Control
  For Each ctl in Me.Controls
    If Len(ctl.tag & "") > 0 Then
       ctl.Visible = Instr(ctl.Tag, Left(Me.txtOpportunityStatus,1))>0
    End If
  Next
End Function

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

It can be shortened to something like

Private Sub txtOpportunityStatus_Change()
On Error GoTo txtOpportunityStatus_Change_Err
   
   dim ctrRes as boolean
   dim ctrNum as boolean
   dim lbl174 as boolean
   dim sol as boolean
   dim lbl71 as boolean
   
   select case  txtOpportunityStatus
      case "Closed", "Active"
        ctrRes = True 
        ctrNum = True
        lbl174 = True
      case "New", "Pending"
        sol = True 
        lbl71 = True
      case else
   end select
   me.controls("ContractResources").visible = ctrRes
   me.controls("Contract#").Visible = ctrNum
   me.controls("Label174").Visible = lbl174
   me.controls("Solicitation#").Visible = sol
   me.controls("Label71").Visible = lbl71 

txtOpportunityStatus_Change_Exit:
    Exit Sub

txtOpportunityStatus_Change_Err:
    MsgBox Error$
    Resume txtOpportunityStatus_Change_Exit

End Sub

 

[staciann]

Thanks for your help guys!
Unfortunately, dhookom, your suggestion did not work for me.

MajP - I got yours running, however, it's giving me the same issue as with the Macros - it is working perfect for the page (ContractResources/ctrRes), but isn't working for any of the text boxes or labels of my fields.

Any theory on why?

Thanks,
Staci

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

Can you tell us what you mean by multiple pages? Are these pages on a tab control? Can you share all your code?

Do you understand that "fields" are columns in tables? If you really mean controls that are bound to fields, you should refer to them as "controls" or "bound controls" or "text boxes".

Again, are there subforms or are all of these controls on the main form?


Duane
Hook'D on Access
MS Access MVP

 

[staciann]

Hi Duane -

I apologize for the confusion. Thanks for sticking with me!

Yes, the pages are tabs. I have (6) tabs including "ContractResources". I know the "fields" are columns in tables - I meant controls. I have a total of 20 tables, but the controls I am referring to ("Solicitation#" and "Contract#") are both text boxes attached to fields in tbl_Solicitations. "Label71" and "Label174" are the labels associated with those two text boxes.

I want to control the visibility of these text boxes and labels based on a selection made in combo box "txtOpportunityStatus". What I want to have happen is if a certain opportunity is set to "Active" or "Closed", I want the contract number to show up (but not the solicitation number); but if it is set to "New" or "Pending", I want the Solicitation number to show up (but not the contract number).

Does that make more sense?

I used MajP's exact code. It makes the tab/page show/hide perfectly - but it doesn't affect the text boxes or labels at all.

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

If you make a text box invisible, its associated/attached label will also be invisible.

I need some clarification of the controls that you want to hide or display. I have mentioned "subform" in both of my replies and you have ignored this. Are these controls truly located on the main form (or on a tab/page) and not on a subform. Or, are they actually names of pages?

What is the actual code you are currently using the partially works?

Does your code generate any errors? Have you compiled your code?

Duane
Hook'D on Access
MS Access MVP

 

[staciann]

Sorry - I do have a few subforms, but these text boxes are on the MAIN form only. The code I have used is below - it is on the onChange event of txtOpportunityStatus and the onCurrent event of the form.

When I went into the DB a minute ago, none of the code now seems to be working (not even to show/hide the page conRes - which was what was working before).

And no, there have not been any errors generated.

txtOpportunityStatus is a combo box on page/tab "Solicitation" which is on the main form

conRes is another page/tab on the main form
con is a text box on page/tab "Solicitation"
lblCon is the label associated with con
sol is a text box on page/tab "Solicitation"
lblSol is the label associated with con

Private Sub txtOpportunityStatus_Change()
On Error GoTo txtOpportunityStatus_Change_Err

Dim conRes As Boolean
Dim con As Boolean
Dim lblCon As Boolean
Dim sol As Boolean
Dim lblSol As Boolean

Select Case txtOpportunityStatus
    Case "Closed", "Active"
        conRes = True
        con = True
        lblCon = True
    Case "New", "Pending"
        sol = True
        lblSol = True
End Select
Me.Controls("ContractResources").Visible = conRes
Me.Controls("Contract#").Visible = con
Me.Controls("Label147").Visible = lblCon
Me.Controls("Solicitation#").Visible = sol
Me.Controls("Label71").Visible = lblSol

txtOpportunityStatus_Change_Exit:
Exit Sub

txtOpportunityStatus_Change_Err:
MsgBox Error$
Resume txtOpportunityStatus_Change_Exit

End Sub

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

Check out faq705-7148.

Since txtOpportunityStatus is actually a combo box, are you sure the bound column is a text value and not a number?

Duane
Hook'D on Access
MS Access MVP

 

[staciann]

Yes. There is only one column and it is text - either "New";"Active";"Pending;"Closed"

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

How far did you get with troubleshooting? We can't do it for you since we can't see your screen.

Duane
Hook'D on Access
MS Access MVP

 

[staciann]

I can't get it to debug. When I pull up the VBA and hit debug (or F5 or F8) it just makes a ding noise and won't do anything. It won't debug any of my other code (that is working) either. Am I doing something wrong or do you think my program is screwed up?

Staci – Using Windows 7, Microsoft Office 2007 & Crystal Reports Version 10

 

[dhookom]

You need to place a breakpoint near the top of you code at the "SELECT CASE" line. Then change the txtOpportunityStatus value which should put you into the debug mode so you can step through your code.

If your code doesn't go into debug mode, I would guess that maybe you haven't enabled VBA code to run in your application.

Duane
Hook'D on Access
MS Access MVP