Automatically generate a record in a table

[scallebe]

Hi specialists,



I have a small table (jaren) with different seasons like 2012-2013, 2013-2014, 2014-2015, ...
I use this table for a combobox in a small form to set the parameters in a query. I open the form, select the season I want and then I click a button to open the report.

works perfectly

.

My question if it's possible ...

I would like to generate a new record in that table every 1st of september because e

very season begins 1st of september.

So this year it would be 2018-2019

And the next time I open the database after 09/01 Access should not create the record because it's already there.

Now I have to create the new season myself, no problem of course, but it would be great if it goes automatically.


Is this possible? With VBA?


Thanks

Greetz

[kd2017]

Yes it's possible.

See https://stackoverflow.com/questions/...-database-open

You'd simply make a sub that checks your table for the record, if it doesn't exist then create it. Can you code vba?

[scallebe]

kd2017,


A little... First I did a basic vba in excell and now I start with access

I will check your link...

Thanks

[scallebe]

kd2017,

I try to learn and understand VBA by recording macro's (Excell) I know it's not the best way because I know in VBA many thing need less code then in a recording macro creates...

In access there is no way to "record" a macro, so I'm very very basic

Greetz

[kd2017]

Before going that route you might could add a field to your seasons table like "ValidAfter", and on each row have 9/1/year . Then in your combobox just modify the rowsource query to only display rows with "ValidAfter" dates before the current date ( Date() ). You could add a row for the next 100 yrs and then forget about it.

[davegri]

This code will do it, but we need to find a way for your db to run it.
1. via the AutoExec macro (a bit complicated).
2. In the Form_Open event, if your db automatically opens a form when it starts (easiest).

Public Sub CheckSeason()
    Dim sSQL As String
    Dim strSeas As String
    strSeas = Year(Date) & "-" & Year(Date) + 1
     'Debug.Print strSeas
    If Month(Date) <> 9 Then Exit Sub
    If DLookup("Season", "Jaren", "Season='" & strSeas & "'") = strSeas Then Exit Sub
    sSQL = "INSERT INTO Jaren (Season) VALUES ('" & strSeas & "')"
    CurrentDb.Execute sSQL, dbFailOnError
End Sub

[scallebe]

kd2017,

Thanks for your sollution. I think the next 40 years will do ... I'm 53

I modify that in the criteria of the ValidAfter field in the combobox query?

I will definitely try it.


Thanks

[kd2017]

You'd modify the table to add the new field, then you'd update the table, then you'd modify the query used by the combobox.

davegri's code would work too

[scallebe]

Davegri,

The first form that opens when I start my DB is form_switchboard.

There is already a code on Form_Open

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True
    
End Sub

I ad your code there?

Thanks

Pascal

[davegri]

I would to it this way. You can change the red to <> 3 to test, then deleted that record if not wanted.
This code assumes that the table name is jaren and the field name is Season.

Private Sub Form_Open(Cancel As Integer)
    'Check for September and add record if it's not already there
    Dim sSQL As String
    Dim strSeas As String
    strSeas = Year(Date) & "-" & Year(Date) + 1
     'Debug.Print strSeas
    If Month(Date) <> 9 Then Exit Sub
    If DLookup("Season", "Jaren", "Season='" & strSeas & "'") = strSeas Then Exit Sub
    sSQL = "INSERT INTO Jaren (Season) VALUES ('" & strSeas & "')"
    CurrentDb.Execute sSQL, dbFailOnError

    ' Minimize the database window and initialize the form.
    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True
  End Sub

[scallebe]

davegri,

Thanks for your reply

It's working FANTASTIC!!!

Thank you so much


Greetz

Pascal

[davegri]

Noticed a problem. The code you had before is never executed unless the month is September.
So, rearrange it this way, keeping the old code first:

Private Sub Form_Open(Cancel As Integer)
    ' Minimize the database window and initialize the form.
    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True   

     'Check for September and add record if it's not already there
    Dim sSQL As String
    Dim strSeas As String
    strSeas = Year(Date) & "-" & Year(Date) + 1
     'Debug.Print strSeas
    If Month(Date) <> 9 Then Exit Sub
    If DLookup("Season", "Jaren", "Season='" & strSeas & "'") = strSeas Then Exit Sub
    sSQL = "INSERT INTO Jaren (Season) VALUES ('" & strSeas & "')"
    CurrentDb.Execute sSQL, dbFailOnError
  End Sub

[scallebe]

Davegri,

I already thouth so, I know that the order for VBA is impotant, so I put your code behind the existing code and it's working just fine.

Thanks again

[Micron]

cross posted at
https://www.mrexcel.com/forum/micros...ord-table.html