Update a field in all records based on a field control.

  • Thread starter Thread starter ernpal
  • Start date Start date
E

ernpal

I have a form that, for every record, parses one field and saves the
new value in another field. It removes vowels and drops the last
letter until the new name is X characters long, where X is a value
entered into a field textbox (txtChars). What I am trying to do is
this: when txtChars is changed/updated, all the parsed values update
accordingly.

Thanks,
Ernest
 
Typically, this is poor design. You are saving the same data (in two
formats) in two different fields. Just save the one format that contains
all the data. When you need the truncated data, create it using your
formula in your forms, queries, and reports. No need to save the redundant
data and hope that you can fix the second field if the first field's data
gets changed by someone.

For more information on this theory, read the previous posts that talk about
"storing calculated data in a table".
 
Basically, the form will take a descriptive ActiveDirectory group name,
and parse it to suggest a new name that conforms to a naming convention
(there is a nother field for manually entering an new name). I started
to make it a calculated field in a query, but I couldn't figure out how
to parse to look the way I want.

Right now, I am using an event procedure based on the On Current event
of the form and the On Change and On Update events of the primary name
field. I two controls in the form header--a desired character length
text box and a force-Ucase check box. I want every primary field to be
reparsed everytime there is a change to either of the two controls.

This is the function I am using to parse the name (it may be sloppy
code, as I am relatively new to VBA, but I am open to suggestions...):
===================================================================
Function SuggestNewADName(RoleName As String, Optional
SuggestedNameLength As Integer) As String
Dim strSuggADName As String
Dim newLength As Integer

SuggestedNameLength = IIf(SuggestedNameLength = 0, 6,
SuggestedNameLength)
strSuggADName = RoleName

strSuggADName = Replace(strSuggADName, " ", "")
strSuggADName = Replace(strSuggADName, ".", "")
strSuggADName = Replace(strSuggADName, "-", "")
strSuggADName = Replace(strSuggADName, "/", "")
strSuggADName = Replace(strSuggADName, "(", "")
strSuggADName = Replace(strSuggADName, ")", "")
Do While Len(strSuggADName) > SuggestedNameLength
strSuggADName = Replace(strSuggADName, "a", "", , 1,
vbBinaryCompare)
strSuggADName = Replace(strSuggADName, "e", "", , 1,
vbBinaryCompare)
strSuggADName = Replace(strSuggADName, "i", "", , 1,
vbBinaryCompare)
strSuggADName = Replace(strSuggADName, "o", "", , 1,
vbBinaryCompare)
strSuggADName = Replace(strSuggADName, "u", "", , 1,
vbBinaryCompare)

newLength = Len(strSuggADName)
strSuggADName = IIf(newLength > SuggestedNameLength,
Left(strSuggADName, newLength - 1), strSuggADName)
Loop

strSuggADName = UCase(strSuggADName)
SuggestNewADName = strSuggADName
End Function
===================================================================

Thanks, again,
Ernest
 
Back
Top