Disallow space character

[shiro]

Hi all,
how to cancel the update even of a txtbox
if the user type a value with space character.
Or automatically delete the space character
since I won't the data with space character.
(space is disallow)
thank's in advance.

 

[Marshall Barton]

how to cancel the update even of a txtbox
if the user type a value with space character.
Or automatically delete the space character
since I won't the data with space character.
(space is disallow)

You can use the BeforeUpdate event with code like:

If Me.textbox Like "* *" Then Cancel = True

Or, you can remove any spaces by using the AfterUpdate
event:

Me.textbox = Replace(Me.textbox, " ", "")

 

[strive4peace]

Hi shiro,

use the control's BeforeUpdateevent to Cancel the update if there is
something that does not fit the requirements for the data

'~~~~~~~~~~~~
if InStr(me.activecontrol," ") > 0 then
msgbox "Value cannot contain a space",,"Re-enter Value"
CANCEL = true
exit sub
end if
'~~~~~~~~~~~~

or, if you just want to correct it for them, use the control AfterUpdate
event

'~~~~~~~~~~~~
if not isNull(me.activecontrol) then
me.activecontrol = replace(me.activecontrol, " ","")
end if
'~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[shiro]

And Thank's for the response,
But how to edit the existing data,
the field had 4000 record,can
edit it with a code,
Thank's again

 

[strive4peace]

Hi shiro,

you're welcome

make a new query with this as its SQL:

~~~
UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~

WHERE
Tablename is the name of your table
fieldname is the name of the field

~~~

Then, click the exclamation point ! icon to run the query

be sure to backup your database first!!!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[shiro]

Sorry to keep asking Mr Strieve,
but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro

Hi shiro,

you're welcome

make a new query with this as its SQL:

~~~
UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~

WHERE
Tablename is the name of your table
fieldname is the name of the field

~~~

Then, click the exclamation point ! icon to run the query

be sure to backup your database first!!!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

And Thank's for the response,
But how to edit the existing data,
the field had 4000 record,can
edit it with a code,
Thank's again

 

[strive4peace]

what version of Access are you using

btw, I am not a "Mr", my name is in my siggy

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Sorry to keep asking Mr Strieve,
but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro

Hi shiro,

you're welcome

make a new query with this as its SQL:

~~~
UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~

WHERE
Tablename is the name of your table
fieldname is the name of the field

~~~

Then, click the exclamation point ! icon to run the query

be sure to backup your database first!!!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

And Thank's for the response,
But how to edit the existing data,
the field had 4000 record,can
edit it with a code,
Thank's again


Hi shiro,

use the control's BeforeUpdateevent to Cancel the update if there is
something that does not fit the requirements for the data

'~~~~~~~~~~~~
if InStr(me.activecontrol," ") > 0 then
msgbox "Value cannot contain a space",,"Re-enter Value"
CANCEL = true
exit sub
end if
'~~~~~~~~~~~~

or, if you just want to correct it for them, use the control AfterUpdate
event

'~~~~~~~~~~~~
if not isNull(me.activecontrol) then
me.activecontrol = replace(me.activecontrol, " ","")
end if
'~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




shiro wrote:
Hi all,
how to cancel the update even of a txtbox
if the user type a value with space character.
Or automatically delete the space character
since I won't the data with space character.
(space is disallow)
thank's in advance.

 

[shiro]

Ups,I'm sorry.
I work with access 2000.

what version of Access are you using

btw, I am not a "Mr", my name is in my siggy

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Sorry to keep asking Mr Strieve,
but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro

Hi shiro,

you're welcome

make a new query with this as its SQL:

~~~
UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~

WHERE
Tablename is the name of your table
fieldname is the name of the field

~~~

Then, click the exclamation point ! icon to run the query

be sure to backup your database first!!!


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




shiro wrote:
And Thank's for the response,
But how to edit the existing data,
the field had 4000 record,can
edit it with a code,
Thank's again


Hi shiro,

use the control's BeforeUpdateevent to Cancel the update if there is
something that does not fit the requirements for the data

'~~~~~~~~~~~~
if InStr(me.activecontrol," ") > 0 then
msgbox "Value cannot contain a space",,"Re-enter Value"
CANCEL = true
exit sub
end if
'~~~~~~~~~~~~

or, if you just want to correct it for them, use the control AfterUpdate
event

'~~~~~~~~~~~~
if not isNull(me.activecontrol) then
me.activecontrol = replace(me.activecontrol, " ","")
end if
'~~~~~~~~~~~~

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




shiro wrote:
Hi all,
how to cancel the update even of a txtbox
if the user type a value with space character.
Or automatically delete the space character
since I won't the data with space character.
(space is disallow)
thank's in advance.

 

[Marshall Barton]

Make sure that you have all the updates.

If that doesn't take care of it, create a function in a
standard module:
Public Function MyReplace(strIN, strSub, strWith)
MyReplace = Replace(strIN, strSub, strWith)
End Function

And use the function in the query.
--
Marsh
MVP [MS Access]

I work with access 2000.

but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro


UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~
WHERE
Tablename is the name of your table
fieldname is the name of the field

 

[shiro]

I'm sorry Marsh,
How to use a module in a query? :-

Make sure that you have all the updates.

If that doesn't take care of it, create a function in a
standard module:
Public Function MyReplace(strIN, strSub, strWith)
MyReplace = Replace(strIN, strSub, strWith)
End Function

And use the function in the query.
--
Marsh
MVP [MS Access]

I work with access 2000.

shiro wrote:
but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro


UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~
WHERE
Tablename is the name of your table
fieldname is the name of the field

 

[strive4peace]

**********************************************************
*** How to Create a Standard (General) Module (pre-2007) ***

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen
-- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it. You can have
several procedures (Subs and Functions) in a module, which gives you a
way to categorize them ... ie: basic procedures that would be useful in
any database; procedures that are specific to a particular database;
procedures for converting data; etc


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I'm sorry Marsh,
How to use a module in a query? :-

Make sure that you have all the updates.

If that doesn't take care of it, create a function in a
standard module:
Public Function MyReplace(strIN, strSub, strWith)
MyReplace = Replace(strIN, strSub, strWith)
End Function

And use the function in the query.
--
Marsh
MVP [MS Access]

I work with access 2000.

shiro wrote:
but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro


UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~
WHERE
Tablename is the name of your table
fieldname is the name of the field

 

[Douglas J. Steele]

As Marsh said, you use the function (not the module) in your query.

Instead of

UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")

use

UPDATE [Tablename]
SET [fieldname] = MyReplace([fieldname], " ","")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry Marsh,
How to use a module in a query? :-

Make sure that you have all the updates.

If that doesn't take care of it, create a function in a
standard module:
Public Function MyReplace(strIN, strSub, strWith)
MyReplace = Replace(strIN, strSub, strWith)
End Function

And use the function in the query.
--
Marsh
MVP [MS Access]

I work with access 2000.

shiro wrote:
but the SQL statement send error :
"Undefined function replace in expression"
What might be wrong?

Thank's Shiro


UPDATE [Tablename]
SET [fieldname] = replace([fieldname], " ","")
~~~
WHERE
Tablename is the name of your table
fieldname is the name of the field