Query problem reading from multiple sources (1 Viewer)

[swarv]

Hi all,

I have the following query named as Qry_Rpt_02.

SELECT BaseData.FirstName, BaseData.LastName, BaseData.Title, BaseData.Department, HolidayBookings.Yearfld, Entitlement.Entitlementfld, BaseData.EmpTerm, HolidayBookings.DateBooked, HolidayBookings.HalfDay, IIf([Halfday] Like 0,"1","0.5") AS CountD
FROM (BaseData INNER JOIN HolidayBookings ON BaseData.ID = HolidayBookings.ID) INNER JOIN Entitlement ON BaseData.ID = Entitlement.Id
WHERE (((Entitlement.Yearfld) Like [Forms]![HolidayMainBasic]![ComboYear]));

On the attached image there is a button at the bottom that is called Holiday Detail Report. When you click that it loads the report using the query above. Report is shown in image3.
As you can see the report is showing another users holiday.
What is the easiest way of getting this report to show the correct users information, i.e. the user that is shown in image2.

Many Thanks

Martin

 

[jzwp22]

Can you supply the code behind the button? The code would filter the data going to the report (using the current record in the form)

 

[Mr. B]

Try adding a reference to the BaseData Id (if that is the record is for the currently selected user) just as you have referenced the "Yearfld" value from the [Forms]![HolidayMainBasic]![ComboYear] control. I am assuming that you have a control on your form that has the BaseData ID for the currently selected user.

 

[swarv]

jzwp:
The code behind the button is:

Dim stDocName As String
a = [Forms]![holidaymainbasic]![ComboYear]
strwhere = "HolidayBookings.Yearfld = [Forms]![HolidayMainBasic]![ComboYear]"
DoCmd.OpenReport "Rpt_HolidayDetail2", acViewPreview, , strwhere

Mr B:
I have changed the WHERE line to:

WHERE (((Entitlement.Yearfld) Like [Forms]![HolidayMainBasic]![ComboYear]) AND (holidaybookings.id) LIKE [Forms]![HolidayMainBasic]![Child3]![ID]);

and I think that seems to work - not sure if that is the right way of doing it though.

I do have 1 more problem with another query / report - will post soon.

Thanks

Martin

 

[jzwp22]

You can use the code behind the button to do the filtering. BTW, I'm surprised that it is working since you have the whole strwhere in quotes.

Is the main form name HolidayMainBasic? If so, the following should work:

Dim stDocName As String
a = [Forms]![holidaymainbasic]![ComboYear]
strwhere = "HolidayBookings.Yearfld =" & me.[ComboYear] 
DoCmd.OpenReport "Rpt_HolidayDetail2", acViewPreview, , strwhere

Now amend the code to include a reference to the current record. You will need the key field (primary key) corresponding to the record currently displayed on the form. Do you have a hidden control on your form that is holding the primary key value?

Dim stDocName As String
a = [Forms]![holidaymainbasic]![ComboYear]
strwhere = "HolidayBookings.Yearfld = " & me.[ComboYear]  & " and yourprimarykeyfieldname =" & me.formcontrolholdingtheprimarykeyvalue

DoCmd.OpenReport "Rpt_HolidayDetail2", acViewPreview, , strwhere