Combo box selection to report.

[Laguerrepj87]

Hey I have a form that has some calculations in them and combo boxes that you can select multiple things. How would i get the calculation to show up on my report? And how would I get the combo box selection to show up on my report?

 

[Laguerrepj87]

The combo box is tied to a Table that has all the information in two coloms. I want what ever they click on to show up on my report.

 

[Gammachaser]

This falls into the 'Not Enough Information' category.

Basically, to get the information from a ComboBox on a form into a report, set the value of a text box on the report to:

"= Forms!MyFormName.MyComboBoxName"

To get the information from a column that is not the Bound Column on the ComboBox use:

"= Forms!MyFormName.MycomboBoxName.Column(x)"

where x is the (0 based) number of the column you want to capture.

To 'get the calculation to show up' on the report you would either put the formula into a textbox (if it is simple enough) on the report or use a Function within the Report's Object (or Class) Module and call that.

Again... we need much more information to do more than take stabs at the answers to your questions.

 

[Laguerrepj87]

(Gammachaser)I tried that and it didnt work.
ok
First im not all that great in Access so I might of set something wrong.

I have a combo box on my form the form is called PNchild. now i have a table called PN1224, in that table i have a table called Riskid

Now I have another table caleed Riskschild

it has 3 feilds.
RiskchildID
Codeschild- these are filled with info that should never be changed
Riskchild- these are filled with info that should never be changed

Now in the combo box I have the Codeschild and the Riskchild to show. and you can make muilti selections.
The problem i am haveing is that i cant get the selections to get to the report. Nothing shows.
I know im missing something.
can anyone help me

 

[Gammachaser]

First, in the versions of Access that I am familiar with, ComboBoxes don't allow multiple selects... ListBoxes do. If your version allows MultiSelect in ComboBoxes, then I am betting the syntax would be similar.

To get a listbox selection you need to use the ListItems Collection which is a little odd. You have to reference the column of the ListBox that has your information in it. So, if your listbox is called lbMyList and it has the information you want in the first column (called, of course, Column(0)) and you wanted to get the first selected item you would use:

lbMyList.Column(0, lbMyList.ItemsSelected(0))

You could iterate through them using something like:

With lbMyList
   For x = 0 To .ItemsSelected.Count - 1
       ThisValue = .Column(0, .ItemsSelected(x))
       ' Do something with the value
   Next
End With

If you wanted to display them in a report, you could use:

Function GetListValue(WhichItem as Integer) as String '(Or Date, Integer, or whatever the column holds
Dim ctr as Control
ctr = Forms!myForm.lbMyList
If WhichItem > ctr.ItemsSelected - 1 Then Exit Function
GetListValue = ctr.column(0,ctr.ItemsSelected(WhichItem))
End Function

and use "=GetListValue(0)", "=GetListValue(1)" in your report TextBoxes.

 

[Laguerrepj87]

Gamm.... do I have to use all 3 of those or do i grab of of the 3
i tried using them and they didnt do anything. I think im doing something wrong

 

[dhookom]

You can't use this syntax outside of MyFormName.

=Forms!MyFormName.MycomboBoxName.Column(x)

I typically use a hidden text box with the control source

=MycomboBoxName.Column(x)

Then just reference the hidden text box in the report.

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

@ dhookom --

If you try to reference it directly from the report it fails, but (as I stated in my original post) using a Function makes it work just fine (at least in Access2003.)

You can't just put

=Forms!MyFormName.MycomboBoxName.Column(x)

into the Report Control's ControlSource, but I made a function in a standard module like this:

Function GetListBox(WhichItem As Integer) As String
With Forms!frmMyForm.lstBox
   Select Case WhichItem >= .ItemsSelected.Count
     Case True
        GetListBox = ""
     Case False
        GetListBox = .Column(1, .ItemsSelected(WhichItem))
   End Select
End With
End Function

And called it from a report using this:

=GetListBox(0)

as the Control source of a TextBox on the Report, and -- perfect!

=GetListBox(1)

gets the next one, etc. If you try to call more items than are selected you just get an empty string returned.

Laguerrepj87 -- did that make sense to you?

 

[dhookom]

I agree your solution will work but it requires the creation of a function and only works with a single list box on a single form. If you have more list boxes, you would need to create more functions.

It might be a better solution if the form/control was passed into the function so that it would be more generic.

=GetListBox("Forms!frmMyForm!lboMyLBox",0)

You might be able to simply wrap the control reference in the report inside Eval() like:

=Eval(Forms!MyFormName.MycomboBoxName.Column(x))

This would be the easiest if it works.



Duane
Hook'D on Access
MS Access MVP

 

[Laguerrepj87]

these comments of syntax are not working the way i was thinking.
Now I might have my form setup wrong and if i do let me know.
ok

I have a form with a lot of stuff on it. Now what i need help on is a Listbox that shows the contents of a table called Riskschild
in Riskschild i have 3 fields.
RiskID
Coderisk
Riskchild

in the Listbox it only shows Coderisk and Riskchild
I have the multi selection enabled.

Now when they click on one or more of the fields i want the information(s) that they click to go on the report as well as all my other stuff that i have on the form.
This report is not just for this one listbox. This report is a progress note that will include all of the information thats in my form.
If you need me to explain more just let me know what part you do not understand and ill try my best to explain it.

Thanks for all of You all help

 

[Gammachaser]

@ dhhokom --

Eval() does not work.

As for accepting the control as a parameter, I was trying to keep it as simple for the OP as I could. He seems to be having trouble enough interpreting what we are saying... but here we go anyway, since he has more than one ListBox like this.

@ Laguerre --

If you have multiple ListBoxes, you need to have your Function accept the control as a Parameter as dhookom and I discussed, so your Function would look like:

Function GetListBox(WhichControl as Control, WhichItem As Integer) As String
With WhichControl
   Select Case WhichItem >= .ItemsSelected.Count
     Case True
        GetListBox = ""
     Case False
        GetListBox = .Column(1, .ItemsSelected(WhichItem))
   End Select
End With
End Function

And in the ControlSource of your TextBox on your Report, use:

=GetListBox(Forms!MyFormName.MyListBox,0)

Of course, replace 'MyFormName' and 'MyListBox' with the names of the Form and ListBox you need the data for. Do not use quotes around the 'Forms!MyFormName' part. Also remember that ItemsSelected is 0 based, so the first selection is 0, the second is 1, etc.

As for getting the data in the proper place on the report, you have to format your report to look like you want it to and place the TextBoxes in which you want the ListBox information to appear in the location where you want the information. (I bet that sentance makes the language translation difficult!)

Do you have experience with creating Access Reports?

 

[dhookom]

I think there is confusion based on combo box vs list box and also "I have the multi selection enabled".

Laguerrepj87,
Are you attempting to filter your report based on values selected in a multi-select list box?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

I just did some testing (Access 2007) and found results that I didn't expect. I have a single select list box with several columns on an open form. The bound column is 0. I added text boxes to a report with control sources of:

=[Forms]![frmRptCrit].[lboEmployees].[column](2)
=Eval("[Forms]![frmRptCrit].[lboEmployees].[column](2)")

These return the correct value for the 3rd column (0-based).

This also worked to display the bound column:

=[Forms]![frmRptCrit].[lboEmployees].[column](0)

This didn't return any value:

=[Forms]![frmRptCrit].[lboEmployees]

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

@ dhookom -

I asked that question (ComboBox vs. ListBox) and didn't really get an answer, but since there is no 'MultiSelect' for a ComboBox, I figured it had to be a TextBox.

What the OP is looking for is the results when there is more than one selection, so the code you are testing will not work. You need to use the ItemsSelected Property which really works strangely (IMHO.)

I don't think there is a way around using a function, because of the way the expression evaluates when you put it in the ControlSource Property of a TextBox a report... you just get the numerical code for the ItemSelected Property of the ListBox. For some reason I can get it to work in the Function, but not in the RecordSource. I have played with it a lot.

Maybe you can do better. Let me know if you get it.

 

[Laguerrepj87]

@dhookom yes. I only want what is selected to show.

 

[Laguerrepj87]

The problem with me and the codes is that i dont know where i have to make my changed and where i have to place them.

 

[Gammachaser]

OK -- some basics, then.

From your original post, this is on a report. You need the Function in a Standard Module which can be created through the Database window by clicking on 'Modules' and selecting 'New' (exactly how varies by Access Version.)

The module (if there is nothing else in it) should look like:

Option Compare Database
Option Explicit

Function GetListBox(WhichControl as Control, WhichItem As Integer) As String
With WhichControl
   Select Case WhichItem >= .ItemsSelected.Count
     Case True
        GetListBox = ""
     Case False
        GetListBox = .Column(1, .ItemsSelected(WhichItem))
   End Select
End With
End Function

(cut and paste should work.)

Then select the control you want the information to appear in. On the Properties List for the control there is a field called 'ControlSource' under the 'Data' tab. The ControlSource should be:

=GetListBox(Forms!MyFormName.MyListBox,0)

Replace 'MyFormName' and 'MyListBox' with the names of the Form and ListBox you are referencing. You can repeat that for the number of TextBoxes you have to display the selected infomation by changing the 0 to 1 for the next selection and then to 2 for the third, etc. Use as many TextBoxes as you need to account for the number of expected (or allowed) selections. The form has to be open when the report is run.

Good Luck.

 

[Laguerrepj87]

Run-time error 2455

You entered an expression that has an invalid reference to the property itemsselected

thats what im getting with the last set of codes you posted

 

[Gammachaser]

When are you getting that message?

What version of Access are you using?

Are you using a ComboBox or a ListBox? (You can't multi-select with a ComboBox unless that has been changed in a later version of Access than I am using.)

Did you change the names of the Form and ListBox to match yours?

=GetListBox(Forms![i]YourForm[/i].[i]YourListBox[/i],0)

I just tested the code (again) and it works fine for me.

 

[Laguerrepj87]

Im getting this message when i open up my report.
Now its working I still get the message but it works. its a list box not a combo. The problem im having is that its not showning the muti selection its only showing 1 selection