If the hours are in the subForm then your Filters and OrderBy should be:
Me.[name of subForm control].Form.Filter = "Format([dtmDailyDate],'ww') = "
& rst
(0).Value
Me.[name of subForm control].Form.OrderBy = "[dtmDailyDate]"
Me.[name of subForm control].Form.OrderByOn = True
----------
Me.[name of subForm control].Form.Filter = "Format([dtmDailyDate],'ww') = "
& rst
(0).Value
Me.[name of subForm control].Form.FilterOn = True
Me.[name of subForm control].Form.OrderBy = "[dtmDailyDate]"
Me.[name of subForm control].Form.OrderByOn = True
I hope this works.
--------------------
| Content-Class: urn:content-classes:message
| From: "D Collins" <
[email protected]>
| Sender: "D Collins" <
[email protected]>
| References: <
[email protected]>
<1dgzvmn#
[email protected]>
<
[email protected]>
<5jZhMKw#
[email protected]>
<
[email protected]>
<uORMoT8#
[email protected]>
| Subject: RE: Displaying only 7 records at a time.
| Date: Wed, 25 Feb 2004 14:31:31 -0800
| Lines: 417
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcP77x4oOJe5bmGZRhWUu0YklHyJSw==
| Newsgroups: microsoft.public.access.forms
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.forms:256923
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.access.forms
|
| Hello again,
|
| I want to thank you so much for helping me. I am pasting
| the code, however, as you requested. It seems to be
| working funny--when I press the Next week button they
| seem to go in order, but it shows one of the January
| weeks last and one of them first, but the rest are in
| order by week.
|
| I'm wondering...does it have anything to do with the fact
| that I have a main form (with the employee data) and
| these hours are in the subform?
|
| here's the code:
|
| Option Compare Database
| Option Explicit
|
| Public rst As DAO.Recordset
|
| Private Sub cmdNextWeek_Click()
| On Error GoTo ErrorTrap
|
| rst.MoveNext
|
| Me.Filter = "Format([dtmDailyDate],'ww') = " & rst
| (0).Value
| Me.OrderBy = "[dtmDailyDate]"
| Me.OrderByOn = True
|
| Exit Sub
| ErrorTrap:
| If rst.EOF Then
| Exit Sub
| End If
| End Sub
|
|
|
|
| Private Sub cmdPreviousWeeksHours_Click()
| On Error GoTo ErrorTrap
|
| rst.MovePrevious
|
| Me.Filter = "Format([dtmDailyDate],'ww') = " & rst
| (0).Value
| Me.OrderBy = "[dtmDailyDate]"
| Me.OrderByOn = True
|
| Exit Sub
| ErrorTrap:
| If rst.EOF Then
| Exit Sub
| End If
| End Sub
|
|
| Private Sub Form_Open(Cancel As Integer)
| Set rst = CurrentDb.OpenRecordset("Select Format
| ([dtmDailyDate],'ww') FROM tblHours Group By Format
| ([dtmDailyDate],'ww') ORDER BY Format
| ([dtmDailyDate],'ww');")
| Me.Filter = "Format([dtmDailyDate],'ww') = " & rst
| (0).Value
| Me.FilterOn = True
| Me.OrderBy = "[dtmDailyDate]"
| Me.OrderByOn = True
| End Sub
|
| Thanks,
| D.
| >-----Original Message-----
| >To have the week days in your Form in sort order just
| add to the Open Event
| >after "Me.FilterOn = True" the following:
| >
| > Me.OrderBy = "[MyDate]"
| > Me.OrderByOn = True
| >
| >And add in the OnClick event after the "Me.Filter....."
| the following:
| >
| > Me.OrderBy = "[MyDate]"
| >
| >As for you having to click 2x to have the code run. Let
| me present the
| >code again in a compact manner (IT WON'T RUN LIKE THIS)
| it may help spot
| >where you may have misplaced pieces of the code:
| >
| > Option Compare Database
| > Public rst As DAO.Recordset
| > Form_Open(Cancel As Integer)
| > Set rst =
| CurrentDb.OpenRecordset
| >.........
| > Me.Filter = "Format
| ([MyDate],'ww') = "
| >& rst(0).Value
| > Me.FilterOn = True
| > Me.OrderBy
| = "[MyDate]"
| > Me.OrderByOn = True
| > End Sub
| > Command12_Click()
| > ' the error trapping
| piece
| > rst.MoveNext
| > Me.Filter = "Format
| ([MyDate],'ww') = "
| >& rst(0).Value
| > Me.OrderBy
| = "[MyDate]"
| > End Sub
| >
| >
| >If the above does not help do the following:
| > - Open the Form in design view
| > - Select the menu option View >
| Code
| > - Select the menu option Edit >
| Select All
| > - Copy the selection and send it
| to me in this post.
| >I'll take a quick scan of it.
| >
| >Eric
| >
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "D Collins" <
[email protected]>
| >| Sender: "D Collins"
| <
[email protected]>
| >| References: <
[email protected]>
| ><1dgzvmn#
[email protected]>
| ><
[email protected]>
| ><5jZhMKw#
[email protected]>
| >| Subject: RE: Displaying only 7 records at a time.
| >| Date: Tue, 24 Feb 2004 14:12:24 -0800
| >| Lines: 224
| >| Message-ID: <
[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MimeOLE: Produced By Microsoft MimeOLE
| V5.50.4910.0300
| >| Thread-Index: AcP7I0e5nos12nOyRi69FbtHGt9w/Q==
| >| Newsgroups: microsoft.public.access.forms
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.access.forms:256750
| >| NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
| >| X-Tomcat-NG: microsoft.public.access.forms
| >|
| >| Thanks so much for the code. It works, except, I'm
| >| wondering if I did something wrong--it doesn't display
| >| the weeks in order. Is there a way to force that?
| >|
| >| Also, I have to click once, then once again to make
| the
| >| code activate. I have the code attached to the on-
| click
| >| event, and not double-click, so that's not the problem-
| -
| >| just wondering if you might know what's up.
| >|
| >| Thanks.
| >|
| >| >-----Original Message-----
| >| >Here's some sample code that may work (however you'll
| >| have to included
| >| >whatever error trapping needed). I'll give my best
| in
| >| explaining it:
| >| >
| >| >Notes:
| >| > - My table is named "Table1". The date field
| in
| >| my table is named
| >| >"Mydate"
| >| > - Will need to make a library reference
| >| to "Microsoft DAO 3.6 Object
| >| >Library"
| >| >
| >| >1- Create a command button on Form named "Command12"
| >| that will serve as
| >| >your Forward navigation button (if this works you
| would
| >| create another
| >| >command button that serves as the Backward navigation
| >| button)
| >| >
| >| >2- Copy and paste the following code behind the Form
| >| >
| >| >Option Compare Database
| >| > ' rst is a Recordset object that will store
| >| > ' the week #s from your table
| >| >Public rst As DAO.Recordset
| >| >
| >| >Private Sub Command12_Click()
| >| >On Error GoTo TestEOF
| >| >
| >| > TestEOF:
| >| > IF rst.EOF THEN
| >| > Exit Sub
| >| > END IF
| >| >
| >| > ' you could probably put some more sophisticated
| >| > ' above error trapping than what I included
| above
| >| >
| >| > ' as a user clicks the Forward command button
| >| > ' the recordset would move Forward
| >| > ' here is where you would put your Error trapping
| >| > ' to test for rst.EOF (i.e., end of recordset)
| >| > rst.MoveNext
| >| >
| >| > ' set the Filter of the Form to only show the
| >| records
| >| > ' with the week that the Recordset is object is
| on
| >| > Me.Filter = "Format([MyDate],'ww') = " & rst
| (0).Value
| >| >
| >| >End Sub
| >| >
| >| >Private Sub Form_Open(Cancel As Integer)
| >| > ' as the Form is opened the Recordset object rst
| >| > ' is set to a GroupBy result pulling the weeks
| in
| >| > ' your table sorted Ascending
| >| >
| >| > Set rst = CurrentDb.OpenRecordset("SELECT Format
| >| ([MyDate],'ww') FROM
| >| >Table1 GROUP BY Format([MyDate],'ww') ORDER BY Format
| >| ([MyDate],'ww');")
| >| >
| >| > ' The Form is then Filtered on the first week
| found
| >| > ' in the Recordset
| >| >
| >| > Me.Filter = "Format([MyDate],'ww') = " & rst
| (0).Value
| >| > Me.FilterOn = True
| >| >
| >| >End Sub
| >| >
| >| >
| >| >I hope this helps.
| >| >
| >| >Eric
| >| >
| >| >--------------------
| >| >| Content-Class: urn:content-classes:message
| >| >| From: "D Collins"
| <
[email protected]>
| >| >| Sender: "D Collins"
| >| <
[email protected]>
| >| >| References: <019b01c3fa66$bca95cd0
|
[email protected]>
| >| ><1dgzvmn#
[email protected]>
| >| >| Subject: RE: Displaying only 7 records at a time.
| >| >| Date: Tue, 24 Feb 2004 03:25:42 -0800
| >| >| Lines: 97
| >| >| Message-ID: <
[email protected]>
| >| >| MIME-Version: 1.0
| >| >| Content-Type: text/plain;
| >| >| charset="iso-8859-1"
| >| >| Content-Transfer-Encoding: 7bit
| >| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| >| X-MimeOLE: Produced By Microsoft MimeOLE
| >| V5.50.4910.0300
| >| >| Thread-Index: AcP6yO/tK9E7lf0WRHSW6Hzp5Fx/Ug==
| >| >| Newsgroups: microsoft.public.access.forms
| >| >| Path: cpmsftngxa06.phx.gbl
| >| >| Xref: cpmsftngxa06.phx.gbl
| >| microsoft.public.access.forms:256631
| >| >| NNTP-Posting-Host: tk2msftngxa08.phx.gbl 10.40.1.160
| >| >| X-Tomcat-NG: microsoft.public.access.forms
| >| >|
| >| >| I don't believe it would work, as I need to use
| those
| >| 7
| >| >| records to enter/edit data. A group by would give
| a
| >| >| summation.
| >| >|
| >| >|
| >| >| >-----Original Message-----
| >| >| >Hi,
| >| >| >
| >| >| >Would a GroupBy Query give you the results you
| need
| >| and
| >| >| base your Form on
| >| >| >that query work?
| >| >| >
| >| >| >Example:
| >| >| >Field: FDate: Format([OrderDate],"ww")
| >| >| >Total: Group By
| >| >| >Sort: Ascending
| >| >| >Show: Yes
| >| >| >
| >| >| >I hope this helps! If you have additional
| questions
| >| on
| >| >| this topic, please
| >| >| >respond back to this posting.
| >| >| >
| >| >| >
| >| >| >Regards,
| >| >| >
| >| >| >Eric Butts
| >| >| >Microsoft Access Support
| >| >| >
| >| >| >"Microsoft Security Announcement: Have you
| installed
| >| the
| >| >| patch for
| >| >| >Microsoft Security Bulletin MS03-026? If not
| >| Microsoft
| >| >| strongly advises
| >| >| >you to review the information at the following
| link
| >| >| regarding Microsoft
| >| >| >Security Bulletin MS03-026
| >| >|
| >|
| ><
http://www.microsoft.com/security/security_bulletins/ms0
| >| >| 3-026.asp> and/or
| >| >| >to visit Windows Update at
| >| >| <
http://windowsupdate.microsoft.com/> to install
| >| >| >the patch. Running the SCAN program from the
| Windows
| >| >| Update site will help
| >| >| >to insure you are current with all security
| patches,
| >| not
| >| >| just MS03-026."
| >| >| >
| >| >| >
| >| >| >--------------------
| >| >| >| Content-Class: urn:content-classes:message
| >| >| >| From: "D Collins"
| >| <
[email protected]>
| >| >| >| Sender: "D Collins"
| >| >| <
[email protected]>
| >| >| >| Subject: Displaying only 7 records at a time.
| >| >| >| Date: Mon, 23 Feb 2004 15:42:45 -0800
| >| >| >| Lines: 20
| >| >| >| Message-ID: <019b01c3fa66$bca95cd0
|
[email protected]>
| >| >| >| MIME-Version: 1.0
| >| >| >| Content-Type: text/plain;
| >| >| >| charset="iso-8859-1"
| >| >| >| Content-Transfer-Encoding: 7bit
| >| >| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| >| >| X-MimeOLE: Produced By Microsoft MimeOLE
| >| >| V5.50.4910.0300
| >| >| >| Thread-Index: AcP6ZrypPQdYCeljT8GzEu6NwVIM9Q==
| >| >| >| Newsgroups: microsoft.public.access.forms
| >| >| >| Path: cpmsftngxa06.phx.gbl
| >| >| >| Xref: cpmsftngxa06.phx.gbl
| >| >| microsoft.public.access.forms:256584
| >| >| >| NNTP-Posting-Host: tk2msftngxa08.phx.gbl
| 10.40.1.160
| >| >| >| X-Tomcat-NG: microsoft.public.access.forms
| >| >| >|
| >| >| >| Hello,
| >| >| >|
| >| >| >| I have a database that needs to keep track of
| hours
| >| >| per
| >| >| >| day and total per week. What I am looking to do
| is
| >| >| have
| >| >| >| the form display just one week at a time with a
| >| button
| >| >| >| that would navigate to the next and previous
| weeks
| >| >| >| (obviously 7 days at a time). I would like the
| >| form
| >| >| to
| >| >| >| look like this:
| >| >| >| Reg. Hours OT Hours
| Vac
| >| Pay
| >| >| >| Monday 2/23/04
| >| >| >| Tuesday 2/24/04
| >| >| >| etc.
| >| >| >|
| >| >| >| What would be the best approach to display just
| one
| >| >| >| week's worth of data at a time? A filter based
| on
| >| >| week
| >| >| >| ending date?
| >| >| >|
| >| >| >| Hope this makes sense.
| >| >| >| Thanks
| >| >| >| D.
| >| >| >|
| >| >| >
| >| >| >.
| >| >| >
| >| >|
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|
