Report To Open Based on Comobox Selection

[Njliven]

I have some code that was written by someone else. I am trying to duplicate this in another database but, can't seem to get it to work. I also don't understand how this code is working as as there is no reference to the combo box in the code behind the print button. But depending on what you select in the combo box the report opens and filters on that selection. Can someone help me understand how this is working, so I can get it work as well. The following code is behind the print button. There is a separate combo box, that does not seem to have a connection to the print button. It just has a query behind it for the items to select from. Thanks!


Public Sub BTNPrint_Project__Job__Numbers_Click()


On Error GoTo Err_BTNPrint_Project__Job__Numbers_Click

Dim stDocName As String
stDocName = "RPTProject Number (Job) Book Year"
DoCmd.OpenReport stDocName, acPreview
Exit_BTNPrint_Project__Job__Numbers_Clic:
Exit Sub
Err_BTNPrint_Project__Job__Numbers_Click:
MsgBox Err.Description
Resume Exit_BTNPrint_Project__Job__Numbers_Clic

[pbaldy]

First thing I'd check is the record source of the report. It may be a parameter query.

[Njliven]

Already check that, it is not a parameter query.

[pbaldy]

Second place to look is code in the open or load events of the form. If you don't find it there, can you post the db here?

[pbaldy]

Oh, and check the filter property of the report too.

[orange]

Further to Paul's comment, if the combo is involved in selecting the Report to run, then somewhere that selected value must adjust the underlying recordset, or Filter, or Openarg....
Can you post a copy of the database -- remove anything confidential?

Have you done a search for the combo box name in your project?

[Njliven]

There is a filter on the report, if refereces the form and the combo box, which makes sense. But, when I put the filter on the one I am trying to create, it comes up with a box like it is looking for the data, of course if I just continue I get a blank report, if I enter the data here the report runs, but it should run without having to enter anything. It should be getting the from the combo box. I keep thinking I have some typo, but I copied and pasted the names of the form and combo box to try and avoid this error. But something is still not right, any suggestions.

[pbaldy]

The form is open at the time? I don't personally use that method as a rule. I either use a parameter query or this:

BaldyWeb wherecondition

[June7]

The working report must have a dynamic parameter in some query involved in the RecordSource - criteria that is a reference to combobox. It's the only explanation for it to be working properly.

I also prefer using the WHERE CONDITION argument over dyanmic query parameters.

[Njliven]

I found the problem. It was in the form name. I had pulled the form name from the caption, which was different that the actual name of the form.
Here is another problem I am facing with this. I went in and made an unrealted change to the report, add another field. Then when I went to run the report the filter was gone.
I re-entered the filter and it works, but how can I prevent this from happening. Can this filter be coded in the code behind the print button. I tried to do that but, can't seem to get it work. Not sure just where it goes or how it should be stated there. Part of this problem is that the filter is more complicated than just a field. Here is the code for my filter

left([ProjectID],2) = Right([Forms]![frm_CO_PROJ_Main]![cmb_PrintPROJYear], 2)

It is matching the right two digits from the year in the combo box to the first 2 digits in the Project ID field.

Thanks for all the help.

[orange]

...so it is matching the first 2 digits of the ProjectID with the last 2 digits of the year

Not really. It is assigning the

the rightmost 2 digits of the year

to

the leftmost 2 digits of ProjectID

I agree with Paul and June7, your should probably adjust the code and use a WHERE CONDITION.

I would still recommend that you remove the embedded significance of the code and only concatenate for display.

[pbaldy]

Not really. It is assigning the

the rightmost 2 digits of the year

to

the leftmost 2 digits of ProjectID

I disagree; in the filter, it's filtering, no different than a filter of:

FirstName = "Paul"

but I agree about switching methods.

[Njliven]

so can someone be more specific about what I need to do in order to change methods. Does something need to exist in the code behind the actual print button? If so, can some one help me figure out how to write it so it works. I have attempted to do this, but have had no luck in getting it to work there. Thanks!

[pbaldy]

Did you see the link in post 8?

[Njliven]

Yes, that what I tried to use as guide, but I could not seem to get the right combination to get it to work.