AfterUpdate event won't call module

[JustATech]

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[JustATech]

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[Klatuu]

Your problem may be that the module and the function have the same name. A
module cannot have any procedure in it with the same name as the module name.

Note that you never call a module. A module is only a container for subs and
functions (procedures).

Also, for reusable code (good for you) it should be in a standard module
and it should be styled as:

Public Function(Furball As String) As Boolean

A Standard module is just a code container, but there are 3 other kinds of
modules.
Form module -- The code attached to a form object that contains the events
for the form and it's controls and any local user defined procedures.

Report module -- The code attached to a report object. It is like a form
module,
but belongs to a report

Class module -- A special kind of code module that behaves like an object.
Get the others down before you go here.
--
Dave Hargis, Microsoft Access MVP

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[JustATech]

Hi Dave,

Thanks for the quick reply.

Your thoughts were right on the nose. I took your advice and renamed my
standalone module to something different than the function and the compile
error magically went away. Thank you VERY much!! (I can't believe I was
so blind *blush*).

Now begins the ordeal for trying to figure out why the data isn't being
processed by the new module, but that's a different matter than this one.

Your problem may be that the module and the function have the same name. A
module cannot have any procedure in it with the same name as the module name.

Note that you never call a module. A module is only a container for subs and
functions (procedures).

Also, for reusable code (good for you) it should be in a standard module
and it should be styled as:

Public Function(Furball As String) As Boolean

A Standard module is just a code container, but there are 3 other kinds of
modules.
Form module -- The code attached to a form object that contains the events
for the form and it's controls and any local user defined procedures.

Report module -- The code attached to a report object. It is like a form
module,
but belongs to a report

Class module -- A special kind of code module that behaves like an object.
Get the others down before you go here.
--
Dave Hargis, Microsoft Access MVP

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[Klatuu]

Post the code where you are calling the function (NOT A MODULE!) and the
code for the function, please.
--
Dave Hargis, Microsoft Access MVP

Hi Dave,

Thanks for the quick reply.

Your thoughts were right on the nose. I took your advice and renamed my
standalone module to something different than the function and the compile
error magically went away. Thank you VERY much!! (I can't believe I was
so blind *blush*).

Now begins the ordeal for trying to figure out why the data isn't being
processed by the new module, but that's a different matter than this one.

Your problem may be that the module and the function have the same name. A
module cannot have any procedure in it with the same name as the module name.

Note that you never call a module. A module is only a container for subs and
functions (procedures).

Also, for reusable code (good for you) it should be in a standard module
and it should be styled as:

Public Function(Furball As String) As Boolean

A Standard module is just a code container, but there are 3 other kinds of
modules.
Form module -- The code attached to a form object that contains the events
for the form and it's controls and any local user defined procedures.

Report module -- The code attached to a report object. It is like a form
module,
but belongs to a report

Class module -- A special kind of code module that behaves like an object.
Get the others down before you go here.
--
Dave Hargis, Microsoft Access MVP

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).


:

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[JustATech]

Hello again Dave.

While I was prepared to post this new issue in a different thread this
morning, I wanted to check this thread first (to see if I missed something)
and here we are again.

After quite a few unsuccessful attempts yesterday, I finally found out this
morning that the function (in the standalone module) was definitely being
called and the string was being updated properly by the functions coding.
Somehow the variable that was passed to the function reverted to its original
state (like it was before it was sent out for updating) when the AfterUpdate
event got the chance to complete itself.

Needless to say, I was overwhelmed with relief once I realized that in my
stupidity, I forgot to update the passed variable with the string that was
converted in the function. Everything's working now just as it should.

To help someone else avoid my mistakes - Here is the corrected code from the
control to call the function:

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
strTempName = Trim(Me.[SiteName])
CapitalizeText (strTempName) 'Convert the string - pass this
variable to the function.
Me.[SiteName] = strTempName 'Store the newly converted variable
into the table.
End If

End Sub

And here is the corrected code from the function in the module (it's
actually the entire module itself):

Option Compare Database
Public strTempName As String

Public Function CapitalizeText(strName As String) As String
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strName = UCase(Left(strName, 1)) & Mid(strName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strName = Left(strName, lngSpacePos) & _
UCase(Left(Right(strName, Len(strName) -
lngSpacePos), 1)) & _
Mid(strName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop
strTempName = strName 'Update the passed variable with the converted
string.

End Function

Thank you very much again Dave for helping me get my initial problem cleared
up and for your expertise.

Post the code where you are calling the function (NOT A MODULE!) and the
code for the function, please.
--
Dave Hargis, Microsoft Access MVP

Hi Dave,

Thanks for the quick reply.

Your thoughts were right on the nose. I took your advice and renamed my
standalone module to something different than the function and the compile
error magically went away. Thank you VERY much!! (I can't believe I was
so blind *blush*).

Now begins the ordeal for trying to figure out why the data isn't being
processed by the new module, but that's a different matter than this one.

Your problem may be that the module and the function have the same name. A
module cannot have any procedure in it with the same name as the module name.

Note that you never call a module. A module is only a container for subs and
functions (procedures).

Also, for reusable code (good for you) it should be in a standard module
and it should be styled as:

Public Function(Furball As String) As Boolean

A Standard module is just a code container, but there are 3 other kinds of
modules.
Form module -- The code attached to a form object that contains the events
for the form and it's controls and any local user defined procedures.

Report module -- The code attached to a report object. It is like a form
module,
but belongs to a report

Class module -- A special kind of code module that behaves like an object.
Get the others down before you go here.
--
Dave Hargis, Microsoft Access MVP


:

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).


:

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[JustATech]

Hi Dave,

Yes, it's me again after a recent encounter with another issue.

You probably have already spotted the error below in my function call, but I
failed to see it until I updated the main form control's AfterUpdate event
with the same subform control code listed below (well, almost the same code -
just the control & field names are different on my main form).

The control on my main form wasn't running the function because I failed to
put the word "Call" in front of the CapitalizeText function call. After
updating this one line in both the main form and subform controls, I'm now
cooking with butter as everything appears to be working beautifully.

So everyone can benefit from my mishap (again), I'm listing the newly
revised AfterUpdate event code so maybe I can help spare someone else the
same headache I went through:

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string - pass this
variable to the function.
Me.[SiteName] = strTempName 'Store the newly converted variable
into the table.
End If

End Sub

Everything else is still exactly the same as below.

Thanks again for your time and input. I really do appreciate it.

Hello again Dave.

While I was prepared to post this new issue in a different thread this
morning, I wanted to check this thread first (to see if I missed something)
and here we are again.

After quite a few unsuccessful attempts yesterday, I finally found out this
morning that the function (in the standalone module) was definitely being
called and the string was being updated properly by the functions coding.
Somehow the variable that was passed to the function reverted to its original
state (like it was before it was sent out for updating) when the AfterUpdate
event got the chance to complete itself.

Needless to say, I was overwhelmed with relief once I realized that in my
stupidity, I forgot to update the passed variable with the string that was
converted in the function. Everything's working now just as it should.

To help someone else avoid my mistakes - Here is the corrected code from the
control to call the function:

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
strTempName = Trim(Me.[SiteName])
CapitalizeText (strTempName) 'Convert the string - pass this
variable to the function.
Me.[SiteName] = strTempName 'Store the newly converted variable
into the table.
End If

End Sub

And here is the corrected code from the function in the module (it's
actually the entire module itself):

Option Compare Database
Public strTempName As String

Public Function CapitalizeText(strName As String) As String
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strName = UCase(Left(strName, 1)) & Mid(strName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strName = Left(strName, lngSpacePos) & _
UCase(Left(Right(strName, Len(strName) -
lngSpacePos), 1)) & _
Mid(strName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop
strTempName = strName 'Update the passed variable with the converted
string.

End Function

Thank you very much again Dave for helping me get my initial problem cleared
up and for your expertise.

Post the code where you are calling the function (NOT A MODULE!) and the
code for the function, please.
--
Dave Hargis, Microsoft Access MVP

Hi Dave,

Thanks for the quick reply.

Your thoughts were right on the nose. I took your advice and renamed my
standalone module to something different than the function and the compile
error magically went away. Thank you VERY much!! (I can't believe I was
so blind *blush*).

Now begins the ordeal for trying to figure out why the data isn't being
processed by the new module, but that's a different matter than this one.

:

Your problem may be that the module and the function have the same name. A
module cannot have any procedure in it with the same name as the module name.

Note that you never call a module. A module is only a container for subs and
functions (procedures).

Also, for reusable code (good for you) it should be in a standard module
and it should be styled as:

Public Function(Furball As String) As Boolean

A Standard module is just a code container, but there are 3 other kinds of
modules.
Form module -- The code attached to a form object that contains the events
for the form and it's controls and any local user defined procedures.

Report module -- The code attached to a report object. It is like a form
module,
but belongs to a report

Class module -- A special kind of code module that behaves like an object.
Get the others down before you go here.
--
Dave Hargis, Microsoft Access MVP


:

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).


:

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?

 

[JustATech]

"JustATech" is an idiot as he forgot to take out the CapitalizeText function
in the main *FORM* module. The word "Call" doesn't need to be in front of
the CapitalizeText function call after all.

Sorry for causing people a headache. I'll go away now.

Hi Dave,

Yes, it's me again after a recent encounter with another issue.

You probably have already spotted the error below in my function call, but I
failed to see it until I updated the main form control's AfterUpdate event
with the same subform control code listed below (well, almost the same code -
just the control & field names are different on my main form).

The control on my main form wasn't running the function because I failed to
put the word "Call" in front of the CapitalizeText function call. After
updating this one line in both the main form and subform controls, I'm now
cooking with butter as everything appears to be working beautifully.

So everyone can benefit from my mishap (again), I'm listing the newly
revised AfterUpdate event code so maybe I can help spare someone else the
same headache I went through:

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string - pass this
variable to the function.
Me.[SiteName] = strTempName 'Store the newly converted variable
into the table.
End If

End Sub

Everything else is still exactly the same as below.

Thanks again for your time and input. I really do appreciate it.

Hello again Dave.

While I was prepared to post this new issue in a different thread this
morning, I wanted to check this thread first (to see if I missed something)
and here we are again.

After quite a few unsuccessful attempts yesterday, I finally found out this
morning that the function (in the standalone module) was definitely being
called and the string was being updated properly by the functions coding.
Somehow the variable that was passed to the function reverted to its original
state (like it was before it was sent out for updating) when the AfterUpdate
event got the chance to complete itself.

Needless to say, I was overwhelmed with relief once I realized that in my
stupidity, I forgot to update the passed variable with the string that was
converted in the function. Everything's working now just as it should.

To help someone else avoid my mistakes - Here is the corrected code from the
control to call the function:

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
strTempName = Trim(Me.[SiteName])
CapitalizeText (strTempName) 'Convert the string - pass this
variable to the function.
Me.[SiteName] = strTempName 'Store the newly converted variable
into the table.
End If

End Sub

And here is the corrected code from the function in the module (it's
actually the entire module itself):

Option Compare Database
Public strTempName As String

Public Function CapitalizeText(strName As String) As String
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strName = UCase(Left(strName, 1)) & Mid(strName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strName = Left(strName, lngSpacePos) & _
UCase(Left(Right(strName, Len(strName) -
lngSpacePos), 1)) & _
Mid(strName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop
strTempName = strName 'Update the passed variable with the converted
string.

End Function

Thank you very much again Dave for helping me get my initial problem cleared
up and for your expertise.

Post the code where you are calling the function (NOT A MODULE!) and the
code for the function, please.
--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

Thanks for the quick reply.

Your thoughts were right on the nose. I took your advice and renamed my
standalone module to something different than the function and the compile
error magically went away. Thank you VERY much!! (I can't believe I was
so blind *blush*).

Now begins the ordeal for trying to figure out why the data isn't being
processed by the new module, but that's a different matter than this one.

:

Your problem may be that the module and the function have the same name. A
module cannot have any procedure in it with the same name as the module name.

Note that you never call a module. A module is only a container for subs and
functions (procedures).

Also, for reusable code (good for you) it should be in a standard module
and it should be styled as:

Public Function(Furball As String) As Boolean

A Standard module is just a code container, but there are 3 other kinds of
modules.
Form module -- The code attached to a form object that contains the events
for the form and it's controls and any local user defined procedures.

Report module -- The code attached to a report object. It is like a form
module,
but belongs to a report

Class module -- A special kind of code module that behaves like an object.
Get the others down before you go here.
--
Dave Hargis, Microsoft Access MVP


:

In my stressfulness I forgot to mention that I comment out the form modules
CapitalizeText functions before trying to compile with the standalone
CapitalizeText module code in place. (Sorry 'bout that).


:

Software used: Access 2007/WinXp Pro.

Hello all.

I apologize for such a long post. I'm relatively new to the Access
programming circle and am going nuts trying to figure out what I'm doing
wrong in trying to get the following idea to work.

I currently have a form which contains 1 subform. Both forms each have a
bound control that I am trying to update when the data it contains is input
or altered. The string parameter I pass to the update function I use
(CapitalizeText) is declared by the data in the control(s). This same
function (CapitalizeText) is not only present in both forms, but also
identical in code. Each appropriate control's AfterUpdate event calls their
corresponding CapitalizeText function perfectly and the data is changed
successfully without any problems.

I know it's good practice to just have 1 standalone module that contains the
update code process and then just call the module whenever I need it, but
this is where my problem occurs in trying to implement this change.

My problem is that I receive the following error message when compiling:

Microsoft Visual Basic
Compile Error:
Expected variable or procedure, not module


Here's the original code in a control's AfterUpdate event on the subform
(this is similar for the control on the main form - the exception being the
name of the control and its bound field):

Private Sub cboSiteName_AfterUpdate()

If Not IsNull(Me.cboSiteName) Then
Dim strTempName As String 'NOTE: This should not needed here when
using the standalone module.

strTempName = Trim(Me.[SiteName])
Call CapitalizeText(strTempName) 'Convert the string.
Me.cboSiteName = strTempName 'Store the newly converted string
into the table.
End If

End Sub


The following is the update function that is present in both form modules at
the moment:

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


Here is the standalone module code that I would like to be called instead of
having the above function present in both forms:

Option Compare Database
Dim strTempName As String

Public Function CapitalizeText(strTempName As String)
'Capitalizes each separate word that follows a space in the newly entered
string.

Dim lngSpacePos As Long

'First: Ensure that the very first letter of the string is a capital.
strTempName = UCase(Left(strTempName, 1)) & Mid(strTempName, 2)

'Second: Loop through the rest of the string to capitalize each separate
word.
lngSpacePos = 1 'Initialize the loop counter.
Do
lngSpacePos = InStr(lngSpacePos, strTempName, " ", vbBinaryCompare)
If lngSpacePos > 0 Then
strTempName = Left(strTempName, lngSpacePos) & _
UCase(Left(Right(strTempName, Len(strTempName) -
lngSpacePos), 1)) & _
Mid(strTempName, lngSpacePos + 2)
lngSpacePos = lngSpacePos + 1
Else
Exit Do
End If
Loop

End Function


What am I doing wrong?