Simple question regarding coding

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

Guest

Hi, This is the first time I am working on something with VBA. I have
inserted 1 Module without any problems but this one, I am getting anything.
The code will change the first letter to caps. I inserted the following
code into a Modeule and named it, fFirstUpper.
When I go to the Control Source of a textbox in a form or report, I am
getting an error. #Name? in a form and a Report a Parameter and then #Error.
=fFirstupper([Lastname]) I tried this in a text called lastname and a text1.

Thanks

Function fFirstUpper(ByVal vstrIn As String) As String

Const cSpace As String = " "

Dim strOut As String
Dim strCur As String
Dim lngPos As Long
Dim blnNewWord As Boolean

blnNewWord = True
strOut = ""
For lngPos = 1 To Len(vstrIn)

strCur = Mid(vstrIn, lngPos, 1)

If strCur = cSpace Then
strOut = strOut & strCur
blnNewWord = True
Else
If blnNewWord Then
strOut = strOut & UCase(strCur)
blnNewWord = False
Else
strOut = strOut & LCase(strCur)
'strOut = strOut & strCur
'if you want to keep case of characters within a word
End If
End If

Next lngPos
fFirstUpper = strOut

End Function
 
One possible reason is that the textbox initially is Null but your function
is expecting a string. Try changing the data type of vstrIn to Variant and
testing it for Null before your existing code.

Hope This Helps
Gerald Stanley MCSD
 
Thanks for the quick reply Gerald but it still gives me the same result

Gerald Stanley said:
One possible reason is that the textbox initially is Null but your function
is expecting a string. Try changing the data type of vstrIn to Variant and
testing it for Null before your existing code.

Hope This Helps
Gerald Stanley MCSD

sneakerhub said:
Hi, This is the first time I am working on something with VBA. I have
inserted 1 Module without any problems but this one, I am getting anything.
The code will change the first letter to caps. I inserted the following
code into a Modeule and named it, fFirstUpper.
When I go to the Control Source of a textbox in a form or report, I am
getting an error. #Name? in a form and a Report a Parameter and then #Error.
=fFirstupper([Lastname]) I tried this in a text called lastname and a text1.

Thanks

Function fFirstUpper(ByVal vstrIn As String) As String

Const cSpace As String = " "

Dim strOut As String
Dim strCur As String
Dim lngPos As Long
Dim blnNewWord As Boolean

blnNewWord = True
strOut = ""
For lngPos = 1 To Len(vstrIn)

strCur = Mid(vstrIn, lngPos, 1)

If strCur = cSpace Then
strOut = strOut & strCur
blnNewWord = True
Else
If blnNewWord Then
strOut = strOut & UCase(strCur)
blnNewWord = False
Else
strOut = strOut & LCase(strCur)
'strOut = strOut & strCur
'if you want to keep case of characters within a word
End If
End If

Next lngPos
fFirstUpper = strOut

End Function
Click to expand...
Click to expand...
 
Here are the details of my test in case they can help.

I set up a form with two text boxes text0 and text2. I set the
ControlSource of text2 to = fFirstUpper(text0). When I open the form with
the original function code, text0 was blank and text2 showed #Error.

I then made the following changes to the code of the function

Function fFirstUpper(ByVal vstrIn As Variant) As String

and

after
Dim blnNewWord As Boolean

I placed
If IsNull(vstrIn) Then
fFirstUpper = ""
Exit Function
End If

With these changes in place, I open the form and both textboxes are blank.
I type in abc into text0 and tab to text2, which then shows Abc.

Hope This Helps
Gerald Stanley MCSD

sneakerhub said:
Thanks for the quick reply Gerald but it still gives me the same result

Gerald Stanley said:
One possible reason is that the textbox initially is Null but your function
is expecting a string. Try changing the data type of vstrIn to Variant and
testing it for Null before your existing code.

Hope This Helps
Gerald Stanley MCSD

sneakerhub said:
Hi, This is the first time I am working on something with VBA. I have
inserted 1 Module without any problems but this one, I am getting anything.
The code will change the first letter to caps. I inserted the following
code into a Modeule and named it, fFirstUpper.
When I go to the Control Source of a textbox in a form or report, I am
getting an error. #Name? in a form and a Report a Parameter and then #Error.
=fFirstupper([Lastname]) I tried this in a text called lastname and a text1.

Thanks

Function fFirstUpper(ByVal vstrIn As String) As String

Const cSpace As String = " "

Dim strOut As String
Dim strCur As String
Dim lngPos As Long
Dim blnNewWord As Boolean

blnNewWord = True
strOut = ""
For lngPos = 1 To Len(vstrIn)

strCur = Mid(vstrIn, lngPos, 1)

If strCur = cSpace Then
strOut = strOut & strCur
blnNewWord = True
Else
If blnNewWord Then
strOut = strOut & UCase(strCur)
blnNewWord = False
Else
strOut = strOut & LCase(strCur)
'strOut = strOut & strCur
'if you want to keep case of characters within a word
End If
End If

Next lngPos
fFirstUpper = strOut

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top