Prevented from saving data in the field

[Chaim]

I have a simple form consisting of a text box in which the user enters a name
in the form 'LastName, FirstName', and a combo box which allows the user to
assign a role (one role per person, roles are static) to the name entered.
The RecordSource for the form is a table consisting of: user ID (PK,
autonumber), Full Name (text, 50), a role ID (long, the bound column of the
combo box), and an email (text, currently ignored, not required, may be zero
length). There are no validation rules or input formats set on any of the
fields in the table.

I want the txtFullName to be able to accept the user's input as all lower,
upper or mixed case and either convert on the fly or be 'fixed up' so that it
will look like a name: >L<?????????, >L<????????? (the usual format mask).
However, I don't want to tell the user 'Sorry that name is too long'. So if
they enter a 15 character last name or a 12 character first name, the format
still has to work.

1. The simplest way to do this would be with an InputMask. Is there a way to
specify an arbitrary length input mask to satisfy the intent mentioned above?
I.e., is there something equivalent to a [Perl] RE like '.*' (a multiplier)
so that the Input Mask would read something like: '>L<?*, >L<?*'? (I'm
looking for the 95%+ solution right now.)

2. I have tried a BeforeUpdate event procedure that looks as follows:

Private Sub txtFullName_BeforeUpdate (Cancel as Integer)
dim val as String
val = nameUpper (txtFullName)
txtFullName = val
Cancel = False
End Sub

Private Function nameUpper (val as String) as String
dim commaPos as Long
val = UCase (Left (val, 1)) & Mid (val, 2)
commaPos = InStr (2, val, ", ")
if commaPos > 0 then
val = Left (val, commaPos + 1) & _ ' Preserve last name
part
UCase (Mid (val, commaPos + 2, 1) & _ ' Cap the
first name
Mid (val, commaPos + 3) ' Preserve rest of
first name
end if
nameUpper = val
End Function

This function works correctly and can be tailored to suit. But the event
procedure upon return from the function displays the following run time error
(at the line which reads
'txtFullName = val'):

Run Time Error '-2147352567 (800200009)

The macro or function set to the BeforeUpdate or ValidationRule property for
the field is preventing the <name of the database> from saving the data in
the field. There are, as I said above, no validation rules applied to any
field in this table.

What's happening here?

3. I've also tried some other event procedures and ended up getting into
infinite loops (LostFocus, Exit). So either I did something wrong (highly
probable) when I wrote those (similar to the BeforeUpdate procedure above;
they called the nameUpper() function to do the real work) or I am missing
some condition which would break out of the loop. Something like Dirty?

4. Another option is a KeyPress event procedure, but I don't know how to
tell the procedure to convert to upper case only on the first character
entered, and the first character following a blank. Nothing seems to be 'in
the text control' yet, so I can't test length or do InStr() calls to look for
the blanks. When I've tried this, I get 'Invalid Use of Null' errors because
the text control's value is null.

If #1 is the best answer, I'll just go with that. But I'm more interested in
knowing what the problem is with the other options.

TIA. Apologies for being so verbose.

 

[Wayne Morgan]

Check the vbProperCase option of the StrConv function, I believe it will do
what you're after. You can use this function in the textbox's AfterUpdate
event (not BeforeUpdate) to change the case. Also, I would recommend
separate first and last name fields. You can concatenate them together when
you need them, but splitting them apart later can be a real pain.

--
Wayne Morgan
MS Access MVP

I have a simple form consisting of a text box in which the user enters a
name
in the form 'LastName, FirstName', and a combo box which allows the user
to
assign a role (one role per person, roles are static) to the name entered.
The RecordSource for the form is a table consisting of: user ID (PK,
autonumber), Full Name (text, 50), a role ID (long, the bound column of
the
combo box), and an email (text, currently ignored, not required, may be
zero
length). There are no validation rules or input formats set on any of the
fields in the table.

I want the txtFullName to be able to accept the user's input as all lower,
upper or mixed case and either convert on the fly or be 'fixed up' so that
it
will look like a name: >L<?????????, >L<????????? (the usual format mask).
However, I don't want to tell the user 'Sorry that name is too long'. So
if
they enter a 15 character last name or a 12 character first name, the
format
still has to work.

1. The simplest way to do this would be with an InputMask. Is there a way
to
specify an arbitrary length input mask to satisfy the intent mentioned
above?
I.e., is there something equivalent to a [Perl] RE like '.*' (a
multiplier)
so that the Input Mask would read something like: '>L<?*, >L<?*'? (I'm
looking for the 95%+ solution right now.)

2. I have tried a BeforeUpdate event procedure that looks as follows:

Private Sub txtFullName_BeforeUpdate (Cancel as Integer)
dim val as String
val = nameUpper (txtFullName)
txtFullName = val
Cancel = False
End Sub

Private Function nameUpper (val as String) as String
dim commaPos as Long
val = UCase (Left (val, 1)) & Mid (val, 2)
commaPos = InStr (2, val, ", ")
if commaPos > 0 then
val = Left (val, commaPos + 1) & _ ' Preserve last name
part
UCase (Mid (val, commaPos + 2, 1) & _ ' Cap the
first name
Mid (val, commaPos + 3) ' Preserve rest of
first name
end if
nameUpper = val
End Function

This function works correctly and can be tailored to suit. But the event
procedure upon return from the function displays the following run time
error
(at the line which reads
'txtFullName = val'):

Run Time Error '-2147352567 (800200009)

The macro or function set to the BeforeUpdate or ValidationRule property
for
the field is preventing the <name of the database> from saving the data in
the field. There are, as I said above, no validation rules applied to any
field in this table.

What's happening here?

3. I've also tried some other event procedures and ended up getting into
infinite loops (LostFocus, Exit). So either I did something wrong (highly
probable) when I wrote those (similar to the BeforeUpdate procedure above;
they called the nameUpper() function to do the real work) or I am missing
some condition which would break out of the loop. Something like Dirty?

4. Another option is a KeyPress event procedure, but I don't know how to
tell the procedure to convert to upper case only on the first character
entered, and the first character following a blank. Nothing seems to be
'in
the text control' yet, so I can't test length or do InStr() calls to look
for
the blanks. When I've tried this, I get 'Invalid Use of Null' errors
because
the text control's value is null.

If #1 is the best answer, I'll just go with that. But I'm more interested
in
knowing what the problem is with the other options.

TIA. Apologies for being so verbose.

 

[Chaim]

Wayne,

Thanks. The vbProperCase works perfectly. I don't use separate first and
last name fields in the table for a reason that totally escapes me now (and
the documentation that would let me refresh my memory is not available from
this location- don't ask;-(). I started things off that way but I can't
remember now why I changed them.

Why the AfterUpdate procedure? Doesn't that fire after the table has been
updated? This is something I haven't been able to get clear from the online
Help. If so, how does the data that's already been saved get changed? And on
the flip side, when does the BeforeUpdate fire? Not before the data in the
control has been comitted to the table? The Help docs aren't very specific-
they play pretty lose about what exactly is being updated and when.

Thanks again.

Check the vbProperCase option of the StrConv function, I believe it will do
what you're after. You can use this function in the textbox's AfterUpdate
event (not BeforeUpdate) to change the case. Also, I would recommend
separate first and last name fields. You can concatenate them together when
you need them, but splitting them apart later can be a real pain.

--
Wayne Morgan
MS Access MVP

I have a simple form consisting of a text box in which the user enters a
name
in the form 'LastName, FirstName', and a combo box which allows the user
to
assign a role (one role per person, roles are static) to the name entered.
The RecordSource for the form is a table consisting of: user ID (PK,
autonumber), Full Name (text, 50), a role ID (long, the bound column of
the
combo box), and an email (text, currently ignored, not required, may be
zero
length). There are no validation rules or input formats set on any of the
fields in the table.

I want the txtFullName to be able to accept the user's input as all lower,
upper or mixed case and either convert on the fly or be 'fixed up' so that
it
will look like a name: >L<?????????, >L<????????? (the usual format mask).
However, I don't want to tell the user 'Sorry that name is too long'. So
if
they enter a 15 character last name or a 12 character first name, the
format
still has to work.

1. The simplest way to do this would be with an InputMask. Is there a way
to
specify an arbitrary length input mask to satisfy the intent mentioned
above?
I.e., is there something equivalent to a [Perl] RE like '.*' (a
multiplier)
so that the Input Mask would read something like: '>L<?*, >L<?*'? (I'm
looking for the 95%+ solution right now.)

2. I have tried a BeforeUpdate event procedure that looks as follows:

Private Sub txtFullName_BeforeUpdate (Cancel as Integer)
dim val as String
val = nameUpper (txtFullName)
txtFullName = val
Cancel = False
End Sub

Private Function nameUpper (val as String) as String
dim commaPos as Long
val = UCase (Left (val, 1)) & Mid (val, 2)
commaPos = InStr (2, val, ", ")
if commaPos > 0 then
val = Left (val, commaPos + 1) & _ ' Preserve last name
part
UCase (Mid (val, commaPos + 2, 1) & _ ' Cap the
first name
Mid (val, commaPos + 3) ' Preserve rest of
first name
end if
nameUpper = val
End Function

This function works correctly and can be tailored to suit. But the event
procedure upon return from the function displays the following run time
error
(at the line which reads
'txtFullName = val'):

Run Time Error '-2147352567 (800200009)

The macro or function set to the BeforeUpdate or ValidationRule property
for
the field is preventing the <name of the database> from saving the data in
the field. There are, as I said above, no validation rules applied to any
field in this table.

What's happening here?

3. I've also tried some other event procedures and ended up getting into
infinite loops (LostFocus, Exit). So either I did something wrong (highly
probable) when I wrote those (similar to the BeforeUpdate procedure above;
they called the nameUpper() function to do the real work) or I am missing
some condition which would break out of the loop. Something like Dirty?

4. Another option is a KeyPress event procedure, but I don't know how to
tell the procedure to convert to upper case only on the first character
entered, and the first character following a blank. Nothing seems to be
'in
the text control' yet, so I can't test length or do InStr() calls to look
for
the blanks. When I've tried this, I get 'Invalid Use of Null' errors
because
the text control's value is null.

If #1 is the best answer, I'll just go with that. But I'm more interested
in
knowing what the problem is with the other options.

TIA. Apologies for being so verbose.

 

[Wayne Morgan]

The form's AfterUpdate fires after the table has been updated, but the
control's AfterUpdate fires after the control has been updated. The record
has not yet been saved to the table. Changing the value of the control via
VBA doesn't cause the BeforeUpdate to fire, so you won't get a loop, but it
does interfere with the update because you are changing the value while the
update is being processed (the AfterUpdate hasn't fired yet). The
AfterUpdate event of the control will fire before the form's BeforeUpdate
event, so the changes will be saved.

--
Wayne Morgan
MS Access MVP

Wayne,

Thanks. The vbProperCase works perfectly. I don't use separate first and
last name fields in the table for a reason that totally escapes me now
(and
the documentation that would let me refresh my memory is not available
from
this location- don't ask;-(). I started things off that way but I can't
remember now why I changed them.

Why the AfterUpdate procedure? Doesn't that fire after the table has been
updated? This is something I haven't been able to get clear from the
online
Help. If so, how does the data that's already been saved get changed? And
on
the flip side, when does the BeforeUpdate fire? Not before the data in the
control has been comitted to the table? The Help docs aren't very
specific-
they play pretty lose about what exactly is being updated and when.

Thanks again.

Check the vbProperCase option of the StrConv function, I believe it will
do
what you're after. You can use this function in the textbox's AfterUpdate
event (not BeforeUpdate) to change the case. Also, I would recommend
separate first and last name fields. You can concatenate them together
when
you need them, but splitting them apart later can be a real pain.

--
Wayne Morgan
MS Access MVP

I have a simple form consisting of a text box in which the user enters a
name
in the form 'LastName, FirstName', and a combo box which allows the
user
to
assign a role (one role per person, roles are static) to the name
entered.
The RecordSource for the form is a table consisting of: user ID (PK,
autonumber), Full Name (text, 50), a role ID (long, the bound column of
the
combo box), and an email (text, currently ignored, not required, may be
zero
length). There are no validation rules or input formats set on any of
the
fields in the table.

I want the txtFullName to be able to accept the user's input as all
lower,
upper or mixed case and either convert on the fly or be 'fixed up' so
that
it
will look like a name: >L<?????????, >L<????????? (the usual format
mask).
However, I don't want to tell the user 'Sorry that name is too long'.
So
if
they enter a 15 character last name or a 12 character first name, the
format
still has to work.

1. The simplest way to do this would be with an InputMask. Is there a
way
to
specify an arbitrary length input mask to satisfy the intent mentioned
above?
I.e., is there something equivalent to a [Perl] RE like '.*' (a
multiplier)
so that the Input Mask would read something like: '>L<?*, >L<?*'? (I'm
looking for the 95%+ solution right now.)

2. I have tried a BeforeUpdate event procedure that looks as follows:

Private Sub txtFullName_BeforeUpdate (Cancel as Integer)
dim val as String
val = nameUpper (txtFullName)
txtFullName = val
Cancel = False
End Sub

Private Function nameUpper (val as String) as String
dim commaPos as Long
val = UCase (Left (val, 1)) & Mid (val, 2)
commaPos = InStr (2, val, ", ")
if commaPos > 0 then
val = Left (val, commaPos + 1) & _ ' Preserve last
name
part
UCase (Mid (val, commaPos + 2, 1) & _ ' Cap the
first name
Mid (val, commaPos + 3) ' Preserve rest
of
first name
end if
nameUpper = val
End Function

This function works correctly and can be tailored to suit. But the
event
procedure upon return from the function displays the following run time
error
(at the line which reads
'txtFullName = val'):

Run Time Error '-2147352567 (800200009)

The macro or function set to the BeforeUpdate or ValidationRule
property
for
the field is preventing the <name of the database> from saving the data
in
the field. There are, as I said above, no validation rules applied to
any
field in this table.

What's happening here?

3. I've also tried some other event procedures and ended up getting
into
infinite loops (LostFocus, Exit). So either I did something wrong
(highly
probable) when I wrote those (similar to the BeforeUpdate procedure
above;
they called the nameUpper() function to do the real work) or I am
missing
some condition which would break out of the loop. Something like Dirty?

4. Another option is a KeyPress event procedure, but I don't know how
to
tell the procedure to convert to upper case only on the first character
entered, and the first character following a blank. Nothing seems to be
'in
the text control' yet, so I can't test length or do InStr() calls to
look
for
the blanks. When I've tried this, I get 'Invalid Use of Null' errors
because
the text control's value is null.

If #1 is the best answer, I'll just go with that. But I'm more
interested
in
knowing what the problem is with the other options.

TIA. Apologies for being so verbose.

 

[Chaim]

Thanks for that explanation, Wayne. Enlightening and much appreciated.

The form's AfterUpdate fires after the table has been updated, but the
control's AfterUpdate fires after the control has been updated. The record
has not yet been saved to the table. Changing the value of the control via
VBA doesn't cause the BeforeUpdate to fire, so you won't get a loop, but it
does interfere with the update because you are changing the value while the
update is being processed (the AfterUpdate hasn't fired yet). The
AfterUpdate event of the control will fire before the form's BeforeUpdate
event, so the changes will be saved.

--
Wayne Morgan
MS Access MVP

Wayne,

Thanks. The vbProperCase works perfectly. I don't use separate first and
last name fields in the table for a reason that totally escapes me now
(and
the documentation that would let me refresh my memory is not available
from
this location- don't ask;-(). I started things off that way but I can't
remember now why I changed them.

Why the AfterUpdate procedure? Doesn't that fire after the table has been
updated? This is something I haven't been able to get clear from the
online
Help. If so, how does the data that's already been saved get changed? And
on
the flip side, when does the BeforeUpdate fire? Not before the data in the
control has been comitted to the table? The Help docs aren't very
specific-
they play pretty lose about what exactly is being updated and when.

Thanks again.

Check the vbProperCase option of the StrConv function, I believe it will
do
what you're after. You can use this function in the textbox's AfterUpdate
event (not BeforeUpdate) to change the case. Also, I would recommend
separate first and last name fields. You can concatenate them together
when
you need them, but splitting them apart later can be a real pain.

--
Wayne Morgan
MS Access MVP


I have a simple form consisting of a text box in which the user enters a
name
in the form 'LastName, FirstName', and a combo box which allows the
user
to
assign a role (one role per person, roles are static) to the name
entered.
The RecordSource for the form is a table consisting of: user ID (PK,
autonumber), Full Name (text, 50), a role ID (long, the bound column of
the
combo box), and an email (text, currently ignored, not required, may be
zero
length). There are no validation rules or input formats set on any of
the
fields in the table.

I want the txtFullName to be able to accept the user's input as all
lower,
upper or mixed case and either convert on the fly or be 'fixed up' so
that
it
will look like a name: >L<?????????, >L<????????? (the usual format
mask).
However, I don't want to tell the user 'Sorry that name is too long'.
So
if
they enter a 15 character last name or a 12 character first name, the
format
still has to work.

1. The simplest way to do this would be with an InputMask. Is there a
way
to
specify an arbitrary length input mask to satisfy the intent mentioned
above?
I.e., is there something equivalent to a [Perl] RE like '.*' (a
multiplier)
so that the Input Mask would read something like: '>L<?*, >L<?*'? (I'm
looking for the 95%+ solution right now.)

2. I have tried a BeforeUpdate event procedure that looks as follows:

Private Sub txtFullName_BeforeUpdate (Cancel as Integer)
dim val as String
val = nameUpper (txtFullName)
txtFullName = val
Cancel = False
End Sub

Private Function nameUpper (val as String) as String
dim commaPos as Long
val = UCase (Left (val, 1)) & Mid (val, 2)
commaPos = InStr (2, val, ", ")
if commaPos > 0 then
val = Left (val, commaPos + 1) & _ ' Preserve last
name
part
UCase (Mid (val, commaPos + 2, 1) & _ ' Cap the
first name
Mid (val, commaPos + 3) ' Preserve rest
of
first name
end if
nameUpper = val
End Function

This function works correctly and can be tailored to suit. But the
event
procedure upon return from the function displays the following run time
error
(at the line which reads
'txtFullName = val'):

Run Time Error '-2147352567 (800200009)

The macro or function set to the BeforeUpdate or ValidationRule
property
for
the field is preventing the <name of the database> from saving the data
in
the field. There are, as I said above, no validation rules applied to
any
field in this table.

What's happening here?

3. I've also tried some other event procedures and ended up getting
into
infinite loops (LostFocus, Exit). So either I did something wrong
(highly
probable) when I wrote those (similar to the BeforeUpdate procedure
above;
they called the nameUpper() function to do the real work) or I am
missing
some condition which would break out of the loop. Something like Dirty?

4. Another option is a KeyPress event procedure, but I don't know how
to
tell the procedure to convert to upper case only on the first character
entered, and the first character following a blank. Nothing seems to be
'in
the text control' yet, so I can't test length or do InStr() calls to
look
for
the blanks. When I've tried this, I get 'Invalid Use of Null' errors
because
the text control's value is null.

If #1 is the best answer, I'll just go with that. But I'm more
interested
in
knowing what the problem is with the other options.

TIA. Apologies for being so verbose.