Open a Report based on combo box Selection ?

[chohan78]

hi,
i am struggling to pull out a report based on the combo box selection. I have NOI database and want to pull out a report for a specific customer that how many NOIs are generated by this customer..
I have a sepatare table of customer and another table of NCRs and the customer information comes from customer table.
I have tried the below statement but gives me an error message:

code for Macro that pulls the report in click event:

[CustomerName]=[forms]![CustName]![combocust1]

do i need to mention the report as well in some where in report ???

Not sure>>>> can anyone please guide me through.. much appreciated.

 

[pr2-eugin]

If you are using the DoCmd.OpenReport method, then you can specify this in the WHERE condition.

DoCmd.OpenReport "yourReportName", acViewNormal, , "[CustomerName] = '" & [Forms]![CustName]![combocust1] & "'"

I am guessing ComboBox returns a String (Customer name) ?
[/CODE]

 

[chohan78]

hi Paul,

thanks for the code, do i need to put this code into on click event of a button ???

 

[pr2-eugin]

Yes, that would make much sense !

 

[chohan78]

hi,

after clicking on button another window appears asking for customer name which is not looking good and also it prints the blank report ???
>>> ???

 

[pr2-eugin]

Okay lets do a bit of debugging..

Private Sub yourButtonName_Click()
    MsgBox "The button to open the report is Clicked !" & _
            vbCrLf & vbCrLf & "Please check your immediate window !", vbInformation
    Debug.Print "The value that is obtained from the User is : " & [Forms]![CustName]![combocust1]
    DoCmd.OpenReport "[COLOR=Blue]yourReportName[/COLOR]", acViewPreview
    Debug.Print Reports![COLOR=Blue]yourReportName[/COLOR].Report.RecordSource
End Sub

Check the Immediate window (CTRL + G) once you go into your VBA Window (ALT + F11), copy and paste what you have printed !

Replace yourReportName with the actual report name.

 

[chohan78]

hi Paul,

a bit silly question but where should i put this code below the original code or above the original code ????
thanks

 

[pr2-eugin]

In the button you have created to open the report, I also should have mentioned the yourButtonName should be the name of your actual button.

 

[chohan78]

thanks for that,
I have added this code in the click event of the button :
firstly if i don't select any customer name and click on report button it pulls the complete report for all the customers and even i select any customer from the list it still brings up the whole customer report which are in the table regardless of selection.

 

[pr2-eugin]

Show the code you have ! Or better upload a Stripped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[chohan78]

here is the code for the "Print Report" button:
Private Sub Command4_Click()
MsgBox "The button to open the report is Clicked !" & _
vbCrLf & vbCrLf & "Please check your immediate window !", vbInformation
Debug.Print "The value that is obtained from the User is : " & [Forms]![Form1]![Combo2]
DoCmd.OpenReport "rptCustSearch", acViewPreview
Debug.Print Reports!rptCustSearch.Report.RecordSource
End Sub

 

[pr2-eugin]

Okay so what was in the Immediate Window? I know this might not bring the actual records, as I mentioned we are debugging the code.

 

[chohan78]

the below appears in the imm.windows when i press Ctrl+G

The value that is obtained from the User is :
qry_test_customer_Search

 

[pr2-eugin]

So this happens when you select no customers? So the whole report will be shown. If you have selected the Customer, then there is something wrong, because the Debug.Print has not shown the Value of the comboBox. If it does not make any sense, try uploading a Stripped DB !

 

[chohan78]

hi Paul,

at last i have managed to print invidual customer report, i have ctreated an extra text box by referencing the combo box and then create a macro which pulls the control:

e.g. [Nameofnewtextbox]=[forms]![frmname]![Comboboxname]

sorted.