Check Boxes-how to verify for inclusion on report 2

[number2]

I would like to use check boxes on a form to select which feilds should be included on a report. (in this case to select recipients of the printed report, a Proof of Service letter.) How can I acheive this?

 

[Lozere]

It is very depending wether you have fixed fields with variable contents or you want to modify the layout..

If you want to change the contents of the field,
Open you report from the form with the CheckBox that way
In your report, the ControlSource are FIELD1, FIELD2 and so on

>>(Dim SQL_String as String

From the value of the CheckBoxes, build a SELECT statement, something like:
"SELECT customer_name AS FIELD1, customer_ZIP as FIELD2, .... FROM My_table;"
where you modify the name in lowercase.
(Of course include is this SELECT any other fields you use in the report)

Then open the report:
>>DoCmd.OpenReport "My_Report",,,SQL_String

If that is to short, please post more details on your process

 

[ChemistZ]

Do you mean you want the report to only print the ones who are checked? You can just base the report on a query that asks to see only those records where the check box field is equal to True.

 

[number2]

Yes Chemist, that is what I need to happen. I am at a loss of how to work with check boxes. I've never had to use them; until now! Any specific suggestions you can offer would be great! Thanks Chemist and Lozere

 

[rbowes]

My suggestion is that you place in the tag of each check box this: [TableName].[FieldName]

When you want to determine the fields for this report call a function like this:

Private Function ScanCheckBoxes()
dim ctl as control
dim strTemp as string

for ctl in me.controls
if typeof ctl is checkbox then
strtemp = strtemp & ", " & ctl.tag & ","
end if
next
'Chop off the trailing comma
strtemp = "SELECT " & Left(strTemp, Len(strTemp) - 1)

'Here is where you would call a function or code to
'finish the sql statement. IF you want to
'to modify a query's sql do it here then OpenReport

end function


Does that help a little?

Bob

 

[number2]

Thanks rbowes, I will play around with that, but frankly I think it is a bit over my head. Seems strange it should be so difficult to do something so basic; well difficult for me!

 

[number2]

I am having trouble with getting any of these solutions to work. rbowes, I understand using the tag for the checkboxes, but how and where do I call the function? In the afterupdate event? Also, I don't seem to be able to come up with the proper function code. Any ideas? Thanks so much.

 

[number2]

rbowes, can you be more specific regarding the function and its use? Unfortunately, I am not getting far.

 

[BoxHead]

number2,

Checkboxes return the following values:

checked = -1
unchecked = 0
greyed out = Null


If you can base your report on a query, try using '-1' in the criteria field. If the query itself is where you're running into trouble, let us know.



HTH
John

Use what you have,
Learn what you can,
Create what you need.

 

[number2]

Thanks Boxhead, I understand the values of the checkbox, I just don't know how to run the procedure to get the -1 or 0 value to return the requested feild data. In other words, how can I get the feild on the report to display the feild data once the value of the check box is known. I am at a loss.

 

[MangroBongacello]

Hi nuber2

I had a little fun trying to solve that, because it's not a bad idea to let the user customize the report that way. I used rbowes's idea to start from. Here are the steps to achieve the goal:

1. Create a report with the maximum number of text boxes that the user can choose to see (the number of checkboxes on your form). Don't set RecordSource property.

2. For the controlsource of the text boxes write:

1st text box: field1, 2nd field2...

3. Create a public variable:

Public ReportSource as String

You do that in a module (mudules tab).

4. For each check box on your form set the tag property

In design view of the form open the property sheet for check box and under tag property write:

[Table or Query of the form].[Corresponding field name]

For example: If you have a table as a record source for a form named Recipients and a field named Name, set the tag property [recipients].[name]

5. In the OnClick event of the button that opens the report write procedure:

Dim str As String
Dim ctl As Control
Dim chk As CheckBox
Dim i As Integer

i = 0
For Each ctl In Me
If TypeOf ctl Is CheckBox Then
Set chk = ctl
If chk = -1 Then
i = i + 1
str = str & chk.Tag & " as field" & i & ","
End If
Set chk = Nothing
End If
Next

Dim j As Integer

For j = (i + 1) To x 'x = number of chkboxes
str = str & "Null as field" & j & ","
Next

'Chop off the trailing comma
str = Left(str, Len(str) - 1)

str = "select " & str & " from Table or query for the form;"
ReportSource = str

DoCmd.OpenReport "ReportName", acViewPreview

6. In the OnOpen event of the report write:

Me.RecordSource = ReportSource

That's it. It will work like this: The first checked field will be issigned to field1, 2nd to field2... To non-checked fields will be assigned value null. The sql for the report will look something like this:

select [recipients].[name] as field1,[recipients].[adress] as field2,null as field3,null as field4 from recipients;

So the first text box in report will display the first checked field, the 2nd will display 2nd checked field..., non-checked fields will display null (nothing).

Hope it will work for you. Good luck.

Mangro

 

[rbowes]

In my haste yesterday to answer your post, I forgot to place the evaluation of the checkbox's value. It should be:

for ctl in me.controls
if typeof ctl is checkbox and (ctl = True) then
strtemp = strtemp & ", " & ctl.tag & ","
end if
next

I would also set each checkbox default to "False" so that the checkbox will only have True or False values.


But,after thinking about it, I would use a Simple multi-select Listbox for this type of thing. It is more flexible in how you fill it and can be easier to maintain. Plus, it will take up less real estate.

Bob

 

[number2]

Thanks for the excellent help! You guys rock! How does a multi select list work?

 

[rbowes]

Just create a listbox and set its MultiSelect property to Simple or Extended. Simple lets you select more than one value in the Listbox and while Extended allows you to select a range of the listbox's values (ie Listbox row number 1 - 5, 'simple' lets you select rows 1,4,6,7,9 separately.). To find the values selected you loop through the listbox using the itemsselected property:

Dim strSQL As String
Dim varItm As Variant

For Each varItm In Me.MyList.ItemsSelected
debug.print varItm
Next

Listboxes also allow you to hide values in their columns but still access them through VB.

If there are multiple columns and you want the values of the 3rd column, then do this

Dim strSQL As String
Dim varItm As Variant

For Each varItm In Me.MyList.ItemsSelected
Debug.Print Me.MyList.Column(1, varItm)
Next

This works like a charm. In your situation, I would make the listbox columncount = 2 with columnwidths of 0";1" to hide the first column and place the table name in the first column and field names in the 2nd. Then loop through it to make your select statement:

strSQL = strSQL & MyList.Column(0,varitm) & ".[" & mylist.Column(1,varitm)& "],"

This dynamically creates the sql statement to the needs of the user instead of you trying to figure out what they want. Its just a thought.

Bob

 

[rbowes]

Sorry, I said if you want the 3rd column. That's wrong. The listbox columns are zero-based, so the first column is 0 like this: Mylist.column(0,varitm)

I'm sorry if I confused you (thank God for debugging tools)

Bob

 

[number2]

Thanks rbowes. I am trying the multi select list. How do I call up the feilds selected for inclusion on the report? i.e., what do I do in the report feilds to look up the values selcted on the form in the list?
Thanks!

 

[number2]

Thanks for the help Mangro; I just can not get that to work. I will need to find a "dumbed down" solution or alternate to using check boxes. Low tech is probably the best use of my time. (It is funny how technology and its development and use is such a time killer)But hey..that's how many of you folks make your living!
Thanks so much.