[Access] Data from a field that depends on another Solved

Question

Hello  I am starting with Access 2010 and I have a small question: I am filling out a database of members and I would like Access to determine a field based on another field. Thus, I request the date of birth of a person and I would like that once this field is filled in, I can have another field "Category" that automatically takes the value according to the date of birth. Example: if I enter a date where the year of birth is before 1993, "Senior" should be indicated in the corresponding Category field; if I enter the year of birth 2000, it should indicate "Minimes". Thank you for your help.

Le Pingou · Answer

Hello, With an event procedure [Before Update] on the [date of birth] field (see under field properties..) and in the macro, retrieve the year and assign the category to the [Category] field.   -- Regards. The Penguin

nlbmoi · Answer

Good evening  Thank you for your help, but to be honest, I didn't really grasp what I was supposed to do :-(

Le Pingou · Answer

Hello, The principle is that you have a form (based on table T) to enter the data. Open the form in design mode, right-click on the [date of birth] field, and select [Properties] from the list. In the window that opens, select the [Event] tab and click on the [Before Update] line. On the far right, click on the small triangle and choose [Event Procedure] from the list, then click on the box (three small dots), and you will see the procedure [Private Sub date of birth_BeforeUpdate(Cancel As Integer)...... End Sub]. It is in this procedure that you need to write the code that allows you to determine the category and enter it into the [Category] field.   Best regards. The Penguin

nlbmoi · Answer

I tried to create this procedure (by searching on the internet) but it does not give me the expected result:  Option Compare Database  Private Sub Datenaissance_BeforeUpdate(Cancel As Integer) Select Case Year(Date_naissance)  Case Is >= 2007  Categorie = "Baby volley"  Case Is = 2005, 2006  Categorie = "Pupille"  Case Is = 2003, 2004  Categorie = "Poussin"  Case Is = 2001, 2002  Categorie = "Benjamin"  Case Is = 1999, 2000  Categorie = "Minime"  Case Is = 1997, 1998  Categorie = "Cadet"  Case Is = 1995, 1996  Categorie = "Junior"  Case Is = 1993, 1994  Categorie = "Espoir"  Case Is < 1993  Categorie = "Sénior"  End Select End Sub

Le Pingou · Answer

Hello, I tested your code and it works correctly. Where is the problem?   -- Regards. The Penguin

nlbmoi · Answer

I went to the form and when I enter a date of birth, it doesn't populate the category in my table.

Le Pingou · Answer

Hello, The [Category] field of the form must have as its data source the corresponding field from the table.   -- Regards. The Penguin

Le Pingou · Answer

Hello, Just a little thought, the category is not a fixed value as it depends on the date of birth and the current year, so it should not be stored in the table but rather determined as needed in the form or state.   -- Regards. The Penguin

nlbmoi · Answer

I’ve been searching, but I can't find how: once I've filled in the code in the date_of_birth properties (following the method you indicated), what should I select in the category properties?

Le Pingou · Answer

Hello, I assume you have read the message with my thoughts.... ! In the form, you have the field [Categorie], so right-click and click on [Properties], in the [Data] tab under [Control Source] you should have the name of your table field.   -- Best regards. The Penguin

nlbmoi · Answer

In [Source control], I have Category I thought it corresponded to the same variable as the one from the procedure.

Le Pingou · Answer

Hello, Well, does it work now....? Question: next year, half of your members will be in the wrong category as it depends on age..... have you thought about it.....? -- Regards. The Penguin

nlbmoi · Answer

No, once the birth date is entered, I have nothing that shows up in Category.  For next year, the age categories do depend on the year of birth, I will just change the years in the procedure to update the correct category.

Le Pingou · Answer

Hello, Next year, it will not be by changing the procedure that the recorded data will be updated... you will experience it...! Furthermore, it is not a good solution to have to change the procedure every year; it would be better to work with age categories such as: Baby volleyball for 7-8 years old, Pupils for 9-10 years old, etc......  Second point: regarding the birth date and category, you surely have an error either in the form fields or in the table, make sure to check the spelling of the names!   -- Regards. The Penguin

nlbmoi · Answer

I believe I am starting to understand the phrase "For next year, it is not by changing the procedure that the recorded data will be updated.... you will experience it...!"  I have changed the code of the procedure taking your comments into account:  Option Compare Database  Private Sub Datenaissance_BeforeUpdate(Cancel As Integer) Dim Age As Long Age = Year(Date) - Year(Datenaissance) Select Case Age  Case Is  20  Categorie = "Sénior"  End Select End Sub  I tested it on my form and I feel like it works. However, I feel that once the date is entered, indeed the category remains the same even if I change something in the procedure: on one hand, this suits me because I do not have a change in age category on January 1st of the following year (an age category is defined for a sports season, thus overlapping two years). But I will have a problem for next season when I want to modify my database: the indicated age categories will be those of this year and not those of next year. Is there a way to circumvent this problem (other than re-entering all the records)?

Le Pingou · Answer

Hello, Regarding the category, I had suggested this: A little thought, the category is not a fixed value since it depends on the date of birth and the current year, so it should not be stored in the table but we will determine it according to the needs in the form or the state.  Feel free to ignore this, and in that case you can create an update query that you would run at the beginning of each season.  For the category assignment, you can use the season as a basis instead of the current year.    -- Best regards. The Penguin

nlbmoi · Answer

I actually think I didn't understand what that bolded sentence meant.

[Access] Data from a field that depends on another

17 answers

Reinstallation of w11 impossible after pc crash...

Puk code and password on samsung

Texture issues in enshrouded

Unrefreshed usb drive on android interface

Free phone no tone..

Pcmcia module for tntsat card

Taskbar that hides

Phone not receiving certain sms

Intrusive ads

Chkdsk /f and sfc /scannow okay, but startup impossible.