Full Version: combo box and subreports - how to link/update
UtterAccess Forums > Microsoft® Access > Access Forms
kamle_b
Dear all,
I have a form called frmNum. This form has one combo box. A combo box called cboView has value list of "Line A Early", "Line A Late", "Line B Early" and "Line B Late". Now i have created different reports for each of this list. I have got a subreport box on the form frmNum. What I want to do is when "Line A Early" is selected in cboView, subreport should display "Line A Early report"; if "Line A Late" is selected then "Line A Late report" should be displayed in subreport and so forth.
How do i go on about this?
Thank you in advance.
adamsherring
Well, there's a few ways to go about it, but all end up using the same general code.
That your looking for is in the after update event of your combo box, place :
docmd.openReport "reportName"
Now, to figure out to determine how to do which. If the report names are the same as the values in your combo box, you could just concatenate them to get it working ie : docmd.openreport me.cboView & " report"
If they dont, then you should (for ease of use), add another column to your combo box, and give it a numeric value. then implement something like this :
CODE
dim strReportName as string
select case (cboView)
       case 1:
              strReportName = "Line A Early"
        case 2:
               strReportName = "Line A Late"
        
  .... etc
end select
    
docmd.openreport strReportName

and that should do it for you.
Hope this helps,
Adam
kamle_b
Hi adam,
till few questions, sorry I am not pro with combo boxes and VB code.
for my combo box cboView, I go into properties, and select event After update, there i select an event procedure so that i can write code in VB. Then in vb i write your above Select case code with few amendments. i save the vb code. now i go back to access and in After update do i put anything? like "=cboView_AfterUpdate ()"??
also, another thing, I do not understand, if the combo box list and report names are same, you said to put docmd.openReport "reportName"
in combo box after update event. I put reportName as "Line A Early". But what about other reports. I did not get this at all!!
HAs you said:
Now, to figure out to determine how to do which. If the report names are the same as the values in your combo box, you could just concatenate them to get it working ie : docmd.openreport me.cboView & " report"
Where do i put this: docmd.openreport me.cboView & " report"?? in vb code?
Thanks
adamsherring
Ok,
Like I mentioned, you have two options of doing it - the first is very easy if the values in your combo box (the rowsource you typed in as a value list) are the same as the names of your reports (If you have to add "report" in each case, that's fine as well).
So, lets assume its that way first.
right click on your combo box and select Build Event Procedure.
The VB screen will pop up, and you'll be in cboView_beforeUpdate
select AfterUpdate from the dropdown in the top right corner of the window.
then type :
docmd.openreport me.cboView & " report"
and you're done.
This assumes a few things. First, that the names in the combobox are letter for letter the same as the report names once you add " report" to the end of it.
ie, one of your combo values is : "Line A Early", and the accompanying report is : "Line A Early Report"
This code will also throw an error if you try to open a report that doesn't exist (or the name doesnt match)
if this is *not* the case (the values in the combo box are different than the accompanying report names),
then I suggest you alter some properties of the combo box.
Change the # columns from 1 to 2. Ensure that 1 is the bound column. Change the ColumnWidth to 0;5 (I just picked 5 - its going to be the width of the names, so pick whatever you want for that one).
Now you need to change your rowsource slightly.
It should look something like this now :
"Line A Early";"Line A Late";"Line B Early";"Line B Late" ...
You need to change it to :
1;"Line A Early";2;"Line A Late";3;"Line B Early";4;"Line B Late" ...
The numbers will be a way of referencing those values from the select statement.
Then go into the after_update event of your combo box and type the ammended select statement.
You will not have to do anything else - the combo box will recognize that it has that procedure because of the property .AfterUpdate is equal to [EventProcedure].
If that is still not working for you, upload a copy of your db and I can help you out a little better.
Adam
kamle_b
Hi Adam,
Yam,
Your reply was great.
Now this is what i have done. I followed your steps
"""So, lets assume its that way first.
right click on your combo box and select Build Event Procedure.
The VB screen will pop up, and you'll be in cboView_beforeUpdate
select AfterUpdate from the dropdown in the top right corner of the window.""""
I wrote following code in Afterupdate event:
-------------------------------------------------------------------------------------------
Private Sub cboView_AfterUpdate()
DoCmd.OpenReport Me.cboView & " Report"
End Sub
Private Sub cboView_BeforeUpdate(Cancel As Integer)
End Sub
------------------------------------------------------------------------------------------------
I went back to access and was in combo box properties, there i went under Event tab and went to After update option, selected [Event Procedure] there and ran the program.
Now when i run the form and select one of the values from the combo box, say "Line A Early" for eg, Access gives me error message " A Problem occured whle ms access was communicating with the OLE server or ActiveX control"
What does this error mean? is it saying that Access is not able to communicate with VB? Or is it something else? What can i do to rectify this error message?
I am personally thinking it is to do with my access and VB and nothing to do with code. What you think?
Thank you
kamle_b
Sorry forgot to mention, I will apply first option as all my combo box values are:
Line A Early
Line A Late
Line B Early
Line B Late
And all my reports are called:
Line A Early Report
Line A Late Report
Line B Early Report
Line B Late Report
Combo box is called: cboView
and subreport where the results of combo box should appear is called: subShfit
Thus, If cboView value = Line A Early
subShift subreport should show = Line A Early Report
If cboView value = Line B Late
subShift subreport should be = Line B Late Report
and so on....
adamsherring
Ok, with regards to your ActiveX error, check this post , it may be able to help you.
econd question. Is there any reason to store the name of the report in the text box before opening it? Or do you want to display it in the text box, and then open it with another control? If you're just opening the report, i'd suggest not storing it in a textbox at all.
CODE
private sub cboView_afterUpdate()
     me.subshift = me.cboView & " Report"
     docmd.openreport me.subshift
end sub

That should work for you.
Adam
kamle_b
Hi Adam,
tried few of the above codes.
Trying this code below gives me run time error:
CODE
Private Sub cboView_AfterUpdate()
Me.subShift = Me.cboView & " Report"
DoCmd.OpenReport Me.subShift
End Sub

Oalso tried to put this below code expecting it to open the report, but instead it straight goes into print. Thus as soon as i select Line A Early in combo it goes straight into printer and prints the report!!
CODE
'DoCmd.OpenReport Me.cboView & " Report"

Any help? Thanks
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.