How to link(insert the subreport) in the main report??

[qajussi]

Hi!
I have several questions.
I have a button(passes a open arguement, say it is nameID) which calls a main report and this main report has three sub reports, a)Name subreport b)Address subreport c)BankAccount subreport.

1) I am using the pass through query like this.
This is on the SQL server. I have access front end and sqlserver backend.
ptQeuryGetAddress:
SELECT streetName, city, state,From Address, Name WHERE Address.nameID = Name.nameID AND Name.nameID = '23'

sub Subreport_Address_Open()
strSQL = "SELECT streetName, city, state," & _
"From Address, Name WHERE Address.nameID = Name.nameID AND Name.nameID = " & nameID
Set db = CurrentDb()
Set rsqAddress = db.QueryDefs("ptQueryGetAddress").SQL = strSQL

Me.RecordSource = rsqAddress
End Sub

I don't think I got the correct DAO code here. I think you see what I am trying to ask.

Why do you do a hard code of 23 in the pass through query and do samething in the strSQL string? Is this correct DAO syntax for the pass through??
I don't understand how this works. It seems to be working correctly in my code. What happens I pass 34 but does the passthrough still contains 23 in the code??
2) I use the above code for the subreport and
in the mainreports, I change the source object to that
Subreport_Address since that i do this in that subreport:
Me.RecordSource = rsqAddress

however, i don't see the subaddress report in the main.
When i run the subaddress report, i see the correct result.
Do I need to link the subreport to the main report??

I know I can put the sql in the subform/subreport properties(souce object) window and link master field and link child field which I left clear since i want to do above method.

3) when I side scroll bars which i found out they are the subreport.
I can click one of them and from the property, i can select "subform in window".

This does open up the subreport.
After I do this,i see white label with the subreport name instead of th scroll bar.
Did i just dettach the subreport?
After I close the subreport and mainreport and reopen the main mainreport, i still that white label of subreport instead of the scroll bar.

Do i need to reinsert the subreport?? if i need to, how do i do that??

4) can i use something else other than dao for the #1?
Can i use the stored procedure??


Thanks for your help.

 

[Remou]

Hi
It seems to me that you are printing a report for one person at a time, for example NameID = 23. If this is so, it may be possible to simplify your report. For example, set the record source of the main report to the table containing all names and addresses. Set up name and address fields in the main report. Set up Bank Details as a subreport, linked to the main report on NameID. Open the report using something like:

[tt]DoCmd.OpenReport "MyReport", acViewPreview, , "NameID = " & NameID[/tt]
This assumes that NameID is numeric.

I am afraid I have missed answering several of your points, however, this may serve as a jumping-off point for other members of the forum.

 

[qajussi]

Thanks Remou.

That is how I open up the report.
DoCmd.OpenReport "MyReport", acViewPreview, , NameID
I am passing the NameID.

I wasn't clear with the main report.
The mainreport will be about one person.
Each report will be about the person.


Do you know anything about the subreport in the mainreport(scroll bar effect)??
Thanks again.

 

[Remou]

Hi
I am not sure if this is what you mean, but here goes anyway. If you open a form or report and then choose design, any subforms or subreports will appear as a white box. The form contained by the subreport or subform box is still there, but it cannot be edited. If you choose design from the database window, this does not happen.

 

[qajussi]

Thanks Remou.

I think what I saw(horizontal scroll bar) is a subform which was minimized(by bring the bottom of the form to the title bar of the form.

When you right click the subform, it has the open subfrom in the new window option.

When I do that, I don't see the scroll bar(subform) in the main form. I only see the name of the subform.

I am trying to understand how the subforms works in side of the main form.

Thanks.