?
Solved

Loop through variables in VBA

Posted on 2006-06-05
10
Medium Priority
?
700 Views
Last Modified: 2012-06-27
I have to provide a code for a user's name.  So this is how it's suppose to be done.
 A = 01
 B =02
 C= 03  etc...
 D = 04
 E = 05
So when a user types in Abbott as a last name, the code generated in a form field = 0102

If the last name was Beatle the code would be 0205  

Anyone?
0
Comment
Question by:TimKestermont
  • 6
  • 2
  • 2
10 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16837227
u could try this


Public Function GetCodeForName(ByVal sName As String) As String

    Dim sNewName As String
    Dim i As Integer

    For i = 1 To Len(sName)
        sNewName = sNewName & Format(Asc(Mid$(sName, i, 1)) - 64, "00")
    Next i
   
    GetCodeForName = sNewName
End Function


test function
Public Sub TestIt()
    msbox GetCodeForName("ABC")
End Sub
       
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16837229
You'll need a table
2 columns
Letter   |   Value
a               01
b               02

etc...

You'll need a form. Two unbound text boxes and a button
textbox one where you will enter the name
the button you will press to get the code
textbox two where the code will show up

Code here in a minute..got called away for a sec.
J

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16837238
urm, slight change

if u want ABC to return same results as abc

Public Function GetCodeForName(ByVal sName As String) As String

    Dim sNewName As String
    Dim i As Integer

    For i = 1 To Len(sName)
        sNewName = sNewName & Format(Asc(Mid$(UCase$(sName), i, 1)) - 64, "00")
    Next i
   
    GetCodeForName = sNewName
End Function



0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 34

Expert Comment

by:jefftwilley
ID: 16837247
nevermind...good paste rock!! dont you have any real work to do? hehehe
J
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16837261
looking at your question, is it just the first two letters you want coding?

if so, then a small change

Public Function GetCodeForName(ByVal sName As String) As String

    Dim sNewName As String
    Dim i As Integer
    dim iMaxLen as integer

    iMaxLen = 2
    if len(sName) < iMaxLen then iMaxLen = len(sName)
    For i = 1 To iMaxLen
        sNewName = sNewName & Format(Asc(Mid$(UCase$(sName), i, 1)) - 64, "00")
    Next i
   
    GetCodeForName = sNewName
End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16837264
come on Jeff, I told u to be quick!
0
 

Author Comment

by:TimKestermont
ID: 16837391
rockiroads

If txtlastname is on the form and CodeNumber is on the form, how does that translate into your formula
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 16837507

when do u want to call it, on the afterupdate event of txtlastname?

private sub txtlastname_afterupdate
    CodeNumber.value = GetCodeForName(txtlastname.value)
end sub

0
 

Author Comment

by:TimKestermont
ID: 16838590
Thanks,

Works like a charm.  Quick and easy, just like I like it.  

TK
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16839863
No probs
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question