Open form to specific record - Today!!

[ggs]

Hi
I Have a DB for a One year period. Each Day is a unique record.
Each Day will only have one entry per control
(I tried to use a date as PK but gave up on populating it, or rather not being able to)


The pK is autonumber and the date is field [Date2Day]
Now I have a Menu form with buttons.
One I would like to open frmD and have it open on todays date record or have a button on frmD that would goto Date2Day
I know the search box will do that but I would like it to be really user friendly
I have the control Date2Day at the top of the form with a button to the left and right (arrows ) to navigate yesterday tomorrow it is just the form opens always to 1st Jan
I feel this is going to be an easy answer and make me feel stupid oh well

[June7]

You can set the form as DataEntry Yes and this will open only to new record, cannot view existing records. Otherwise will need code to open form then go to new record. That can be automatic when form opens or after user clicks button. Can be macro or VBA code. You can create button on form and let the wizard guide you through setting up macro code. Is that how you created the forward and back buttons?

[ssanfu]

Hi
I Have a DB for a One year period. Each Day is a unique record.

Be sure and set (in table design view) the property "Indexed" to "Yes (No Duplicates)"

If you have the dates for the year already entered in the table, put this code in the form "frmD" open event:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  Dim strCriteria As String

  strCriteria = "[date2day] = # " & Date & "#"
  Set rs = Me.RecordsetClone

  rs.FindFirst strCriteria
  If rs.NoMatch Then
    MsgBox "No entry found"
  Else
    Me.Bookmark = rs.Bookmark
  End If

End Sub

When "frmD" opens, today's date will be selected.

[June7]

Prepopulated records, didn't even occur to me! But on re-read does seem to be what OP has.

[ggs]

Thanks both for your help. June7 Tried to thank you but MsgBox "You have to spread some thanks around before you can thank June7 again"
Hope that doesn't mean you have to stop helping me

- The DB - yes, I had prepopulated [Date2Day] after trying allsorts (not licorice) I Excel ed Jan1 thru Dec31 and imported. Cheating I know
Only thing I now need to make up some templates 2013, 2014 etc (thats hopeful)
Anyway the code worked like magic and is just what I wanted

[ssanfu]

Thanks both for your help. June7 Tried to thank you but MsgBox "You have to spread some thanks around before you can thank June7 again"
Hope that doesn't mean you have to stop helping me

- The DB - yes, I had prepopulated [Date2Day] after trying allsorts (not licorice) I Excel ed Jan1 thru Dec31 and imported. Cheating I know
Only thing I now need to make up some templates 2013, 2014 etc (thats hopeful)
Anyway the code worked like magic and is just what I wanted

Instead of using Excel, here is some code to help you along. I didn't add any comments... Your task is to understand how this works. There WILL be a test....

Copy the code and put it into a module. Change "YourTable" to the name of your table. Then press execute (F5) to run or (F8) to single step the code.

Public Sub AddDates()
   Const BegDate As Date = #1/1/2013#
   Dim i As Integer
   Dim sSQL As String
   Dim TheDate As Date

   For i = 0 To 364
      TheDate = DateAdd("d", i, BegDate)

      sSQL = "INSERT INTO YourTable (Date2Day) VALUES (#" & TheDate & "#);"
      CurrentDb.Execute sSQL, dbFailOnError

   Next

   MsgBox "Done"

End Sub

**This is quick and dirty. Does not take into account Leap years.** You might be missing the last day of the year.

Do you have the Indexed property for the field field "Date2Date" set to "Yes (No Duplicates)"? This will ensure you cannot have two dates that are the same.

[June7]

Just what I was thinking of! (Except there are 365 days in non-leap year). Could add some code to check if current year is a leap year and accommodate that in the loop structure. Derived from http://www.mrexcel.com/forum/showthread.php?t=70215 suggestion is:

Dim intDays As Integer
intDays = IIf(IsDate("2/29/" & Year(BegDate)), 366, 365)
For i = 0 To intDays

[ssanfu]

Just what I was thinking of! (Except there are 365 days in non-leap year). Could add some code to check if current year is a leap year and accommodate that in the loop structure. Derived from http://www.mrexcel.com/forum/showthread.php?t=70215 suggestion is:

Dim intDays As Integer
intDays = IIf(IsDate("2/29/" & Year(BegDate)), 366, 365)
For i = 0 To intDays

The counter is zero based.
0 to 364 = 365 days

could use i = 1 to 365 but then the constant BegDate should be the last day of the prev year ie 12/31/2011.. sometimes confusing.....


So I opted the use Jan 1 and adjust the loop.... many ways to skin the cat. (remember this was quick and dirty)

But that was a slick way to take into account leap years.

[ggs]

i thought Excel ing the date was the quick and dirty. Yes I had set up my [Date2Day] as Indexed and No Duplicates. Not sure if I passed the test or not. It works and the dates are now in order so I will remain ignorant as to how or why my PK is this strange number somehow representing the date
I'll have a look at the leap year thing at the end of 2015

[June7]

My ooops. Case of seeing what I thought was there. Adjust my suggestion:

intDays = IIf(IsDate("2/29/" & Year(BegDate)), 365, 364)

[ggs]

You are probably wondering what Im raving about. For some reason excel started the ID as the date code for 1/1 plus the 31 entries Id already made so the PK number started at 41040 then my 2013 DB started at 365 more than that.
So I started from scratch with PK now starting at 1. I don't understand why the dates are out of order, but now I have something else to think about.
My Database thinks today is 2/1/2012. I'm picking you are going to tell me it is ... not down here it's not Its 1st February 2012 not 2nd January 2012. Dang thats stumped me I thought the computer worked on local settings

[June7]

Although the Access interface can recognize local settings, VBA will not. I know this drives much of the rest of world crazy trying to deal with it. Maybe Allen can explain it http://allenbrowne.com/ser-36.html