Hiding Combobox in a form

[james28]

Hello!


I am trying to hide 2 comboboxes in a form.


I have 3 comboboxes on a form. "ComboboxOptions", "ComboboxSubOption1" and "ComboboxSubOption2".

"ComboboxOptions" has 3 values. "All", "Cat", and "Dog". "ComboboxOptions" is always supposed to remain visible.


When "ComboboxOptions" = "All", I would like for the other two comboboxes to be hidden.
When "ComboboxOptions" = "Cat", I would like "ComboboxSubOption1" to be visible (in additional to "ComboboxOptions").
When "ComboboxOptions" = "Dog", I would like "ComboboxSubOption2" to be visible (in additional to "ComboboxOptions").

When you change between "All", "Cat", and "Dog", the comboboxes should appear and disappear as described above.


I have been able to get some what close to what I am after. By default, "ComboboxOptions" is set to "All", so I have set "Visible" to "No" in the propertysheets of "ComboboxSubOption1" and "ComboboxSubOption2".

I then right clicked on "ComboboxOptions" and went to "build event", then "code builder".

I have the following code:

Private Sub ComboboxOptions_AfterUpdate()
If Me.ComboboxOptions = "All" Then
Me.ComboboxSubOption1.Visible = False
Else
Me.ComboboxSubOption1.Visible = True
End If


End Sub

This sort of works for what I am trying to accomplish. When the form is first loaded, the suboption comboboxes are already hidden. When I change "ComboboxOptions" to anything other than "All", "ComboboxSubOption1" becomes visible.; however, if I change it back to "All", it does not become hidden again for some reason.



This leaves me with 3 problems:

1) Right now, "ComboboxSubOption1" shows whether I select "Cat" or "Dog", when it should only show if I select "Cat". Do I need to add in an Else If f some kind? I tried to add one in, but it gave an error.


2) How do I make sure that the rules stay in place the entire time? If I change the "ComboboxOptions" more then once, the rules need to still take effect.

3) How do I add in code for my second Suboption combobox? Do I just add another section that starts with Private Sub ComboboxOptions_AfterUpdate()?




Thank you for any help that you can provide!
James

[June7]

Is this a report or a form - you reference both.

Are these unbound controls in form header?

Private Sub ComboboxOptions_AfterUpdate()
Me.ComboboxSubOption1.Visible = Me.ComboboxOptions = "Cat"
Me.ComboboxSubOption2.Visible = Me.ComboboxOptions = "Dog"
End Sub

[james28]

June,

Thanks! Sorry, my head is getting a bit jumbled. They are in a form. I have edited my original post.


As for your question, I am not entirely sure how to see if they are unbound or not. I think that they are. I created a blank form and drew 3 comboboxes. I then manually added the contents of each box.


From what I can tell from Design view of the form, they appear to be in the "detail" area of the form and not the header. I do not even see a header listed for the form.

Thanks!

[June7]

If the control displays UNBOUND in design view then it is not bound (no ControlSource).

This will not work with the controls in Detail section if the form is Continuous or Datasheet. Setting property of control will be reflected in all records because there is only one control.

Why do you have 3 comboboxes?

[james28]

June,

I just tried the code that you suggested and it works brilliantly!!!! Thank you!


Would it be possible to have the same "ComboboxOptions" from my "Testform" control a chart that is located in a report named "testreport"?

Right now, I have 2 charts in my "testreport". "GraphDog" and "GraphCat".

The way it works right now is, you use "ComboboxOptions" in "testform" to pick "All", "Cat", or "Dog". You then click a button that says "Run Report", which opens "testreport". Right now, "GraphDog" and "GraphCat" always load.


If "ComboboxOptions" = "All" I would like both to show (as they currently do).

If "ComboboxOptions" = "Cat", I would like only "GraphCat" to show.

Likewise, if "ComboboxOptions"= "Dog", I would like only "GraphDog" to show.



The problem is that I am not sure how to reference outside of the object that I am currently in.



I have tried, however, I get an error saying, "Compile error: Method or data member not found".

This is the code that I tried.


Private Sub ComboboxOptions_AfterUpdate()
Me.ComboboxSubOption1.Visible = Me.ComboboxOptions = "Cat"
Me.ComboboxSubOption2.Visible = Me.ComboboxOptions = "Dog"
Me.GraphDog.Visible = Me.ComboboxOptions = "Dog"
Me.GraphCat.Visible = Me.ComboboxOptions = "Dog"
End Sub



Thank you again!

[james28]

If the control displays UNBOUND in design view then it is not bound (no ControlSource).

This will not work with the controls in Detail section if the form is Continuous or Datasheet. Setting property of control will be reflected in all records because there is only one control.

Why do you have 3 comboboxes?

June,

Hmm, that is a fairly obvious one. I am surprised I missed the giant "UNBOUND" text on all of my boxes. Yes, they do appear to be unbound.

I am using the form as a way to control the queries that are being used in the report. I have 3 because that is the level of detail that will ultimately be needed to filter out everything.

For example, when a user selects "Cat", the combobox that loads shows types of cats. That value is then used as criteria for a query. The form does not open the query directly, but instead opens a report that has a chart on it, that was created by the query.



I am not sure if this is the best way to do it, it seems to be working at this point. All I have left is trying to figure out how to hide the charts that are not needed in the report.

Thanks!

[June7]

Me. is an alias reference for the form or report the code is behind. Since the graphs are not on the form, they cannot be found and therefore the error.

Code to control visibility of the graphs would have to be in the Format event of whatever report section the graphs are in. One method:

Me.GraphDog.Visible = Forms!formname.ComboboxOptions = "Dog" Or Forms!formname.ComboboxOptions = "All"
Me.GraphCat.Visible = Forms!formname.ComboboxOptions = "Cat" Or Forms!formname.ComboboxOptions = "All"

[james28]

June,

Thank you for the reply.


I think I follow, but it did not seem to do anything.


I went to my report, then design viewer. I found the section that said Detail and clicked on it. Then in its property sheet, I went to "On Format" and clicked the blank area. It gave me a pop up, so I picked "code builder". In code builder, I found "Format" and put in the code. I ended up looking like:


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.GraphDog.Visible = Forms!testform.ComboboxOptions = "Dog" Or Forms!testform.ComboboxOptions = "All"
Me.GraphCat.Visible = Forms!testform.ComboboxOptions = "Cat" Or Forms!testform.ComboboxOptions = "All"
End Sub


I also made sure that in the propertysheet for GraphDog and GraphCat, "visible" was set to "no" for each.


At this time, when I go to the report, neither GraphCat or GraphDog shows, regardless of what the ComboboxOptions is set to.

Did I complete a step incorrectly?


Thanks again!

[June7]

I edited my post perhaps after you read it. The code goes in Format event for whatever section the graphs are located in.

I do the same thing for some graphs in my report so I know it should work.

[james28]

June,

Thank you for the reply. I did indeed read your reply prior to you editing it.

All of my graphs are in the "header" section of my form. They probably should be in the "detail" section of the form, but I could not get the graphs to stop repeating unless I put them in the header.



It does not seem to be working now, but I am not entirely sure that I have edited the right area.


In both of their individual property sheets, I set both GraphDog and GraphCat to Visible = No.

I went to my report, then design viewer. I found the section that said "Page Header" and clicked on it. Then in its property sheet, I went to "On Format" and clicked the blank area. It gave me a pop up, so I picked "code builder". In code builder, I found "Format" and put in the code. I ended up looking like:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Me.GraphDog.Visible = Forms!testform.ComboboxOptions = "Dog" Or Forms!testform.ComboboxOptions = "All"
Me.GraphCat.Visible = Forms!testform.ComboboxOptions = "Cat" Or Forms!testform.ComboboxOptions = "All"
End Sub


I saved it, closed the report and relaunched the form.



At this point, I get no error messages, but neither graph loads.

Thank you again!

[June7]

I forgot to mention that Format event only executes during direct to printer or PrintPreview, not ReportView.

Does the code run? Set a breakpoint.

Do you mean the graphs are in header section of report?

The graphs display property if visibility is yes and there is no code?

The form with the comboboxes is open? Comboboxes have values? Then you open report?

The graphs repeat in the Detail section because there are multiple records. My graphs are all in Detail section but the report is filtered to a single record.

[james28]

I forgot to mention that Format event only executes during direct to printer or PrintPreview, not ReportView.

Rats. You are right. I just checked Print Preview and it is indeed working as expected. Is there any way to make it function in Report View as well? My users will only be printing the report ~20% of the time and viewing in Reportview the other ~80%.

Does the code run? Set a breakpoint.

Yes. The code appears to be working as intended, now that I know of the Printpreview limitation.

Do you mean the graphs are in header section of report?

The graphs display property if visibility is yes and there is no code?

Yes and Yes. Sorry, I know the difference between a form and a report, but for whatever reason, I keep accidentally using them interchangeably. My comboboxes are in my FORM and my graphs are in my REPORT.

The form with the comboboxes is open? Comboboxes have values? Then you open report?

Yes, that is the way that I have been doing it.

The graphs repeat in the Detail section because there are multiple records. My graphs are all in Detail section but the report is filtered to a single record.

Hmmm, that is interesting. Could you elaborate what you mean when you say that the report is filtered to a single record?





Thank you again!

[james28]

June,

I just noticed something interesting... So I have some hope that it will work fully as intended.


When I load the report (in report view) with "All" selected in my "comboboxoptions" both graphs are hidden. When I switch to PrintPreview, both graphs are visible, as they should be. When I close PrintPreview they graphs are still visible.


I still do not know how to make them load as originally intended, but I was able to add a button. I edited the code of the button to be:

Private Sub Command33_Click()
Me.GraphDog.Visible = Forms!testform.ComboboxOptions = "Dog" Or Forms!testform.ComboboxOptions = "All"
Me.GraphCat.Visible = Forms!testform.ComboboxOptions = "Cat" Or Forms!testform.ComboboxOptions = "All"
End Sub




This makes it so that I can make the graphs load without going to PrintPreview. If there is some way to make them load automatically without going to PrintPreview, that would be best, but this method is at least usable for the time being.



Thank you again!!!

[james28]

For anyone following along, I was able to figure it out! I ended up putting it in the Form Open section.


Private Sub Report_Open(Cancel As Integer)
Me.GraphDog.Visible = Forms!testform.ComboboxOptions = "Dog" Or Forms!testform.ComboboxOptions = "All"
Me.GraphCat.Visible = Forms!testform.ComboboxOptions = "Cat" Or Forms!testform.ComboboxOptions = "All"
End Sub




I am still trying to figure out the filter to just a single record in detail, so if anyone has any advice on that, it would be greatly appreciated.



Thanks!

[June7]

Great! Never thought about Open event, probably because my graphs are in Detail section and are dependent on the record.

Report can be filtered by making the RecordSource a dynamic parameterized query or by passing the filter criteria in WHERE CONDITION argument of OpenReport method.