increase a number by 1 in a field on the same day each year automatically

[Micron]

If this could be done automatically

You now have 2 suggestions on how to do that; plus whatever else you find that you prefer.

[jetanotherone]

Thank you for your help. The automatic way would be preferably.
You mentioned (use an AutoExec macro to call a function to figure out if it's time, and either prompt or not. Or the opening of any form that is pretty much guaranteed to be opened can do the check.) Beginning of each year the form [Students Maintenance] will be opened so I guess that would be where to put such a code. I'm sorry to say however that I'm a total novices and have absolutely no idea of how to go about creating an AutoExec macro or what to put into it.

I can see I have a lot to learn.....

[June7]

AutoExec is not needed if you use code behind form.

[Micron]

I/we can help with that. A few issues might have to be worked out first. Best approach might be for you to upload a small db with dummy data, but contains the necessary table(s) and form(s) to make it happen. These (and fields, controls, etc) should have the same name as what you will have in the final db so that you don't have to be concerned about figuring out what has to be renamed to make things work in the final version. In absence of that, code can be provided but it will be up to you to modify to suit and make it work. If you do post a db, make sure it doesn't contain features that are not backwards compatible. I haven't found any issues regarding compatibility between 2016 and 2010, but here's a heads up regarding 2010 vs 2007.

One thing that is important is to figure out what prevents the update from arbitrary execution - so that it can't be done 01/21 and 01/22 and 01/23...each time the form is opened, even if you only plan to open it once a year because there's no guarantee about that. A prompt to confirm would be OK but can become annoying if the form is opened frequently as a matter of procedure. An UpdateDate field in the same table as Scholastic might be the way to go, then the suggestions from posts 7+8 should work.

Thus, on 01/20, if the UpdateDate field is Null or the year portion of that date <> the current year, run the update. The UpdateDate would have to be part of each student record in the table containing the Scholastic year field.

[jetanotherone]

Thank You so much to all that have helped. It would be great to get this sorted before school starts again.
When I created this DB 15 years ago it was not so complex and it was my first experience with databases. My knowledge was trial and error and what I could find through google and of cause trying to meet our needs at the school as we do not fit into mainstream schooling. When you look at my database I'm sure you will find lots of (not so good practices) but it seems to work somehow. I'm trying to set things up so the database can be used by office staff without knowledge of Access database and to do little else apart from entering data into a form and selecting the reports as needed. It is therefor the reason for the Scholastic year being automated or else someone will need to change this each year for each student by one.

here is a copy of my database without the confidential info...
https://schoolsnsw-my.sharepoint.com...3a54%3a20.000Z

Hope this link worked.
Thank you once again for your time and help.

[isladogs]

The site won't allow me to look at your database. Permissions issue

You might be interested in my schools database -School Data Analyser
Demo version available here with student, staff and school data for a fictitious school.
http://www.mendipdatasystems.co.uk/s...ser/4584605482

[jetanotherone]

The site won't allow me to look at your database. Permissions issue

You might be interested in my schools database -School Data Analyser
Demo version available here with student, staff and school data for a fictitious school.
http://www.mendipdatasystems.co.uk/s...ser/4584605482

download to your computer, then hold the shift key down when opening. It worked when I tried it.

[Micron]

I opened it ok. A little instruction would help, like which forms to open or buttons to click on...

[June7]

Had to use Chrome to download, IE would not cooperate.

Why is Classes table included in the Relationships 3 times - all linked to the same field?

Did you try my suggested code anywhere? MainSwitchboard form is one possible location.


Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.

[isladogs]

I can't download it at all.
Asks for my office 365 info then says that's not in your database

[Micron]

Interesting. I just Save As with Firefox and move the file to an appropriate folder. Anyway, notwithstanding any valued comments regarding your structure ( I have several issues myself) here's how you can satisfy the question that started this thread:

**add table tblDbParams. Add ParamName and ParamValue fields. Enter 'Scholastic Update Date' and 12/24/2015 as values like this

ParamName	ParamValue
Scholastic Update Date	12/24/2015

The date might be OK as part of the student record as I first envisioned, but it makes little sense to apply that to every student as repetitive data when the needs you expressed don't support that. Better to start with a table of parameters that apply to the db as a whole. This is common amongst db's I've created that have similar update requirements. It gives you the ability to add other parameters should you need to define them in the future.

**add a reference to DAO 3.6 type library unless you have a higher version, then add that reference instead
**for the student reference form, create a form Open event (from the property sheet Event tab) and add this code between the Sub and End Sub lines:

Dim db As DAO.Database
Dim ws As DAO.Workspace
Dim blnInTrans As Boolean
Dim strSql As String, strSql2 As String
Dim msg As String

On Error GoTo errHandler

'I think June7's code suggestion would only work on the 20th of December.
'I added > to the = parts & made it January per the requirement
If Month(Date) >= 1 And Day(Date) >= 20 Then
'has the update already occurred this year?
   If Year(DLookup("ParamValue", "tblDbParams", "ParamName = 'Scholastic Update Date'")) < Year(Date) Then
      Set ws = DBEngine(0) 'create workspace for the transaction
      Set db = ws(0) 'set the db object
      strSql = "UPDATE Students SET Students.Scholastic = Students.Scholastic + 1"
      strSql2 = "UPDATE tblDbParams SET tblDbParams.paramvalue = date() WHERE ParamName = 'Scholastic Update Date'"
      ws.BeginTrans 'start the transaction and set the boolean value to indicate this
         blnInTrans = True
         db.Execute strSql, dbFailOnError 'attempt execute the two update queries
         db.Execute strSql2, dbFailOnError
      'if no error, transaction gets committed
      ws.CommitTrans
       blnInTrans = False 'probably not req'd
   End If
End If

exitHere:
Set ws = Nothing
Set db = Nothing
Exit Sub

errHandler:
'if any error, create and present message. Traps for various error numbers could be added
msg = "Error " & Err.Number & " occurred during the automatic update of the scholastic years." & vbCrLf
msg = msg & "No changes will be made." & vbCrLf & vbCrLf
msg = msg & "Please notify the database administrator."
MsgBox msg
If blnInTrans Then
   ws.Rollback
   blnInTrans = False
End If
Resume exitHere

To cause an update without altering your pc clock you would have to alter this line
If Month(Date) >= 1 And Day(Date) >= 20 Then
so that the month is <= the current month and the day is >= the current day date. This illustrates another reason to have tlbDbParams: the date on which you want to enforce the update can be a parameter itself. Thus hard coded values can be avoided, and the db admin need only change the table field parameters without altering code.

NOTE: implement the above on a copy of your db or copies of the affected objects. Likely this is it for a few days, considering the time of year.

Also, I suggest you get this working so the thread can be solved. You could do well to start another one where you address the design issues, some of which have been raised. I have a few concerns myself, but that's really another thread.

Hope you like Santa Micron's Christmas present.
Merry Christmas to my esteemed Access Forums collaborators!

EDIT:
you need a date value in tblParams otherwise this
If Year(DLookup("ParamValue", "tblDbParams", "ParamName = 'Scholastic Update Date'")) < Year(Date)
will return Null. Rather than edit code at this point, I leave it to you to provide a date. Suggest something from 2016 if you want the code to update the scholastic year for testing.

[jetanotherone]

Hi Santa Micron

Thank you for your time and effort.
I have just learnt how little I know and how it's a miracle that I managed to create a DB that has served our purpose of sorts.

I created the table tblDbParams. and added the fields ParamName and ParamValue . I then entered 'Scholastic Update Date' and 12/24/2015 as values exactly as it is written.

I then opened the form (students Maintenance) went to the -property sheet, event, on open - selected [event Procedure] and that is where I ran into problems...... I had to googled what DOA was and how to change it but got this message:


a DLL error....
you said to reference a higher version if that is what I have.... again this is my lack of knowledge. this is what reference options I have:

So if I have a higher version where and how do I reference that? I google a few things but I thing it lead me on the wrong path as it said something about using CurrentDb()?? but then it does't like the dim ws statement.....

I'm not expecting you to spend any more of your precious time but thought you should know where I'm at.

It's obvious I'm way out of my depth with this and have a lot to learn.....

Once again
THANK YOU!

[ssanfu]

Instead of trying to add a reference to "Microsoft DAO 3.6" type library, try adding "Microsoft Office XX.0 Object Library".
(for Access2010, xx = 14. Access 2016 might have a different number)

[isladogs]

The reference you need is called Microsoft Office Access Database Engine Object Library
It will have a version number e.g. 14.0 for Access 2010 ; 15.0 for A2013 ; 16.0 for A2016.

[Micron]

I think the last two posts are incorrect because you need DAO to create the transaction. Here's the thing: someone moved my laptop to a "safer" place then someone spilled vegetable juice on the keyboard. It seems to be fried so not much I can do until I have something to use besides my phone. Merry effing ho ho to me. Hang tight or work with others if you wish. I can tell you that I had the code working on my end so whatever is wrong at your end should be solvable.