Acc97: Combining Values & Text

[noodnutt]

G'Day ppl,

I would like the following to happen using an AfterUpdate code. I have 3
fields in question that I need to extract specific info from and combine it
into 1 field, eg.

EmpArea 1
EmpLast Rider
EmpFirst Ghost

So in the AfterUpdate of "EmpFirst" it will insert into the "EmpCode" field
1RidG.

Then, (and this is where it gets tricky) In the Before Update of "EmpCode"
would like it to check if there is an existing "EmpCode" that already
exists. If it doesn't then I would like it to place a (1) at the end of the
empcode so it reads 1RidG1. If "EmpCode" already has 1RidG1 then make it
1RidG2.

Looking forward to your thoughts and ideas ppl.

TIA

Mark.

 

[Klatuu]

Private Sub EmpCode_BeforeUpdate()
Dim intX As Integer
Dim intY As Integer
Dim strNumber As String
Dim blnDone As Boolean

intX = 0
blnDone = False
Do Until blnDone
intX = intX + 1
If Not IsNumeric(Right(Me.EmpCode, intX)) Then
blnDone = True
End If
Loop
If intX = 1 Then
Me.EmpCode = Me.EmpCode & "1" ' Put a 1 on the end
Else
intX = intX - 1
intY = CInt(Right(sMe.EmpCode, intX)) + 1
Me.EmpCode = Left(Me.EmpCode, Len(strCode) - intX)
strNumber = CStr(intY)
Me.EmpCode = Me.EmpCode & strNumber
End If
End sub

You did not say how the code should be constructed, so I am assuming, based
on your post, that it is Area + First 3 Characters of EmpLast + First
Character of EmpFirst. Easy enough:

Me.EmpCode = Area & Left(EmpLast,3) & Left(EmpFirst,1)

 

[noodnutt]

G'day Klatuu

Thx for your assistance, alas there seems to be a bug with this section of
the code which I have used Quotes to highlight

Me.EmpCode = Left(Me.EmpCode, "Len(strCode)" - intX)

The Compile Error message states that: (Variable not defined)

I changed it to Len(Me.EmpCode), but that didn't work either.

I will try and explain systematically how the code should work.

Lets assume I have a fresh DB, just installed with no data inputted, the DKE
operator then starts to enter all the employees into the DB.

the EmpArea, EmpLast(1st 3 Char), EmpFirst(1st Char only)! so we assume that
Shane Williams works in area 1, so his code/ID will be 1WilS, consequently
he will have a 1 at the end (1WilS1), so if the next employee is Steven
Wilson working in the same area, his code/ID would be 1WilS2 and then 3, 4,
5, 6 and so on for anyone who has the same name configuration outcome of
code/ID structure.

What would be extremely helpful for me (being somewhat limited in my
understanding of VBA) is if you could break the code up into sections with
explainations of what it is each section does so I can get a better
understanding.

I sincerely hope this will clarify what it is I am attempting to achieve.

Many thx again.

Reg's

Mark.

 

[noodnutt]

Oops!

I screwed up in my last post.

"Lets assume I have a fresh DB, just installed with no data inputted, the
DKE operator then starts to enter all the employees into the DB."

After he/she has entered the area in which the employye will be working and
the full last name & first name the employee code/ID will auto generate his
or her code using the following structure:

EmpArea(#), EmpLast(1st 3 Char), EmpFirst(1st Char only) & if he/she is the
first employee to have the a unique employee code/id then it should
automatically have a (1) at the end.

Sorry about the confusion.

Thx heaps again

Mark.