469,125 Members | 1,835 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

open form after checking system date

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
Jan 2 '09 #1
5 1422
3,532 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.   Me.txtYourDate.SetFocus
  3.   DoCmd.FindRecord Date
  4. End Sub
Welcome to Bytes!

Linq ;0)>
Jan 3 '09 #2
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,
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 Sub

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 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 :):)

================================================== =======
Jan 3 '09 #3
3,532 Expert 2GB
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


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)>
Jan 3 '09 #4
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:):):):):):)
Jan 3 '09 #5
3,532 Expert 2GB
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)>
Jan 3 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Job Lot | last post: by
3 posts views Thread by rdemyan via AccessMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.