Need help with a print report command button in a form 1

[Teleconnections]

Was wondering if anyone can point me in the right direction. I know very basic C++ programming, not familiar with VBA at all.

I am trying to make a command button in a form. That will print that form’s current selected record information in the format of a report I have already created. I also need to tell it to print to a specific report based on a field value in that current record.

For example…

If I have a field named Company and the value is “A”, I want to print a report that I already have a specific layout for company “A”. I also need that report to only print that current record and not all the information based on the query it was created from.

If the value is Not “A”, I want it to print a different report that I have already laid out. But again I need it to print only that current record and not the query the report was originally created from or is currently tied to as the data source.


Any help would be appreciated

thanks

 

[GingerR]

1) Are you saying that you have different reports for different companies? Is this because the layout requirements are different for each company?

2) generally: in the button's OnClick event, you'd put something like this. This is based on your comment that you have a diff report for diff companies. I'm assuming since you only want to print the current record, there's a unique field for each record. I'll call it RecordID.

dim strWhere, strReportName as String
strWhere = "[RecordID] = " & me.RecordID

If me.Company = "A" then
    strReportName = "ReportA"
else
    strReportName = "ReportB"
end if

docmd.OpenReport strReportName, acviewpreview, , strWhere

so the report is based on a query or table that returns lots of records, but the strWhere will only show the record with the RecordID of the form.

 

[hymn]

have a look at
thread703-954993

Hope this helps
Hymn

 

[Teleconnections]

Thanks for the help Ginger & Hymn!

I am trying Ginger's method first, but I am getting stopped in the debugger on the line that has me.company

what does "me.Company" reference in your example? The field with the value I am comparing in the current record? the form name?

Well I tried changing the me.company to the field name of the value I am comparing and now I am getting a pop up asking to enter paramter value "Company_For". Here is what I currently have in the onclick event

Private Sub Command72_Click()
On Error GoTo Err_Command72_Click

Dim strWhere, strReportName As String
strWhere = "[Company_For] = " & Me.Company_For

If Me.Company_For = "TC" Then
strReportName = "TCTest"
Else
strReportName = "SubTest"
End If

DoCmd.OpenReport strReportName, acViewPreview, , strWhere
Exit_Command72_Click:
Exit Sub

Err_Command72_Click:
MsgBox Err.Description
Resume Exit_Command72_Click

End Sub



Company_For is the field I am comparing in the form
"TC" is the yes or no value it is looking at
"TCTest" is one report and "SubTest" is the other report

Where does the current form name or query the form is using fit into this? If needed

Thanks

 

[GingerR]

ME" = the form the button is sitting on. If you notice, when you begin typing "ME." in VBA, a pop-up box comes up with all kinds of choices, including the names of all the controls (text boxes, etc) on your form.

in my example, "Company" is the name of i assumed a text box or other control with a company name in it. I assumed you'd want to open a report FOR THAT COMPANY. It was correct for you to change that to the name of your field. Without knowing what the name of your form, control or report is, folks here will do their best to provide samples of code that you'll have to tweak to suit your specific needs.

I'm not really clear on what you are doing with "TC"? What is that? A company name? If not, what is it? What does "TC" stand for? I'm not understanding why you have two different reports so I'm not sure if I'm providing you with the right answer.

What the code is saying is: "Open the report named BLAH where the company name is whatever is in the control named Company_For on the current form".

If you are getting a pop-up asking for "Company_For" then that tells me that perhaps the control (text box?) on the form is not called that. Do you have a text box or other control on the form called "Company_For"? In the query/table that the report is based on, is there a field called "Company_For"?

This is a pretty straight-forward use of MS Access, so if you have a text book laying around you'll probably get an answer much quicker from it than from me ) You can also use the sample Northwind.mdb that comes with Access to see how they do it. I'm more than happy to help but I may not be here quickly enough for you.

g

 

[Teleconnections]

Ok, after figuring out how some of the syntax for this language works. I have found that the problem is in the filter for the report.

For instance
My primary key is a control named record_id
and the filter

"[record_id] = " & Me.record_id

is not working for me. Your "if" statement is working correctly for me. For instance if I change the filter to just

Me.record_id

the correct report will show based on the For company field criteria. So no problems there. Problem is, all the records show instead of the one that is currently being viewed (selected).

I have tried the filters that hymn gave me a link to also and I get the same result. A pop up basically asking me to enter the record_id instead of taking it from the currently selected form. So what am I missing here. I tried the example (creating a command button to print current record) in the help files for access and that didn't work either. Maybe if some can explain the formating of the filter to me I can figure it out. Hymn explained it in her link, but putting the

"table.primary key= " & primary key

format is not working for me. The tables are referenced, but I didn't think that matters.

Just for more info that may help the table name is "Info table" that the queries and forms are built off. So based on hymns example the filter

"Info_table.record_ID = " & record_ID

I also tried Bobnz's

"record_ID = " & Me.record_ID


Get a pop up asking enter parameter value record_ID either way

 

[GingerR]

is your record_ID a number or text?

by using the term "Filter" in your last post, are you saying that you have something typed into the FILTER property of your Report Design? If so, change it to

[Record_ID] = [Forms]![YourFormName]![Record_ID]

another option is to go into the design of the report, into the query/table that the report is based on (the RecordSource), and in the criteria under the Record_ID field put something like:

[Forms]![YourFormName]![YourControlNameThatIsOnTheForm]

You can also put your cursor in the CRITERIA area under the field Record_ID, right-click and choose BUILD and it will help you select the form and control name and build the statement for you.

If you are still messing with the original suggestion:
are you puting the above example in as the WHERE, or as the FILTER? Note the number of commas, etc, in the statement I'd given you:

docmd.OpenReport strReportName, acviewpreview, , strWhere

If you still cannot get this to work, please supply the following information:
1) the query that the report is based on (copy the RecordSource SQL statement)
2) the name of the the Report
3) the name of the form
4) the name of the control on the form
5) if the Record_ID is a number or text

 

[Teleconnections]

Hey Ginger , sorry to be confusing. When I was saying filter, I mean't the Where clause. Basically from reading the OpenReport action parameters. It seemed to me the where clause is an additonal filter to your existing filter on the table/query that the report is created from. Which would work great, if I could figure out why it's not working.

Putting the criteria in the query that the report is based off as you suggested worked. But I would rather not do that, because I am using multiple forms that will need to access the 2 reports. And If I set the criteria in the query itself for the record_id field, then I have to create an additional query and report for every form I want this to work in. Which is easy enough to do, via copying the reports and queries and just changing the criteria to the correct form name it will reference. It seems to me it would be much easier if the where clause would work for me though.

Here is info you asked for

1)TCTest query
2)TCTest
3)Test print form
4)Record_ID
5)Record_ID is a number (it's a autonumber, primary key)


Am I wrong to think the where clause filters your existing filter(defined in the query) that the report runs off?

What does leaving a space between the commas for the filter name in your where statement do? And yes I did make sure the syntax was exactly like you had it for the where variable.


Thanks for the help! much appreciated

 

[Teleconnections]

Well for some reason the syntax you gave me in your very first post is now working

strWhere = "[Record ID] = " & Me.Record_ID


Must have been user error at some point while testing it.

Thanks for the solution and sorry for added confusion

 

[GingerR]

No problem! Glad it worked out!

g