On Tue, 8 Jun 2004 20:30:01 -0700, Sue wrote:
Sue,
This is the third post you've sent regarding this. As this is the
latest one, I'll assume this supersedes all the others.
My comments are interspersed below.
Hi Fred
I have the following fields setup
LastName (PrspDemoLastName)
FirstName (PrspDemoFirstName)
MiddleName (PrspDemoMiddleName)
NickName (PrspDemoNickName)
AddressLine1 (PrspDemoAddrLine1)
AddressLine2 (PrspDemoAddrLine2)
City (PrspDemoCity)
State (PrspDemoState)
ZipCode (PrspDemoZipCode)
Now you got it right.
I plan on using the code you provded me in the AddressLine1 and AddressLine2. I've implemented the code but when I open the form I receive an outside invalidation procedure error.
I'm attaching different code here which will compare the entire field
for capitalization, not just each word in the field.
In the [PrspDemoLastName] After Update event:
If Not IsNull([PrspDemoLastName]) Then
[PrspDemoLastName] = ConvExceptions([LastName])
End If
=======
Place the following new function in a module.
Remember to save the module under a different name,
**You can delete the previous one that you're having some problems
with if you want**
If you have a problem with error's when you write this code, please
COPY and PASTE the code from the code window directly onto a reply
mail so I can see how it actually appears, not how it appears when you
re-type it.
Regarding the error with the
vbYesNo, "Exception found! ") ------------------- I removed " from
the end here. It kept highlighted in red. Was that correct to do?
When using quotation marks they are always in pairs. If VBA won't
accept one at the end of a line, look to see where it's missing
partner should be. That particular line has 3 sets of quotes (6
total). It may be because the line has been incorrectly split when it
was copied and all that needs doing is making it into one line instead
of two.
I have indicate line breaks in the code with a '***
so the code line BELOW the '*** is all on one line, no matter how many
lines your email reader shows it on.
After you copy the code remove each of the ' ***'s .
I've added an Option Explicit statement to the declarations section
which should help you find compile errors before you run the code.
Option Compare Database
Option Explicit
===
Function ConvExceptions(StrIn As String) As String
' Will find exceptions to Proper Case capitalization of names.
' Checks for the complete name in a field.
' van der Hoff, van Beethoven, McDaniel
' ***
If DCount("*", "tblExceptions", "[ExceptName] = " & Chr(34) & StrIn &
Chr(34) & "") > 0 Then
' ***
Dim intResponse As Integer
' ***
Dim strFind As String
' ***
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] =
" & Chr(34) & StrIn & Chr(34) & "")
' ***
intResponse = MsgBox(strFind & vbCrLf & " is an exception name." &
vbCrLf & "Accept the above capitalization? Y/N ?", vbYesNo,
"Exception found!")
' ***
If intResponse = vbYes Then
' ***
ConvExceptions = strFind
' ***
Exit Function
' ***
End If
' ***
End If
' ***
ConvExceptions = StrConv(StrIn, 3)
' ***
End Function
=================
You can use the same tblExceptions from the previous messages, but
you must store the exact name in each record, i.e. van Beethoven,
McDonald, O'Brien, van den Steen, etc.
If you wish to use this for First Name entry and City entry also, use
the same code as the Last Name AfterUpdate event, but place it in that
Control's (FirstName or City) AfterUpdate event, changing the field
name in the code, of course.
You can use names, cities, states, nickname in the table if you want,
but just one per record.
It will NOT find words in the Street Address field because address
data such as PO Box 123 is not the same as PO Box 456.
For the Address field, if you have Access 2000 or newer, you can check
for PO Box capitalization in the address field directly without using
the table of exceptions.
In the Address AfterUpdate event:
[Address] = StrConv([Address],vbProperCase)
If InStr([Address],"Po Box ") > 0 Then
[Address] = Replace([Address],"Po Box ","PO Box ")
End If
I hope we haven't covered too much ground.
Good luck.
Fred
=======================
Scratch this previous code beneath this line...
Option Compare Database
' Will find exceptions to Proper Case capitalization of names.
' In a multi-word string
On Error Resume Next -----------------Errors out on here with invalid outside procedure when you open up the form. If I comment this out I receive the same error on StrIn
Dim strWord As String
Dim intX As Integer
Dim intY As Integer
Dim strNewString As String
Dim intResponse As Integer
Dim strFind As String
StrIn = StrIn & " " ------------------- Next invalid procedure appears here if On Error Resume Next is commented out.
intX = InStr(1, StrIn, " ")
strWord = StrConv(Mid(StrIn, intY + 1, intX - 1), vbProperCase)
Do While intX <> 0
If DCount("*", "tblExceptions", "[ExceptName]= " & Chr(34) & strWord & Chr(34) & "") > 0 Then
strFind = DLookup("[ExceptName]", "tblExceptions", "[ExceptName] = " & Chr(34) & strWord & Chr(34) & "")
intResponse = MsgBox(strFind & vbCrLf & " is an exception name." & vbCrLf & " Accept the above capitalization? Y/N ?", vbYesNo, "Exception found! ") ------------------- I removed " from the end here. It kept highlighted in red. Was that correct to do?
If intResponse = vbYes Then
strWord = strFind
End If
End If
strNewString = strNewString & strWord & " "
intY = intX + 1
intX = InStr(intY, StrIn, " ")
strWord = StrConv(Mid(StrIn, intY, intX - intY), vbProperCase)
Loop
ConvExceptionsInField = strNewString
End Function
Other then the above I have been able to test the rest. I look forward to receiving the code from you for the name fields.
Thanks in advance.
fredg said:
I'll try your suggestions soon. Time seems to be escaping me lately.
I do have the city, state and zip code separated.
I'm planning to use this for the last name, first name, middle name, nick name, address line 1, address line 2 and city field.
Should I not be doing it this way?
No you should not be doing it this way.
You should have a LastName field, a FirstName field, an Initial field,
a NickName field, an Address1 field, an Address2 field.
You already do have separate City, State and Zip fields, so that's
good.
Also important is a RecordID field, indexed with no duplicates.
It's easy to assemble a mailing label or a list from the above, and
sort it if wanted by City, or Zip code, or LastName, etc. It's very
difficult to do all of that if the Prospects name field is something
like:
John M. Smith Jack 123 So. Main Street, PO Box 258 Cincinnati
Some records will not have a middle initial, some will not have a
nickname, some will not have two addresses.
Is the above's last name Jack or is that his nickname?
You have confusion and lots of work to make sense out of all of this.
Access is designed for atomic data in a field (the smallest that is
useful).
How would you sort the above by City?, Or by Last Name? Or find John
Smith's records?
To assemble a mailing list for the properly separated fields, you
would use something like this in an address label.
Using 3 unbound controls:
=[FirstName] & " " + ([Initial] & " ") & [LastName]
Use of the + above will remove the empty initial spaces if there is no
initial.
= IIf IsNull([Address1]),[Address2],[Address1])
If Address1 is blank, Address2 will print
= [City] & ", " & [State] & " " & [ZipCode]
I would strongly suggest you search these news groups for posts on
parsing fields, splitting fields, separating last names from first
names in a field, etc. There are lots of questions by posters who
finally realize they have improperly designed databases. For some of
them it's a struggle to make it right afterwards.
Good luck.
