Switchboard for reports - repost and revision

Greetings all:

I posted this about 10 days ago, and I'm still looking for an answer.
Here is the current status. Any help is appreciated:

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.

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

Hi Ken,

Why not simply have the query that drives your report with a where clause
that looks up the member from the form, eg:

WHERE MemberID = forms!FORMNAME.MEMBERIDFIELD

Damian.

"Ken" wrote:

Greetings all:

I posted this about 10 days ago, and I'm still looking for an answer.
Here is the current status. Any help is appreciated:

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.

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

I'll try it and let you know.

Thanks

K.


Damian S wrote:
Hi Ken,

Why not simply have the query that drives your report with a where clause
that looks up the member from the form, eg:

WHERE MemberID = forms!FORMNAME.MEMBERIDFIELD

Damian.

"Ken" wrote:

Greetings all:

I posted this about 10 days ago, and I'm still looking for an answer.
Here is the current status. Any help is appreciated:

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.

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

Damian:

On first look this should work. I think I need to refine the code to
not show anything when there is no data in any part of the join. Next
step....


Ken wrote:
I'll try it and let you know.

Thanks

K.


Damian S wrote:
Hi Ken,

Why not simply have the query that drives your report with a where clause
that looks up the member from the form, eg:

WHERE MemberID = forms!FORMNAME.MEMBERIDFIELD

Damian.

"Ken" wrote:

Greetings all:

I posted this about 10 days ago, and I'm still looking for an answer.
Here is the current status. Any help is appreciated:

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.

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