Open report from form with combo selection

[Archie1]

Hi again. I have a form with a combobox where I choose an employee and it autofills the form with the Employees name and details. I wanted to have a command button on the form which will take me to a report showing only the employee details selected from the combobox on the form. Hope that makes sense. My combobox is called CmbIIBY and the 3 combobox fields for selection are EmployeeID, First Name, Surname. My Report is called Indepth Incidents by Employee

 

[arnelgp]

you create a form first for the details of employees.
now on your original form, drag the newly created form.
create the Link on the subform.

Link Master Fields: cbmIBY
Link Child Fields: EmployeeID

when you add new employee on the subform make sure to requery the combobox.

on your command button to print the current employee in view, supply the Where clause of the OpenReport:

private sub button_click()
docmd.OpenReport "Indepth Incidents by Employee",acViewPreview,, "EmployeeID = '" & me.subformname.form!EmployeeID & "'"
end sub

 

[Archie1]

Thanks. I have already done the form and subform so its just help with opening the report with the selected data. What do you mean by where clause ? (Sorry very new to this!)

 

[arnelgp]

the where clause of the docmd.openreport. i already put the the "where clause":

"EmployeeID = '" & Me.EmployeeID & "'"

suggest googling the syntax of ms access OpenReport for correct syntax.

 

[Archie1]

Thanks for your help. I have googled but cant find a lot. My combobox selection is called "leader ID" and not Employee ID as I originally quoted and My report is called RptIDBE. One post on google said I needed to have the name of the combobox in there? With the following (copied and pasted) its coming up with a parameter box asking for the Leader ID? This is the first time I have attempted anything like this so any help is appreciated

Private Sub Command19_Click()
Dim strWhere As String
strWhere = "LeaderID="" & Me.LeaderID" & ""
DoCmd.OpenReport "RptIDBE", acViewPreview, , strWhere
End Sub

 

[Archie1]

Have just tried your code as follows but getting this error "Compile Error expected end of statement" and the word "reporting" from my subform name is hilighted?
Private Sub Command20_Click()
DoCmd.OpenReport "RptIDBE", acViewPreview, , "Leader ID =" & ME.Accident Reporting Subform1.form!LeaderID&""
End Sub

 

[arnelgp]

if leader ID is numeric:

DoCmd.OpenReport "RptIDBE", acViewPreview, , "[Leader ID ]=" & ME.Accident Reporting Subform1.form!LeaderID

if it is string:

DoCmd.OpenReport "RptIDBE", acViewPreview, , "Leader ID ='" & ME.Accident Reporting Subform1.form!LeaderID & "'"

 

[Archie1]

Many thanks for your help. Its still not working. Error message "Compile Error -Expected:End of statement. The word "Reporting" is hilighted in that error. Ive checked the names of everything and its all correct. My method (form) is as follows - I created a form with a combobox to select from a list of employees. That then autofills the employee first name & surname. I then created a subform and dragged it onto the main form and linked it by LeaderID. When I select an employee it then autofills the whole form with related accidents/ incidents and it works perfectly. I called the form Accident Reporting Subform1. Any ideas as to why I cant get the report to show the selected data only?

 

[Minty]

You need to remove all those spaces from your form names and fields - as it is causing you massive headaches. Where you have spaces in anything you will need to put square brackets around them to make things work, therefore this

DoCmd.OpenReport "RptIDBE", acViewPreview, , "[Leader ID ]=" & ME.Accident Reporting Subform1.form!LeaderID

Becomes

DoCmd.OpenReport "RptIDBE", acViewPreview, , "[Leader ID ]=" & ME.[Accident Reporting Subform1].form!LeaderID

But even that may not work as when you put spaces in sometimes access replaces the spaces with Underscores ... So it might become

DoCmd.OpenReport "RptIDBE", acViewPreview, , "[Leader ID]=" & ME.Accident_Reporting_Subform1.form!LeaderID

See how confusing this is! Get your self a naming convention in place, it will help you enormously. http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[Archie1]

Thanks for the advice! I am very new to Access and appreciate the help from you guys. I will have a go at that

 

[Archie1]

I decided to start again and re-do the forms. I designed a blank form with a combobox to select the employee. I then dragged the subform and linked by leader ID. For some reason it worked. I can now select the employee from the combobox which autofills the form. I then click my new command button (print record )and hey presto
This is the code
Private Sub Command14_Click()
DoCmd.OpenReport "RptIDBE", acViewLayout, , "[leader ID] = '" & Me.Combo9 & "'"
End Sub


Thanks everyone