Need a code automatically generated based on data from a form

[Iram]

Hello,
I am using Access 2003.

I have a field called "Codigo" in a form. I would like to create a button
next to it so that when I click it, data from the form becomes concatenated
and helps create the codigo which is the code in english.

I need the following...

The first letter + the first vowel of the field called "Paterno" field. If a
vowel doesn't exist after the first letter than we will need to use the
second letter regardless of vowel or no vowel.
+
The first letter of "Materno" field
+
The first letter of "Nombre" field
+
A dash
+
The "Date of birth" field in format (##/##/##).

Example:
"Paterno" field: Alvarez (AA)
"Materno" field: Diaz (D)
"Nombre" field: George (G)
"DOB" field: 07/21/74

I need to concatenate the above example into a field called "Condigo" with
with

AADG-072174


Can you help me with this? Otherwise I will have to type this in for every
applicant manually.

Your help is greatly appreciated.

Thanks.
Iram/mcp

 

[Steve Sanford]

Try this...... it could be tighened up.....

'---------------------------------------------
Option Compare Database
Option Explicit '<<=should always have this

'my button is named "Command5"
'change to your button name

Private Sub Command5_Click()
On Error GoTo HandleErr

Dim tmpStr As String
Dim sP As String
Dim sM As String
Dim sN As String
Dim dtDOB As String
Dim sC As String
Dim i As Integer
Dim OK As Boolean

OK = False

'get first letter of Paterno
sP = UCase(Left(Me.Paterno, 1))

'loop thru the name starting at the second letter
For i = 2 To Len(Me.Paterno)
tmpStr = UCase(Mid(Me.Paterno, i, 1))
'is letter in the string?
OK = InStr(1, "AEIOU", tmpStr)

If OK Then
'found a vowel - get out
Exit For
End If
Next

'get second letter of Paterno
If OK Then
'vowel
sP = sP & tmpStr
Else
'no vowel
sP = sP & UCase(Mid(Me.Paterno, 2, 1))
End If

' get letter from Materno
If Len(Trim(Nz(Me.Materno, ""))) > 0 Then
sM = UCase(Left(Me.Materno, 1))
End If

' get letter from Nombre
If Len(Trim(Nz(Me.Nombre, ""))) > 0 Then
sN = UCase(Left(Me.Nombre, 1))
End If


'process DOB
dtDOB = Format(Month(Me.DOB), "00") & _
Format(Day(Me.DOB), "00") & _
Right(Year(Me.DOB), 2)

'add them together
Me.Codigo = sP & sM & sN & "-" & dtDOB

Handle_Exit:
Exit Sub

HandleErr:
MsgBox Err.Number, Err.Description
Resume Handle_Exit

End Sub
'---------------------------------------------


HTH

 

[Iram]

Impressive Steve!

I am going to try it later today because I have to go to church. The actual
field names are a little different however I figure that I can replace for
example all of the "Paterno" fields with "ApellidoPaterno" and so on for the
other field names in the below code you sent me . Is this correct?


Iram.

 

[Iram]

I just want everyone to know that Steve Sanford is teh "MAN".
I just tested it and it worked awesome!


Thanks Steve.



Iram/mcp

 

[Iram]

Question:
I am creating this db in the US with Windows XP with the US region, however
this db will be sent to Mexico to be used. The computers in Mexico have the
Mexican region. They like to enter the dates such as the DOB and other dates
in the following format:

Year/Month/Day format ##/##/##

Will your code work for them, the folks using this db with the Mexican
Region settings? The date fields in the DB are "Date/Time" types.
Will there be any problems?


Iram/mcp

 

[John W. Vinson]

Hello,
I am using Access 2003.

I have a field called "Codigo" in a form. I would like to create a button
next to it so that when I click it, data from the form becomes concatenated
and helps create the codigo which is the code in english.

I need the following...

The first letter + the first vowel of the field called "Paterno" field. If a
vowel doesn't exist after the first letter than we will need to use the
second letter regardless of vowel or no vowel.
+
The first letter of "Materno" field
+
The first letter of "Nombre" field
+
A dash
+
The "Date of birth" field in format (##/##/##).

Example:
"Paterno" field: Alvarez (AA)
"Materno" field: Diaz (D)
"Nombre" field: George (G)
"DOB" field: 07/21/74

I need to concatenate the above example into a field called "Condigo" with
with

AADG-072174

Just be aware that you have NO guarantee that Condigo will be unique. You
could very easily have two people with the same letters in their names (or for
that matter the same name!) and coincidentally the same birthdate. If you're
planning to use this field as a unique identifier you're making a mistake!!

 

[Steve Sanford]

Sorry, I don't know.

You could reconfigure your computer for the Mexican local and test the MDB.

I think it *should* run OK because of the Month, Day and Year functions. It
would be a different matter if you were trying to parse a "date" that is a
text string.

 

[Iram]

Thanks for watching out. We have thought about this as well and when this
occurs we will manually add a -1 to the end of the "codigo". The problem is
that in Mexico not everyone has an ID of some sort so we thought of this
little formula for the short term.

Again thanks John Vinson.


Iram