CAPITALIZE FIRST LETTER OF EACH WORD

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this code that was provided in a module.

Public Function FirstCharUC(sText As String) As String
Dim sTmp() As String
Dim iCtr As Integer

If Len(Trim(sText)) > 0 Then
sTmp = Split(sText)

For iCtr = 0 To UBound(sTmp)
sTmp(iCtr) = UCase(Left(sTmp(iCtr), 1)) & Mid(sTmp(iCtr), 2)
Next iCtr

FirstCharUC = Join(sTmp)
Else
FirstCharUC = sText
End If
End Function

I have this code in the AfterUpdate control in the form on the field.

Me!PrspDemoAddrLine1 = FirstCharUC(Me!PrspDemoAddrLine1)

The codes works great. Exactly what I needed. Here's the problem though. If I delete all the information after the record has been saved I get a VB error code of Run Time Error 94. Invalid use of null.

Is there something that I can add to the AfterUpdate control just before the code to prevent it doing this? Everything else is great though.
 
You can set a flag to run the code if 'True' and not run
the code if 'False'.
i.e.

Sub PrspDemoAddrLine1_AfterUpdate()
Dim blnRunCode as Boolean
blnRunCode=[the condition which you want to test]

If blnRuncode Then
Call FirstCharUC(Me!PrspDemoAddrLine1)
End If

End Sub

You can set your flag (blnRunCode) depending on the
result of a conditional expression.
I hope that's what you were looking for.
-----Original Message-----
I have this code that was provided in a module.

Public Function FirstCharUC(sText As String) As String
Dim sTmp() As String
Dim iCtr As Integer

If Len(Trim(sText)) > 0 Then
sTmp = Split(sText)

For iCtr = 0 To UBound(sTmp)
sTmp(iCtr) = UCase(Left(sTmp(iCtr), 1)) & Mid (sTmp(iCtr), 2)
Next iCtr

FirstCharUC = Join(sTmp)
Else
FirstCharUC = sText
End If
End Function

I have this code in the AfterUpdate control in the form on the field.

Me!PrspDemoAddrLine1 = FirstCharUC(Me!PrspDemoAddrLine1)

The codes works great. Exactly what I needed. Here's
the problem though. If I delete all the information
after the record has been saved I get a VB error code of
Run Time Error 94. Invalid use of null.
Is there something that I can add to the AfterUpdate
control just before the code to prevent it doing this?
Everything else is great though.
 
I've tried that and apparently I don't know what I'm doing. Not sure what the expression should be.

Basically what I'm looking for is if the data in the field gets deleted that the code doesn't run.

Thanks

SFAxess said:
You can set a flag to run the code if 'True' and not run
the code if 'False'.
i.e.

Sub PrspDemoAddrLine1_AfterUpdate()
Dim blnRunCode as Boolean
blnRunCode=[the condition which you want to test]

If blnRuncode Then
Call FirstCharUC(Me!PrspDemoAddrLine1)
End If

End Sub

You can set your flag (blnRunCode) depending on the
result of a conditional expression.
I hope that's what you were looking for.
-----Original Message-----
I have this code that was provided in a module.

Public Function FirstCharUC(sText As String) As String
Dim sTmp() As String
Dim iCtr As Integer

If Len(Trim(sText)) > 0 Then
sTmp = Split(sText)

For iCtr = 0 To UBound(sTmp)
sTmp(iCtr) = UCase(Left(sTmp(iCtr), 1)) & Mid (sTmp(iCtr), 2)
Next iCtr

FirstCharUC = Join(sTmp)
Else
FirstCharUC = sText
End If
End Function

I have this code in the AfterUpdate control in the form on the field.

Me!PrspDemoAddrLine1 = FirstCharUC(Me!PrspDemoAddrLine1)

The codes works great. Exactly what I needed. Here's
the problem though. If I delete all the information
after the record has been saved I get a VB error code of
Run Time Error 94. Invalid use of null.
Is there something that I can add to the AfterUpdate
control just before the code to prevent it doing this?
Everything else is great though.
 
I have this code that was provided in a module.

Public Function FirstCharUC(sText As String) As String

There is a builtin function that makes this a LOT simpler:

StrConv([fieldname], 3)

can be used in an Update query or as the control source of a textbox
to convert CAPITALIZE FIRST LETTER OF EACH WORD to Capitalize First
Letter Of Each Word.
 
Thanks for responding. I've tried you recommendation and the line turns all red.

The code I have works great. I just need it to not run if the data in the field is deleted. This was provided by someone in the discussion group but I forgot who. Like I said it works just fine but if you delete the text in the field you then get a run time error.

John Vinson said:
I have this code that was provided in a module.

Public Function FirstCharUC(sText As String) As String

There is a builtin function that makes this a LOT simpler:

StrConv([fieldname], 3)

can be used in an Update query or as the control source of a textbox
to convert CAPITALIZE FIRST LETTER OF EACH WORD to Capitalize First
Letter Of Each Word.
 
The code I have works great. I just need it to not run if the data in the field is deleted. This was provided by someone in the discussion group but I forgot who. Like I said it works just fine but if you delete the text in the field you then get a run time error.

Try changing this code just a bit to:

Public Function FirstCharUC(vText As Variant) As String
Dim sText As String
Dim sTmp() As String
Dim iCtr As Integer
If IsNull(vText) Then
FirstChar = ""
Exit Sub
End If
sText = vText
If Len(Trim(sText)) > 0 Then
<etc>
 
If not IsNull(Me!PrspDemoAddrLine1) Then
Me!PrspDemoAddrLine1 = FirstCharUC(Me!PrspDemoAddrLine1)
End if


--
Terry Kreft
MVP Microsoft Access


Vic said:
I have this code that was provided in a module.

Public Function FirstCharUC(sText As String) As String
Dim sTmp() As String
Dim iCtr As Integer

If Len(Trim(sText)) > 0 Then
sTmp = Split(sText)

For iCtr = 0 To UBound(sTmp)
sTmp(iCtr) = UCase(Left(sTmp(iCtr), 1)) & Mid(sTmp(iCtr), 2)
Next iCtr

FirstCharUC = Join(sTmp)
Else
FirstCharUC = sText
End If
End Function

I have this code in the AfterUpdate control in the form on the field.

Me!PrspDemoAddrLine1 = FirstCharUC(Me!PrspDemoAddrLine1)

The codes works great. Exactly what I needed. Here's the problem though.
If I delete all the information after the record has been saved I get a VB
error code of Run Time Error 94. Invalid use of null.
Is there something that I can add to the AfterUpdate control just before
the code to prevent it doing this? Everything else is great though.
 
Back
Top