open form after checking system date

[thecheyenne]

Hi there and happy new year to all of you.
I've been reading the thread "Auto Date in form "pages" from 'frys' in the topics and his/her problem is similar though not identical to mine.
Here is my database structure which works, crudely, but I'd like to re-fine it, if possible, so feel free to give your 2-pence on my design effort

My 'journal' is actually a time-table for an educational establishment. It relates to my lessons over the week, Mo-Fr, so there will never be a need to have dates relating to a Saturday/Sunday and there will also be substantial gaps in the dates during holidays.

For this reason, I have decided to enter the dates needed manually into a tbl.dates, from which my frm.planner gets its dates.

At present, I have sorted the query that feeds frm.planner in descending order, which means the last entered date will show up first when opening frm.planner.

Here is the thing I'd like to refine:
When planning ahead over the next week or so, I'll be using dates from tbl.dates that are in the future and so when opening frm.planner, it opens at a date that is yet to come. This means, I have to scroll backwards, sometimes quite a bit, depending on how much 'forward planning' I've done.

It's this 'back-scrolling' I'd like to eliminate if possible by putting in some code somwhere that compares the system date to the dates in tbl.dates to see if there is a match and then opens frm.planner on the matching date.

Any ideas anybody?
Thanks for reading

[missinglinq]

Place this code in your frmPlanner. If a record exists with the current date, it'll open to that record, otherwise it'll open as usual, in your case to the latest date entered.

Where txtYourDate is the control that holds the date:

Private Sub Form_Load()
  Me.txtYourDate.SetFocus
  DoCmd.FindRecord Date
End Sub

Welcome to Bytes!

Linq ;0)>

[thecheyenne]

Hi there 'linq' and thanks for the reply.
tried it out straight away but I must be doing something wrong.
I was actually looking for the bit of code that opens the form in the first place but couldn't find it. I'm sure it must be there somewhere, else the form wouldn't open, or would it?
the from's name is 'Lessonplans' and this is the code attached to it so far:
================================================== =====
Private Sub Form_lessonplans_load()
Me.Date.SetFocus------------------------------------------- thanks, again, for the tip
DoCmd.FindRecord Date----------------------------------- wasn't quite sure where
End Sub -----------------------------------------------------------exactly to put it, so I put it at
-------------------------------------------------------------------------the very beginning.

I'm sure that
if I could find the code
that opens the form in
the first place, and inserted
the relevant bits there,
then it would work,
but those,
perhaps,
are the hopes of
an amateur enthusiast.

---------------------------------------------------------------------------------------------------------------
Private Sub Macro_Update_Click()
On Error GoTo Err_Macro_Update_Click

Dim stDocName As String

stDocName = "Update"----------------- ----------------------------this is a command-
DoCmd.RunMacro stDocName ----------------------------------- button on the form
--------------------------------------------------------------------------------- that lets me add a
Exit_Macro_Update_Click:---------------------------------------------- a new date straight
Exit Sub-------------------------------------------------------------------- form the form, rather
------------------------------------------------------------------------------------ than having to go to
Err_Macro_Update_Click:----------------------------------------------- tbl.dates first.
-------------------------------------------------------------------------------------It's not my code
MsgBox Err.Description----------------------------------------------access did that auto-
Resume Exit_Macro_Update_Click------------------------------matically

End Sub
---------------------------------------------------------------------------------------------------------------
Private Sub cmd_open_frm_academicMonitoring_Click()
On Error GoTo Err_cmd_open_frm_academicMonitoring_Click

Dim stDocName As String-----------------------------------------------command
Dim stLinkCriteria As String--------------------------------------------- button opens a
------------------------------------------------------------------------------------------subform to
stDocName = "Academic-Monitoring"-------------------------------record academic
DoCmd.OpenForm stDocName, , , stLinkCriteria--------------- achievements

Exit_cmd_open_frm_academicMonitoring_Cli:
Exit Sub

Err_cmd_open_frm_academicMonitoring_Click:
MsgBox Err.Description
Resume Exit_cmd_open_frm_academicMonitoring_Cli

End Sub
----------------------------------------------------------------------------------------------------------------
Private Sub rqry_wbw_assignments_Click()
On Error GoTo Err_rqry_wbw_assignments_Click

Dim stDocName As String

stDocName = "WBWAssignments"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_rqry_wbw_assignments_Click:
Exit Sub ---------------------------------------------------------------------this last lot opens
------------------------------------------------------------------------------------- a form to see if
Err_rqry_wbw_assignments_Click:------------------------------------ previously used
MsgBox Err.Description------------------------------------------------ assignments are
Resume Exit_rqry_wbw_assignments_Click------------------- of any use for the
--------------------------------------------------------------------------------------- current day; the
End Sub -------------------------------------------------------------------------decision as to

whether or not
they are useful is
taken by me, not
the computer :):)

================================================== =======

[missinglinq]

You need to place the code, exactly as I posted it, in the code module behind your planner form! Do not change Form_Load to Form_lessonplans_load!

The only thing you need to do, as I indicated before, is to replace txtYourDate with the actual of your control that holds your date. If your modification

Me.Date.SetFocus

means that you've actually named your tetxbox Date, you need to change this to something else. Date is a reserved word in Access, and using it as the name of a textbox or field is going to cause major problems! The change can be minor, such as simply using txtDate instead of Date.

Linq ;0)>

[thecheyenne]

Fantastic!!! it works!!! Fantastic!!!
Thank you for the tip about reserved access words. my next step will be to find a list of all of them, so I don't use them to name tbl.fields. It took almost 20 minutes to change them all - the 'date' field is used in a number of other forms and reports as well - and re-set the links between the main-forms and subforms.
Thanks again:):):):):):)

[missinglinq]

Glad you got it working! Here's a link to a list of Reserved Words for the various versions of Access:

Reserved keywords in Microsoft Access 97, 2002, 2002 and 2003 | Database Solutions for Microsoft Access | databasedev.co.uk

Linq ;0)>