"switchboard" for Reports

K

ken.halpern

I have a membership database form with a button on it to display a
smaller form with a choice of reports to print. I want these reports to
print with data from various related databases, but only for the member
who is being displayed on the membership form. I've racked my brain
trying to figure this out, but with no luck.

Any suggestions??

TIA

Ken Halpern
 
J

John Vinson

I have a membership database form with a button on it to display a
smaller form with a choice of reports to print. I want these reports to
print with data from various related databases, but only for the member
who is being displayed on the membership form. I've racked my brain
trying to figure this out, but with no luck.
Click to expand...

Base the Reports on Queries referencing the membership form:

=[Forms]![MembershipFormName]![ControlName]

using your own form and control names of course.

Or, you can use the optional WhereCondition argument of the OpenForm
method in your command button code:

.....
Dim strWhere As String
strWhere = "]MemberID] = " & Me!MemberID
DoCmd.OpenReport "MyReportName", WhereCondition := strWhere
.....

John W. Vinson[MVP]
 
K

Ken

John:
I tried both ways, but it still wants to print all of the rows in the
member table. This is a form that was created by the user, I'm trying
to plug it into a button. Am I missing something on the report itself
that would limit the range?

Ken


John said:
I have a membership database form with a button on it to display a
smaller form with a choice of reports to print. I want these reports to
print with data from various related databases, but only for the member
who is being displayed on the membership form. I've racked my brain
trying to figure this out, but with no luck.
Click to expand...

Base the Reports on Queries referencing the membership form:

=[Forms]![MembershipFormName]![ControlName]

using your own form and control names of course.

Or, you can use the optional WhereCondition argument of the OpenForm
method in your command button code:

....
Dim strWhere As String
strWhere = "]MemberID] = " & Me!MemberID
DoCmd.OpenReport "MyReportName", WhereCondition := strWhere
....

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

John:
I tried both ways, but it still wants to print all of the rows in the
member table. This is a form that was created by the user, I'm trying
to plug it into a button. Am I missing something on the report itself
that would limit the range?
Click to expand...

Since I have no idea how you set up the report, what query it's based
on, what criteria you used in that query, or how you're opening the
report, all I can say is "Probably".

Care to post some details?

John W. Vinson[MVP]
 
K

Ken

Sorry I didn't get back sooner, was away and didn't get to work on
this.

I've simplified a little and still having a problem.

I've placed a preview button on the form, it will still show the first
item in the table index, as well as the ability to scroll thru the rows
in the table. What I need is a single report screen for the current
info in the form (from multi tables). Here is the query
(q_renewal_form) and the click event code.


SELECT Member_Info.First_name, Payments.Payment_type, Payments.[Dues
Year], Payments.Amount_Pd, Payments.Date_Pd, Member_Info.Last_name,
Member_Info.Degree, Member_Address.Address_1, Member_Address.Address_2,
Member_Address.City, Member_Address.State, Member_Address.Zip,
Member_Address.Province, Member_Address.Country,
Member_Address.Phone_1, Member_Address.Email_address
FROM (Member_Info INNER JOIN Payments ON
Member_Info.ID_Number=Payments.ID_Number) INNER JOIN Member_Address ON
Member_Info.ID_Number=Member_Address.ID_Number;


-------

Private Sub Command197_Click()
On Error GoTo Err_Command197_Click

Dim stDocName As String
Dim StrWhere As String
StrWhere = "[ID_Number] = " & Me!ID_Number
stDocName = "2007 Renewal Form"
DoCmd.OpenReport stDocName, acPreview, StrWhere, q_renewal_form


Exit_Command197_Click:
Exit Sub

Err_Command197_Click:
MsgBox Err.Description
Resume Exit_Command197_Click

Again, thanx for any help

Ken
 
J

John Vinson

Sorry I didn't get back sooner, was away and didn't get to work on
this.

I've simplified a little and still having a problem.

I've placed a preview button on the form, it will still show the first
item in the table index, as well as the ability to scroll thru the rows
in the table. What I need is a single report screen for the current
info in the form (from multi tables). Here is the query
(q_renewal_form) and the click event code.


SELECT Member_Info.First_name, Payments.Payment_type, Payments.[Dues
Year], Payments.Amount_Pd, Payments.Date_Pd, Member_Info.Last_name,
Member_Info.Degree, Member_Address.Address_1, Member_Address.Address_2,
Member_Address.City, Member_Address.State, Member_Address.Zip,
Member_Address.Province, Member_Address.Country,
Member_Address.Phone_1, Member_Address.Email_address
FROM (Member_Info INNER JOIN Payments ON
Member_Info.ID_Number=Payments.ID_Number) INNER JOIN Member_Address ON
Member_Info.ID_Number=Member_Address.ID_Number;
Click to expand...

Well, then it's giving you exactly what you're asking for. This query
will select all records in the MemberInfo table which have
corresponding records in the Payments and MemberAddress tables, and
print them out.

What you see on the form is *ABSOLUTELY IRRELEVANT* to this fact. The
query is printing what you're asking it to print!

You can either change the Query to reference a form control as a
criterion - I don't know how your table is structured, or what
constitutes an "item", but try opening the query in design view and
putting

=[Forms]![TheNameOfYourForm]![TheNameOfSomeControl]

on the criteria line under the Member_Info table's primary key field,
using the name of your form and the name of a control on the form
containing that primary key value (the control can be invisible if you
wish).

Or, edit the VBA code in the click event of the button which prints
the report to set the value of the WhereCondition argument to

"[MemberID] = " & Me.txtMemberID

using your own field and control names.

John W. Vinson[MVP]
 
K

Ken

John:

That's how I solved it. Once I made the query specific to the ID_Number
(my primary key and link to other tables) I only get the one report I
need.

Thanks.

Ken


John said:
Sorry I didn't get back sooner, was away and didn't get to work on
this.

I've simplified a little and still having a problem.

I've placed a preview button on the form, it will still show the first
item in the table index, as well as the ability to scroll thru the rows
in the table. What I need is a single report screen for the current
info in the form (from multi tables). Here is the query
(q_renewal_form) and the click event code.


SELECT Member_Info.First_name, Payments.Payment_type, Payments.[Dues
Year], Payments.Amount_Pd, Payments.Date_Pd, Member_Info.Last_name,
Member_Info.Degree, Member_Address.Address_1, Member_Address.Address_2,
Member_Address.City, Member_Address.State, Member_Address.Zip,
Member_Address.Province, Member_Address.Country,
Member_Address.Phone_1, Member_Address.Email_address
FROM (Member_Info INNER JOIN Payments ON
Member_Info.ID_Number=Payments.ID_Number) INNER JOIN Member_Address ON
Member_Info.ID_Number=Member_Address.ID_Number;
Click to expand...

Well, then it's giving you exactly what you're asking for. This query
will select all records in the MemberInfo table which have
corresponding records in the Payments and MemberAddress tables, and
print them out.

What you see on the form is *ABSOLUTELY IRRELEVANT* to this fact. The
query is printing what you're asking it to print!

You can either change the Query to reference a form control as a
criterion - I don't know how your table is structured, or what
constitutes an "item", but try opening the query in design view and
putting

=[Forms]![TheNameOfYourForm]![TheNameOfSomeControl]

on the criteria line under the Member_Info table's primary key field,
using the name of your form and the name of a control on the form
containing that primary key value (the control can be invisible if you
wish).

Or, edit the VBA code in the click event of the button which prints
the report to set the value of the WhereCondition argument to

"[MemberID] = " & Me.txtMemberID

using your own field and control names.

John W. Vinson[MVP]
Click to expand...
 
Top