Full Version: Reverse Name
UtterAccess Forums > Microsoft® Access > Access Forms
kapeller
Hi All!!!!
seek assistance with the following question.
I have a combo control titled txtAuditor on a form and when I select a name it is display as eg Kapeller Lou (this is how the name has been record. There are reasons for this)
I have another control titled txtAuditorName where on lost focus on the txtAuditor control the txtAuditorName displays the name as Lou Kapeller. (required in this format for an other reason)
Is it possible to do this?
Cheers!!!!!!
Lou
cheekybuddha
Hi Lou,
hilst it would be better to split the name into FirstName and Surname if possible for storage and then concatenate the 2 in the required order for display, you can probaly achieve what you need with a bit of code.
Of course there are caveats - what happens if there are 3 names, or a double-barrelled surname that contains a space?
CODE
Private Sub txtAuditor_LostFocus()
    Dim strNames() As String    ' Use an array
    Dim strRet As String        ' Result variable
    Dim b As Byte              ' Counter
    If Len(Me.txtAuditor) > 0 Then
        strNames() = Split(Me.txtAuditor, " ")
        For b = Ubound(strNames) to LBound(StrNames) Step -1
            strRet = strRet & " " & strNames(b)
        Next b
        strRet = Trim(strRet)
        Me.txtAuditorName = strRet
    End If
End Sub

hth,
d
kapeller
Hi!!!!!
Thank you very much for the quick response. I understand the caveats. We just had a busniess rule change that has prompted this question.
Originally I wanted to have two fields in the table for given name and family name but got over ruled.
So once again many thanks
Cheers!!!!
lou
cheekybuddha
Lou, you're welcome,
I hope it worked - I didn't test it! You may have to adjust it according to your needs)
You're working a bit late aren't you?!
thumbup.gif
d
kapeller
Hi!!!
Just one small issue I get an error message "Run-time error 6" Overflow.
The code stops at this point
CODE
         For b = UBound(strNames) To LBound(strNames) Step -1

Any ideas?
Cheers!!!!
Lou
cheekybuddha
Sorry, I forgot that we were decrementing the counter.
ytes can not be negative.
Change the declaration of b to an Integer and all should be well.
hth,
d
argeedblu
Lou,
hate to disagree with cheeky, but I think it would be better to bite the bullet at this time and modify the table structure so that there are separate fields for each segment of a name (first, middle, last.) This is a basic normalization issue. Whoever overruled you needs to be educated about data management. The change in business rules is a perfect example of why having a single name field was a bad idea in the first place. Don't waste time with work arounds. Use that time to fix the problem so that it doesn't come back to haunt you again in the future.
Glenn
kapeller
Once again many thanks.
Yes I just have to complete a couple of small items for tomorrow.
If you are into cricket, ticket sales are starting to go crazy for when the English come out our summer. Should be very good. Especially the traditional Boxing Day Test at our new MCG.
Cheers!!!
Lou
kapeller
Hi Glenn!!!
intend to take your advice onboard and over the coming weeks modify this part of the structure. I just need to demonstrate a new idea by tomorrow.
Oknow that once I complete the demo and explain their original short comings your advice will then be accepted.
Many thanks
Have a great day.
Cheers!!!!
Lou
cheekybuddha
Glenn, thanks for making that particular point. Lou is an experienced campaigner here at UA, so I thought it better not to teach granny to suck eggs.
However, since you have made the point, I must agree with you. Lou, even if you can't get the powers that be to understand, since you are the designer you could simply adjust the structure and display the info as required without anyone being any the wiser!
On the cricket front, how I would love to be in Aus this winter (summer wink.gif ) - but I fear it will several late nights in front of the telly! Still, unlike last summer here, it might mean I can get some work done during the day!!!!!
Regards,
d
Spacecase69
My 2cents...
ever let the "users" dictate table design...
Unless one of the users is your boss and he thinks he knows database design (and doesn't listen to reason)... crazy.gif
Spacecase
kapeller
Hi!!!!
agree, however when you are talking director level "He who knows best" then it is very difficult to argue against.
We now have a ne director who lets us get on with our jobs.
Thanks for your input.
Cheers!!!!!
Lou o! thumbup.gif uarulez2.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.