By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

How to get desired field data with the help of check boxes?

P: 2
I have a table stored in Access. I want a query form that will contain check boxes for each column in the table.

The tabular report that will be generated must contain those columns which are selected in the form.

can some one please let me know how?
Dec 19 '07 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,494
I'm not sure you appreciate the enormity of the task you ask about. This would involve creating code to handle a redesign of a report. A report is an object designed to show certain data within a specific width.
Even if you were able to hide the unwanted fields, what would the report look like with great gaping spaces?
This is possible however, please indicate if you have the stomach for progressing with this. I don't want to be dealing with basic questions if we go further. This is not beginner level stuff so don't even go there unless you're confident you can handle it.
Dec 19 '07 #2

P: 2
Thanks for the quick reply.

Dont worry for the basics I will be able to do it.

The problem is that I want the report in a table format with normal look. Not showing empty spaces for the hidden fields.
Dec 20 '07 #3

Expert Mod 15k+
P: 31,494
Right, I'm not entirely convinced you have the basics covered (your 2 or 3 line response didn't inspire confidence), but I will give the gist of how I would approach the problem and see where we go from there.
Any reasonable questions (not ones I consider too basic) will find me willing to post replies to. Remember I warned you before we started how this was going to work.
  1. Start by determining a method of passing the information from the CheckBoxes to the report.
    I have a function I use for this which you're welcome to use :
    Expand|Select|Wrap|Line Numbers
    1. 'RptParms sets and returns a set of parameters required by a report.
    2. Public Function RptParms(intSetGet As Integer, _
    3.                          ParamArray avarParams() As Variant) As Variant
    4.     Static avarParms() As Variant
    5.     Dim intIdx As Integer
    7.     RptParms = 0
    8.     If intSetGet = 0 Then
    9.         intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
    10.         If intSetGet < 1 Then
    11.             ReDim avarParms(1 To 1)
    12.             avarParms(1) = "Error"
    13.             Exit Function
    14.         End If
    15.         ReDim avarParms(1 To intSetGet)
    16.         For intIdx = 1 To intSetGet
    17.             avarParms(intIdx) = avarParams(intIdx - 1)
    18.         Next intIdx
    19.     Else
    20.         'If outside bounds then it drops through and is set to "Error"
    21.         On Error Resume Next
    22.         If avarParms(intSetGet) = "Error" Then
    23.             RptParms = "Error"                  'On Error
    24.         Else
    25.             RptParms = avarParms(intSetGet)
    26.         End If
    27.     End If
    28. End Function
    Call the function before opening the report using intSetGet = 0 (and passing the string of required fields in avarParms) to pass the information on to the report.
    From within the report, call the function again (RptParms(1)) to pick up the string of fields that are required.
  2. Design a report with ALL the possible columns in it. We intend to hide columns that we don't need rather than create any new columns on the fly.
  3. In the reports Open event procedure, add code which handles all of the following :
    1. Processes the string of fields required.
    2. Processes through all the fields and sets only those which are required to visible. All others should be hidden.
    3. Processes through all the visible (displayed) fields setting their start positions (start pos + width of the previous field) and optionally, their widths. It may be that too many fields mean that some of the columns have to squeeze up.
    4. Sets the width of the report itself to match the width of the fields displayed (Try to - I'm not positive this can even work). This may not be necessary or what you want, but it's a consideration. I'll leave that up to you to decide on.

If I've missed anything out or you can't understand how I've explained something, that's fine to ask for clarification on.

Good luck.
Dec 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.