Macro/Function to replace spaces

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

I am having a problem getting a macro and function to work together.

I have Field_One where a string gets entered. I want Field_Two to
contain the same string with the spaces replaced by underscores in a
form.

I have a simple SetValue Macro for Field_One which will place the
string from Field_One into Field_Two on exiting the control. Works
fine.

I have a Function (found on the group) which will do the replace, but
I can't get it to happen automatically.
----
With Me.Field_Two
.Value = Replace(.Value, " ", "_")
End With
----
Field_Two has [Event Procedure] set for On Change. I thought that the
Macro making the change would trigger the function butIt doesn't.
Also tried On Dirty.

Clearly, I am lacking the fundamentals here. Can someone show me how
to make this work? Even a completely different approach would be
fine.

TIA
gary
 
I am having a problem getting a macro and function to work together.

I have Field_One where a string gets entered. I want Field_Two to
contain the same string with the spaces replaced by underscores in a
form.

I have a simple SetValue Macro for Field_One which will place the
string from Field_One into Field_Two on exiting the control. Works
fine.

I have a Function (found on the group) which will do the replace, but
I can't get it to happen automatically.
----
With Me.Field_Two
.Value = Replace(.Value, " ", "_")
End With
----
Field_Two has [Event Procedure] set for On Change. I thought that the
Macro making the change would trigger the function butIt doesn't.
Also tried On Dirty.

Clearly, I am lacking the fundamentals here. Can someone show me how
to make this work? Even a completely different approach would be
fine.

TIA
gary
Click to expand...

Gary,
I'm not clear on what you are doing, or why, but some events do not
fire unless the focus is on that control and the data is typed in.

If you are entering data in Control1 you can use the Control1
AfterUpdate event to set a value in Control2. But Control2's Change
event will not fire. Perhaps you can do the job using Control1's
AfterUpdate event, something like this:

[Control2] = Replace(Me![Control1]," ","_")
DoCmd.RunCommand acCmdSaveRecord
Then add here whatever code you wanted to have run from the Control2
change event.

If you are saving the Control2 data to a table, it is redundant to
have 2 fields with basically the same data. You might wish to
normalize your table.
 
Gary said:
I am having a problem getting a macro and function to work together.

I have Field_One where a string gets entered. I want Field_Two to
contain the same string with the spaces replaced by underscores in a
form.

I have a simple SetValue Macro for Field_One which will place the
string from Field_One into Field_Two on exiting the control. Works
fine.

I have a Function (found on the group) which will do the replace, but
I can't get it to happen automatically.
----
With Me.Field_Two
.Value = Replace(.Value, " ", "_")
End With
----
Field_Two has [Event Procedure] set for On Change. I thought that the
Macro making the change would trigger the function butIt doesn't.
Also tried On Dirty.

Clearly, I am lacking the fundamentals here. Can someone show me how
to make this work? Even a completely different approach would be
fine.
Click to expand...

Neither the Change event nor the BeforeUpdate and AfterUpdate events
will fire when the value of a control is modified programmatically.
They only fire when they are changed by user action. So your Change
event for Field_Two won't fire. By the way, the AfterUpdate event would
have been a better choice anyway, since it only fires when the user
finishes editing the control, where the Change event fires for every
keystroke that changes the text in the control.

If Field_Two is always supposed to show the blank-replaced value of
Field_One, why not just make it a calculated control by setting its
ControlSource property to:

=Replace([Field_One] & "", " ", "_")

Then you wouldn't need any code or macros in your event procedures. The
calculated value won't be stored in your table, of course, but
calculated values usually shouldn't be.
 
Back
Top