Delete combo box field automatically next year 1st may 2022

[erickemboi]

Hello everyone. Kindly please I am requesting for your asssistance to come up with a function or vba code to delete a field in a combo box next year 1ST MAY 2022 that is delete CLASS 6.
COMBO BOX FIELD


PP1
PP2
GRADE 1
GRADE 2
GRADE 3
GRADE 4
GRADE 5
GRADE 6
CLASS 6 (THIS FIELD TO BE DELETED AUTOMATICALLY NEXT YEAR 1ST MAY 2022)
Thanks in advance. God bless you.

[June7]

That is not deleting a field, it is removing a value from list.

Options:

1. code modifies combobox properties and saves form - this requires code to open form in design view, set RowSource, save form, not really practical in a split and distributed db

2. build a table to use as source for combobox

And what should happen May 2023? If this is a one-time edit, might as well do it manually.

[Welshgasman]

Put a flag in the table where that data comes from, do not hard code it, if the data is going to be changed all the time?
The flag could be a date field where you put in 01/05/2022. Then the Select for the combo ignores any data where Date > that field or is Null

Here is an example
[code]
SELECT TestTransactions.*, TestTransactions.TransactionDate
FROM TestTransactions
WHERE (((TestTransactions.TransactionDate)>Date())) OR (((TestTransactions.TransactionDate) Is Null));

[code]

[erickemboi]

Is there any way if changing CLASS 6 to GRADE 6 in 1st may 2022. I will appreciate. Thanks

[June7]

Automating this would require code that runs every time db opens. Code checks date and if it is May 1, 2022, continues with edit by one of already suggested methods.

[erickemboi]

Assist me with the code

[erickemboi]

Assist me with the code. Put the code to sample database i download. Thanks in advance. God bless you

[moke123]

Code checks date and if it is May 1, 2022,

Actually you would need to check if date is equal to or greater than 5/1/22 just in case the database is not opened on that date.

[Welshgasman]

Is this all going to happen again on 1st may 2023?

[Welshgasman]

Is there any way if changing CLASS 6 to GRADE 6 in 1st may 2022. I will appreciate. Thanks

You already have a Grade 6 ?

[moke123]

Not really clear what your endgame is but,

First get rid of the lookup field in your table.

Make a table tblGrades, with ClassNameID(autonumber), ClassName(text),Expires(date), InValid (yes/no)
Use that table as the rowsource of your combobox and in the criteria set InValid as false. Make sure to set your column count and widths, and what column you want bound to the field.

use a sub on startup, something like this...

Private Sub CheckDate()    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    
    strSql = "select * from  tblGrades"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)


    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If


    Do Until rs.EOF


        If Date >= rs!Expires Then


            rs.Edit
            rs!InValid = True
            rs.Update


        End If


        rs.MoveNext
    Loop


MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing


End Sub

[June7]

Don't you mean 'delete' instead of 'change'?

If you use a table as already described in post 3, wouldn't need any code.

[erickemboi]

CLASS 6 to be DONE AWAY WITH PERMANENTLY AFTER 1ST MAY 2022. DELETE PERMANENTLY CLASS 6

[moke123]

The problem with deleting the entry is that you are using a value list. In order to delete it you'll need to replace the rowsource string. It is easier to just flag the record in a table that it no longer should be included in the rowsource of the combobox.

Heres an example

[erickemboi]

i have this function to find the grade. i want to delete class 6 permanently. assist me

Function ConvertBirthdayToGrade(Birthday As Date, Optional SchoolYear As Variant) As String
Dim intGrade As Integer
On Error Resume Next
'5 YEARS -PP1
'6 YEARS -PP2
'7 YEARS- GRADE 1
'8 YEARS- GRADE 2
'9 YEARS- GRADE 3
'10 YEARS- GRADE 4
'11 YEARS- GRADE 5
'12 YEARS- CLASS 6(TO BE DELETED permanently ON 1ST MAY 2022)
'12 YEARS- GRADE 6
'13 YEARS- CLASS 7
'14 YEARS- CLASS 8

'If IsEmpty(SchoolYear) Then SchoolYear = Format(Date, "yyyy")

intGrade = CInt(Left(SchoolYear, 4)) - Year(Birthday)

Select Case intGrade
Case Is < 5
ConvertBirthdayToGrade = "NOT YET IN SCHOOL! LEARNER STILL VERY YOUNG!"
Case 5
ConvertBirthdayToGrade = "PP1"
Case 6
ConvertBirthdayToGrade = "PP2"
Case 7
ConvertBirthdayToGrade = "GRADE 1"
Case 8
ConvertBirthdayToGrade = "GRADE 2"
Case 9
ConvertBirthdayToGrade = "GRADE 3"
Case 10
ConvertBirthdayToGrade = "GRADE 4"
Case 11
ConvertBirthdayToGrade = "GRADE 5"
Case 12
ConvertBirthdayToGrade = "CLASS 6"(TO BE DELETED permanently ON 1ST MAY 2022)
Case 12
ConvertBirthdayToGrade = "GRADE 6"
Case 13
ConvertBirthdayToGrade = "CLASS 7"
Case 14
ConvertBirthdayToGrade = "CLASS 8"
Case Is >= 15
ConvertBirthdayToGrade = "LEARNER NO LONGER IN SCHOOL! CHECK BIRTH DATE!!"
End Select


End Function