Choose to show/hide control on gerated report

[DaveyEdgar]

Greetings All,

I have been using this....

http://www.tek-tips.com/faqs.cfm?pid=181&fid=5497

Question 1:
Lets say I have a list box with a list of supervisors for me to choose as criteria for the generated report. Is there a way to have a choice in the list box that will show all the records where the supervisor field is blank/null?

Question 2:
Is there a way to have a check box where if it is checked it will show a field/control on the generated report and if it is not checked will hide that field/control?

Thanks a million all!

 

[mhartman1]

Hello:

If you had a form named frmFields and a check box on it named chkHide, and it was checked, The below code would hide a text box control named txtField2 on a report.

Private Sub Report_Open(Cancel As Integer)
If Forms!frmFields!chkHide = True Then
txtField2.Visible = False
End If

End Sub

 

[Remou]

Perhaps something like:

If Me.[i]lstList[/i] = "Blanks" Then
   strWhere = "Trim(Supervisor) & ''=''"
   DoCmd.OpenReport [i]"rptReport"[/i], acViewPreview, , strWhere
Else
   'Call BuildWhere ...
End If

 

[DaveyEdgar]

Wow thanks!!

#1 worked nicely.

For #2 I taylored it to my situation....
In the "Trainer" list box (which is the list of choices/criteria for the "trainer" field/control in the report) I put in the word "blanks" in the value list.

here is the code for the button that runs the report

Private Sub cmdrunperfrpt_Click()

If Me.lstpertrainer = "Blanks" Then
strWhere = "Trim(trainer) & ''=''"
DoCmd.OpenReport "rptperformance", acViewPreview, , strWhere
Else
DoCmd.OpenReport "rptperformance", acViewPreview, , BuildWhere(Me)
End If
End Sub

No records are showing up. I confirmed that there are records with the "trainer" field blank/null.

Any thoughts on why it does this?

 

[Remou]

Do you get any messages? Is trainer text or numeric?
What happens if you put:
strWhere = "trainer is null"

 

[PHV]

What happens if you replace this:
[tt]strWhere = "Trim(trainer) & ''=''"[/tt]
with this ?
[tt]strWhere = "Trim(trainer & '')=''"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DaveyEdgar]

Hi thanks for the help!!

I tried the above suggestions..
...the field is a text field
...there are no errors. The report shows up but with no records to display. For example the "name" control/field says "#Error"

The "BuildWhere (Me)" calls a module I installed from...

http://www.tek-tips.com/faqs.cfm?pid=181&fid=5497

...as mentioned above.

Could it be that the "strWhere" is not calling or running the "BuildWhere (Me)" function/module? I'm sorry but how that code works is a tad beyond me.

I do appreciate the help

 

[Remou]

The idea of the code snippet is to run the buildwhere unless "blanks" is selected.
Name is a very bad name to have on a field or control as it is a reserved word, and a much used reserved word, at that.
To see what is happening you can set a break point, put in messageboxes, or use debug.print. For example, you could put:

Private Sub cmdrunperfrpt_Click()

If Me.lstpertrainer = "Blanks" Then
strWhere = "Trim(trainer) & ''=''"
'For testing
msgbox "strWhere used: " & strWhere
DoCmd.OpenReport "rptperformance", acViewPreview, , strWhere
Else
'For testing
msgbox "BuildWhere used"
DoCmd.OpenReport "rptperformance", acViewPreview, , BuildWhere(Me)
End If
End Sub

 

[DaveyEdgar]

Ah!! Thanks and nice trick...

When I clicked on "Blanks" in the list box and clicked the button to open the report, it said "BuildWhere used" and no records showed up in the report.

When nothing was selected in the list box it said "BuildWhere used" and records did show up in the report.
Pretty slick, but what does it mean?

 

[Remou]

Ok, I do not think that Me.lstpertrainer is returning "blanks". We can test this with another message box:

Private Sub cmdrunperfrpt_Click()
'For testing
msgbox "Me.lstpertrainer: " & Me.lstpertrainer 

If Me.lstpertrainer = "Blanks" Then
  strWhere = "Trim(trainer) & ''=''"
  'For testing
  msgbox "strWhere used: " & strWhere
  DoCmd.OpenReport "rptperformance", acViewPreview, , strWhere
Else
  'For testing
  msgbox "BuildWhere used"
  DoCmd.OpenReport "rptperformance", acViewPreview, , BuildWhere(Me)
End If
End Sub

 

[DaveyEdgar]

Khewl thanks!

When "blanks" is selected in the listbox the first message box says "Me.lstpertrainer:" and the second says "BuildWhere used", and no records show in the report.

When nothing is selected in the listbox, the same messages show up, and all the records are displayed in the report.

Thanks mountains for this by the way I'm learning some khewl tricks!

 

[Remou]

Ok. Let's try this (with apologies to FancyPrairie):

Private Sub cmdrunperfrpt_Click()
Dim strWhere As String
Dim strList As String

If (lstpertrainer.MultiSelect) Then
  For Each varItem In lstpertrainer.ItemsSelected
    strList = strList & " " & lstpertrainer.Column(lstpertrainer.BoundColumn - 1, varItem)
  Next varItem
Else
  strList = lstpertrainer.Column(lstpertrainer.BoundColumn - 1)
End If

'For testing
msgbox "Me.lstpertrainer: " & strList  

If Trim(strList) = "Blanks" Then
  strWhere = "Trim(trainer & '') = ''"
  'For testing
  msgbox "strWhere used: " & strWhere
  DoCmd.OpenReport "rptperformance", acViewPreview, , strWhere
Else
  'For testing
  msgbox "BuildWhere used"
  DoCmd.OpenReport "rptperformance", acViewPreview, , BuildWhere(Me)
End If
End Sub

I have included the bit about "If multiselect" rather than bounce another question back and forth. You can trim it down if you know what type of listbox your have.

Using message boxes can be handy, Debug.Print is also good:
Debug.Print strWhere
The answer appears in the Immediate Window (Ctrl+G).

 

[DaveyEdgar]

Wow thanks!!!

Ok, I chose "Blanks" again from the list box and hit the run report button and got the following messages.
First message:
Me.lstpertrainer: Blanks

Second message:
strWhere Used: Trim(trainer & ")= "

And low and behold the report showed all the records with the "trainer" field blank

When I chose nothing from the list box and hit the button I got the following messages
First message:
Me.lstpertrainer:

Second message:
BuildWhere used

And the report displayed correctly.

This is the greatest thing since sliced bread I can't thank you enough.

Can we sticky this thread or something? I'm sure alot of people who use that BuildWhere module will find this useful.

Hats off!