Report opens only with one ID from a form, why? 1

[ychousa]

HI. I use the following code to open a report from frmMain.

stDocName = "rptShowExpenses"
DoCmd.OpenReport stDocName, acViewPreview, "[DetID]= " & Me.DetID

DetID is a primary key in tblDetail which is a base of frmMain.

But, whatever DetID I choose in frmMain, the report only opens with one DetID.

In SQL code in the report, there is no WHERE clause because DetID is parsed from frmMain.

What is wrong?

Thanks for your comments in advance.

John

 

[RickSpr]

You're missing a comma before "[DetID] = " & Me.DetID. It's in the third parameter position, which is reserved for the name of a query to be used as a filter. You want it in the fourth parameter, the WhereCondition.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ychousa]

Hi, Rick. Thanks for your comments.

I put a comma as you suggested. There was a slight change in the result, but it didn't work.

Before I put a comma, the report always displayed with a DetID, 36 whatever DetID I choose in frmMain.

But now, if I try with DetID 36, it works. But with a different DetID, all the fields are blank.

As I said in my first post, what I did was to put the WHERE clause in DoCmd of frmMain, but put nothing in the SQL of my report. No filtering or something..

Is this the right way to achive my goal?

John

 

[RickSpr]

Well, I'm a little confused. By putting "[DetID] = " & Me.DetID into the OpenReport, you're asking for the report to be filtered on the one DetID, but the statement "whatever DetID I choose in frmMain, the report only opens with one DetID" makes it sound like you're expecting more.

Your report may have been saved with a filter applied. Open the form in design view, erase the Filter property, and save it. Then try again, and tell me what it did and what you expected it to do.

If the report comes up with all the fields blank, check to be sure there really is a record in the report's record source that has the DetID value you selected.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ychousa]

Hi, Rick.

You're right.

My report's record source had a problem. I have 6 to 7 subforms inside frmMain connected with DetID, and if any of them doesn't have a data for a DetID, the report shows all fields blank.

I think I should fix this somehow because there's a possiblity that one of the subforms are empty while others have data with a DetID.

Could you help me with this?

 

[RickSpr]

It sounds as if the main form's record source has an inner join to the table that may have no related records.

Usually, you use a main form to display data from a parent table and subforms to display child table data. The main form's record source is based on only the parent table, and each subform's record source is based on one of the child tables. The Master Link Fields and Child Link Fields properties of each subform control identify the fields used to relate the tables. The built-in logic for subforms acts as if there were an outer join from the parent table to the child table.

So normally, you do not have the child tables in the main form's record source at all.

Please show me the following:
For the Parent table:
Name and primary key column(s)
For the child table which may have no related records:
Name, key column(s)
Confirm Foreign key is DetID
For the main form:
Name and record source
If record source is a query, SQL statement
For the child form which may have no related records:
Name and record source
For the subform control associated with the child form
Confirm Master Link Fields and Child Link Fields = DetID


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ychousa]

Hi.

Here's the info:
1. Name and key columns of parent table.
Name:tblDetail PKetID(No Duplicate), SID(Dup OK), SYear(Dup OK)

2. Name and key columns of child tables
Name: tblBudget PK: Same as tblDetail
I have 5 more child tables as tblBudget, which have the same set of PKs and one-to-one relationship with tblDetail.

I also have 2 more child tables(different set of PKs) which are bases of another subforms in frmMain, but these are ok with empty data, so I think we can exclude these from consideration. But tell me if you want info about these, too.

3. Name and Record Source of main form.
Name: frmMain
Record Source: tblDetail

4. Name and Record Source of Child forms.
Name: tblBudget Subform
Record Source: A SQL statement. I deleted most of fields which are not related to this analysis for your convenience.

"SELECT tblBudget.DetID, tblBudget.SID, tblBudget.SYear, tblBudget.Booth_Rental, tblBudget.Booth_Electricity, tblBudget.Booth_Handling
FROM tblBudget;"

And of cource the same kind of SQL statements for other child forms.

5. LInk of forms looks like this:
DetID;SID;SYear

Thank you for your help and let me know if you need more info.

Johhn

 

[RickSpr]

Good information. Thanks.

In my previous post, I had somehow lost track of the fact that we were talking about a problem in the report, not the forms.

I now believe that the problem is in your report's Record Source. You're probably using inner joins there. You need to use left outer joins, with tblDetail nested most deeply.

If the report's Record Source is a query, the easiest thing to do is to modify the joins in the query design. Double click each join line and select the option that says "Include ALL records from 'tblDetail' and ...". (I expect that all the lines link tblDetail with one of the other tables. If that's not the case, it probably should be.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ychousa]

Hi, Rick.

Thanks for your instruction.

It works beautiful!

Have a great day!

John