Solved Open form and limit subforms records!

[slharman1]

I am not even sure where this post goes but here it is:
I have a Employee form With EmpID and various other fields.
Linked by Master/Child Fields is a sub form with time clock data, I want to be able to open the forms and limit the subform data to a Period ending date picked on a modal (or dialog? not sure of the correct terminology) form. I can't seem to get my head wrapped around this one.
Thanks for any help you may have.

 

[GPGeorge]

Filter the subform's recordset using the value of the control on the dialog form. You can do that in a couple of ways, but I'd probably just put that control on the main form instead of the dialog so it's always available.

 

[slharman1]

Filter the subform's recordset using the value of the control on the dialog form. You can do that in a couple of ways, but I'd probably just put that control on the main form instead of the dialog so it's always available.

Thanks for the reply but I do not want to have to requery the subform's data everytime i change main form records - in other words, I do not want to have to select week ending date every time the user moves to another employee on the main form

 

[bastanu]

You wouldn't have to. Make the textbox unbound (txtPeriodEndDate) and populate it when you open the form (you could also do it in code in the Form Load event if you have some rule for the value you want to start with). It will stay like that until you change it while you move through your records. To limit the subform records you can add that control to the Master\Child linking fields if the subform contains a control that is bound to the PeriodEndDate:
EmpId;txtPeriodEndDate(master)\EmpId;PeriodEndDate(child)

Cheers,

 

[slharman1]

You wouldn't have to. Make the textbox unbound (txtPeriodEndDate) and populate it when you open the form (you could also do it in code in the Form Load event if you have some rule for the value you want to start with). It will stay like that until you change it while you move through your records. To limit the subform records you can add that control to the Master\Child linking fields if the subform contains a control that is bound to the PeriodEndDate:
EmpId;txtPeriodEndDate(master)\EmpId;PeriodEndDate(child)

Cheers,

Ok, so maybe I am not understanding:
On the subform I have these fields, EmID, TimeIn, and TimeOut.
I want to limit the records on the subform to where the TimeIn field is between period ending date and period ending date - 6
I don't have a period ending field anywhere on the forms as it is calculated based on the users selection on the dialog form.

I guess I could add the unbound txtbox containing the data from the dialog to the main form as suggested, but I can't figure out how to limit the subform's records to the dates (or Date Range)

Thanks for being patient with a newb.

 

[slharman1]

Filter the subform's recordset using the value of the control on the dialog form. You can do that in a couple of ways, but I'd probably just put that control on the main form instead of the dialog so it's always available.

Ok, so I have the unbound txt box on my main form with a date in it. how do i limit the txtTimeIn field on my subform to between that date ad 6 days prior?
Thankds

 

[GPGeorge]

Ok, so I have the unbound txt box on my main form with a date in it. how do i limit the txtTimeIn field on my subform to between that date ad 6 days prior?
Thankds

The subform's recordsource would be a parameterized query, along these lines

"...WHERE txtTimeIN Between DateDiff("d", -6, Date) and Date())

 

[bastanu]

Replace both references to Date() in GPGeorge's answer above with a reference to your unbound control (Forms!frmYourMainForm!txtPeriodEndDate)
Cheers,

 

[GPGeorge]

Replace both references to Date() in GPGeorge's answer above with a reference to your unbound control (Forms!frmYourMainForm!txtPeriodEndDate)
Cheers,

 

[slharman1]

Replace both references to Date() in GPGeorge's answer above with a reference to your unbound control (Forms!frmYourMainForm!txtPeriodEndDate)
Cheers,

So do I set the recordsource for the subform to a sql statement instead of the query def (currently set to qryPayrollPunchEdit)?

Here is my current recordsource in SQL View:

SELECT tblTimeClock.TimeIn, tblTimeClock.TimeOut, tblTimeClock.Comment, tblTimeClock.AdjTimeIn, tblTimeClock.AdjTimeOut, tblTimeClock.TimeClockID, tblTimeClock.EmpID
FROM tblTimeClock
WHERE (((tblTimeClock.WONumber) Is Null))
ORDER BY tblTimeClock.TimeIn DESC , tblTimeClock.EmpID;

 

[slharman1]

Thanks guys, I think I got it working.

 

[bastanu]

Awesome, good luck with your project!
Cheers,