Trying to ApplyFilter to form

[Chris Freeman]

I have a form with timesheet fields, Sun - Sat, a field called Weekending,
which is the Friday of each week.

What I'm trying to do is the user opens the form, the Load code runs, and at
the bottom is code:

Dim WE As Date
Do Until rst.EOF
If rst.Fields![WeekEnding] >= Date Then
WE = rst.Fields![WeekEnding]
Me.cbo_WeekEnding = rst.Fields![WeekEnding]
Exit Do
End If
rst.MoveNext
Loop

DoCmd.ApplyFilter "Me.Weekending=" & WE

That is supposed to filter the data so that the form would display either a
blank entry field, no records, or display previous time entries based on this
week.

Currently the ApplyFilter does nothing, the form opens and displays all
records. If I click on a date and filter by by selection, it works perfectly,
but I can't get the applyfilter to kick in.

Any suggestions???

 

[NevilleT]

Hi Chris
You can try the code in the Open event rather than the Load. The Open event
occurs before a screen is displayed or data loaded. I would use different
queries and set the recordsource using the Open event rather than apply
filters.

 

[Chris Freeman]

No such luck Neville. Unfortunately, I'm one of those really good
non-professionals. And since i don't follow a particualr methodology, i
probably messed something up somewhere and it simply doesn't respond to the
docmd.applyfilter.

I've also tried doing a me.recordsource to filter the qry the from runs on.
Nothing is working and i've about had it here. Thank God its Friday.

--
Chris Freeman
IT Project Coordinator

Hi Chris
You can try the code in the Open event rather than the Load. The Open event
occurs before a screen is displayed or data loaded. I would use different
queries and set the recordsource using the Open event rather than apply
filters.

I have a form with timesheet fields, Sun - Sat, a field called Weekending,
which is the Friday of each week.

What I'm trying to do is the user opens the form, the Load code runs, and at
the bottom is code:

Dim WE As Date
Do Until rst.EOF
If rst.Fields![WeekEnding] >= Date Then
WE = rst.Fields![WeekEnding]
Me.cbo_WeekEnding = rst.Fields![WeekEnding]
Exit Do
End If
rst.MoveNext
Loop

DoCmd.ApplyFilter "Me.Weekending=" & WE

That is supposed to filter the data so that the form would display either a
blank entry field, no records, or display previous time entries based on this
week.

Currently the ApplyFilter does nothing, the form opens and displays all
records. If I click on a date and filter by by selection, it works perfectly,
but I can't get the applyfilter to kick in.

Any suggestions???

 

[NevilleT]

Hi Chris
If I understand what you want to do, you want to display all records for the
week you are in. For example if it is Tuesday, you want to show all records
from and including the previous Sunday. If that is the case, put a text box
on the form called txtWeekCommencing and another called txtWeekEnding. Using
the form open event do this.

Private Sub Form_Open(Cancel As Integer)
Dim dteWC As Date

dteWC = Date

Do While Weekday(dteWC) <> 1
dteWC = dteWC - 1
Loop

Me.txtWeekCommencing = dteWC
Me.txtWeekEnding = DateAdd("d", 6, dteWC)

End Sub

You now have the date range. Create a subform as a datasheet. Create a
query similar to the one below. I created a table for individual records
with a few fields in it including TimeSheetDate which is the date of the
entry.

Create a query and use the criterial to add the where clause. You can use
the build function (Right Click and select Build) to create the criteria for
selection. I created the SQL below.

SELECT tblTimeRecord.TimeRecordNo, tblTimeRecord.TimeSheetDate,
tblTimeRecord.PersonNo, tblTimeRecord.Time
FROM tblTimeRecord
WHERE (((tblTimeRecord.TimeSheetDate)>=[Forms]![Form1]![txtWeekCommencing]
And (tblTimeRecord.TimeSheetDate)<=[Forms]![Form1]![txtWeekEnding]));

Use this as the query for the subform.

Hope this works for you. If you want a copy of the database, send me your
email and I will forward it to you.

Enjoy your weekend.

No such luck Neville. Unfortunately, I'm one of those really good
non-professionals. And since i don't follow a particualr methodology, i
probably messed something up somewhere and it simply doesn't respond to the
docmd.applyfilter.

I've also tried doing a me.recordsource to filter the qry the from runs on.
Nothing is working and i've about had it here. Thank God its Friday.

--
Chris Freeman
IT Project Coordinator

Hi Chris
You can try the code in the Open event rather than the Load. The Open event
occurs before a screen is displayed or data loaded. I would use different
queries and set the recordsource using the Open event rather than apply
filters.

I have a form with timesheet fields, Sun - Sat, a field called Weekending,
which is the Friday of each week.

What I'm trying to do is the user opens the form, the Load code runs, and at
the bottom is code:

Dim WE As Date
Do Until rst.EOF
If rst.Fields![WeekEnding] >= Date Then
WE = rst.Fields![WeekEnding]
Me.cbo_WeekEnding = rst.Fields![WeekEnding]
Exit Do
End If
rst.MoveNext
Loop

DoCmd.ApplyFilter "Me.Weekending=" & WE

That is supposed to filter the data so that the form would display either a
blank entry field, no records, or display previous time entries based on this
week.

Currently the ApplyFilter does nothing, the form opens and displays all
records. If I click on a date and filter by by selection, it works perfectly,
but I can't get the applyfilter to kick in.

Any suggestions???