Create a Report from a Subform within a Main form?

[ndkvan]

Hi

I have a Main form (which is an employee's information from one table) & a Subform (which is all training courses that THIS employee enrolled). In the subform, it will list all courses that for only this employee.

i would like to create a button, to create a report: a list of all courses for only this employee (this list should come from the subform)

How can I let user to generate a report when they view at that specific employee?

Thank you

 

[Jeanette Cunningham]

You use OpenReport:
DoCmd.OpenReport "RptMyReport", acViewPreview, , "EmployeeID=" & Me.SubformControlName.Form.EmployeeID

Note: replace RptMyReport with the name of your report.
This code goes on the click event of a button on the parent form.
This code assumes that EmployeeID is a number field.
The subform control name can be different from the name of the subform inside the subform control.

 

[ndkvan]

Thank Jeanette,

Your code seem works, but it just not work correctly.
You are right, my subform & main form connect by EmployeeID. However, the EmployeeID field (in Subform) has condition: WHERE [tblEmployee].EmployeeID=[EmployeeEnrol_subform].EmployeeID

So,the click event in the form:

DoCmd.OpenReport "IndividualEnrollment_Report", acPreview, , "EmployeeID=" & Me.EmployeeEnrol_subform.Form.EmployeeID


=> the button doesn't generate without asking to enter EmployeeID?

Any suggests?

 

[Jeanette Cunningham]

When you say
"However, the EmployeeID field (in Subform) has condition: WHERE [tblEmployee].EmployeeID=[EmployeeEnrol_*subform*].EmployeeID"

What do you mean 'has a condition'?
Where is the condition?
Why do you have an asterisk in [EmployeeEnrol_*subform*]?
How many subforms do you have on this main form?

 

[boblarson]

Why do you have an asterisk in [EmployeeEnrol_*subform*]?

Jeanette:

If you saw the * in the email notification it is because a plain text email (which is generated by the forum) puts those around something that is bold.

 

[Jeanette Cunningham]

Perhaps it will work better if you put the button to open the report in the subform.
Use code like this:
DoCmd.OpenReport "IndividualEnrollment_Report", acPreview, , "EmployeeID=" & Me.EmployeeID

Perhaps you got error when the code was in the main form because you are not understanding the difference between a subform control name and the name of the subform inside the control.

 

[ndkvan]

Yes, Jeanette. Thank for your replies
I am self studying this Access by book, so, I am not really sure what I'm doing. I only know some very basic, macro & VBA behind all!

I did create a button as you suggested, and have the line:
DoCmd.OpenReport "IndividualEnrollment_Report", acPreview, , "EmployeeID=" & Me.EmployeeID;

Well, the proble is the report is not create from the form, even thouse they have the same EmployeeID as the common key.
So, does "EmployeeID=" or Me.EmployeeID come from the main form?

I still have the Syntax Error, and i can't find what would be problem?



FYI:
my subform is generated from the mainForm through [EmployeeID].Value
My query for Subform will match if EmployeeID in this query = with EmployeeID in mainform's table.
=> thus, my report depend on the same query that i used for the subform=> every time i click the button, it's will as me
Enter the Parameter :
[EmployeeID].value


Do you have any suggest to help eliminate this issue?


Thank you so much

 

[Jeanette Cunningham]

What is the table that the main form is based on?
What is the table that the subform is based on?
What is the primary key for the table the main form is based on?
What is the foreign key for the table the subform is based on?
Go back to the button for the report and put this line above the line that opens the report.
Debug.Print "EmployeeID: " & Me.EmployeeID
In your form in normal view, click the code to run the report.
Open the immediate window and see what access got for EmployeeID
Post back and let us know what access got for EmployeeID

 

[ndkvan]

Hi,

1.What is the table that the main form is based on?
=> main form based on tblEmployee

2.What is the table that the subform is based on?
=> subform based on a query, which is generated from many tables: Employee, Enrollment, Courses,

3.What is the primary key for the table the main form is based on?
=> primary key for main from is EmployeeID

4.What is the foreign key for the table the subform is based on?
=> bcoz the subform based on the Query, so at the Criteria row on Query, I leave the condition in the EmployeeID field: [employee_txt].value

5. I went back to the button for the report and put this line above the line that opens the report.
Private Sub cmdOpenIndividualReport_Click()
Debug.Print "EmployeeID= " & Me.EmployeeID
DoCmd.OpenReport "IndividualEnroll_Report", acPreview, , "EmployeeID=" & Me.EmployeeID
End Sub

=> they keep saying Syntax error, but i already check the name of the fields, should be correct! :? But.., still error


Thanks

 

[Jeanette Cunningham]

Post the query that is the record source for the subform.

 

[ndkvan]

This is the query that I used for the subform in SQL view:

SELECT [04_employee_def].SEID, [First] & " " & [Last] AS Name, [02_track_def].Track_id, [02_track_def].Track_Type, [02_track_def].Track_Description, [03_course_def].CourseID, [03_course_def].CourseDescription, IIf([21_EmployeeEnrollment].SEID=[04_employee_def].SEID,[21_EmployeeEnrollment].StartDate,' ') AS StartDate, IIf([21_EmployeeEnrollment].[SEID]=[04_employee_def].[SEID],[21_EmployeeEnrollment].[CompletionDate],' ') AS CompletionDate

FROM 21_EmployeeEnrollment RIGHT JOIN (((03_course_def INNER JOIN (02_track_def INNER JOIN 12_one_track_many_course ON [02_track_def].Track_id=[12_one_track_many_course].track_id) ON [03_course_def].CourseID=[12_one_track_many_course].course_id) INNER JOIN 11_one_position_many_track ON [02_track_def].Track_id=[11_one_position_many_track].track_id) INNER JOIN (13_one_position_many_employee INNER JOIN 04_employee_def ON [13_one_position_many_employee].employee_id=[04_employee_def].SEID) ON [11_one_position_many_track].position_id=[13_one_position_many_employee].position_id) ON [21_EmployeeEnrollment].Course_id=[03_course_def].CourseID

WHERE ((([04_employee_def].SEID)=[ListResult_list].[value]))

ORDER BY [02_track_def].Track_id, [03_course_def].CourseID;

FYI:
EmployeeID is SEID field
ListResult_list is a listbox control, and the control field is SEID


thanks

 

[Jeanette Cunningham]

I see that you don’t have a field called EmployeeID in the record source of your subform.
What is the name of the field that uniquely identifies each row of the subform?
Do you have a table for employees and what is the primary key of that table?

 

[ndkvan]

As I stated at the end of the post, in "FYI:"
EmployeeID is SEID field


Thanks

 

[Jeanette Cunningham]

You should be able to easily open a report which shows all the employees on the subform, using a button on the main form.
The code for the button
If Not IsNull(Me.ListResult_list) Then
Debug.Print Me.ListResult_list
DoCmd.OpenReport "IndividualEnroll_Report", acPreview, , "SEID=" & Me.ListResult_list
End If