Automatically fill another field in a form

[Frankie]

Hi,

In my form users must enter data about new employees.

One of the fields is the birthdate of an employee. The next field is a
registration number that looks like this: 0YYMMDD.

0: first digit that is always a zero unless the registration number already
has been stored, in this case the first digit will be a 1, etc.
YY: year of birth (without century)
MM: month, e.g. 01, 02...12
DD: day, e.g. 01, 02...31.

My purpose is that the field 'registration number' is filled automatically
using the value from the field 'birth date'. It would be great if
automatically is checked whether a registration number already exists and
the first digit is raised from 0 into 1, 2 etc. This last step would be
perfect but if the field 'registration number' is automatically filled with
'0YYMMDD' I will be greatfull.

Thanks.

Frank

[Allen Browne]

Use the AfterUpdate event procedure of your birthdate field to lookup the
maximum prefix used so far, and assign the registration number.

This kind of thing:

Private Sub Birthdate_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim iNextNum As Integer

If Not IsNull(Me.birthdate) Then
strWhere = "[Registration Number] Like ""#" & _
Format(Me.[birthdate], "yymmdd") & """"

varResult = DMax("Registration Number", "MyTable", strWhere)
If Not IsNull(Me.varResult) Then
iNextNum = CInt(Left(varResult, 1)) + 1
End If

If iNextNum < 10 Then
Me.[Registration Number] = Trim(Str(iNextNum)) & _
Format(Me.[birthdate], "yymmdd")
End If
End If
End Sub
In a multi-user environment, you may want to do this in Form_BeforeUpdate
instead, so it is calculated at the last possible moment, to reduce the
chances that 2 users are given the same result.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frankie" <f.*********@tiscali.nl> wrote in message
news:41**********************@dreader2.news.tiscal i.nl...

In my form users must enter data about new employees.

One of the fields is the birthdate of an employee. The next field is a
registration number that looks like this: 0YYMMDD.

0: first digit that is always a zero unless the registration number
already has been stored, in this case the first digit will be a 1, etc.
YY: year of birth (without century)
MM: month, e.g. 01, 02...12
DD: day, e.g. 01, 02...31.

My purpose is that the field 'registration number' is filled automatically
using the value from the field 'birth date'. It would be great if
automatically is checked whether a registration number already exists and
the first digit is raised from 0 into 1, 2 etc. This last step would be
perfect but if the field 'registration number' is automatically filled
with '0YYMMDD' I will be greatfull.