Solved Macro command equal to VBA replace function (1 Viewer)

kengooch

Member
Local time
Today, 09:05
Joined
Feb 29, 2012
Messages
137
I need a Macro command so I can check a form field for "," and replace it with ", "
Thanks in advance for your help!
 

vba_php

Forum Troll
Local time
Today, 11:05
Joined
Oct 6, 2019
Messages
2,884
there isn't one. why can't you do it with code? not to mention that this question is very unusual. I don't think I've ever seen it asked before. although people *have* asked it regarding excel sheets. but the bigger question is...why are you doing it? are you preparing to parse the box values out using a delimiter or something?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried the Set Value action?

Sent from phone...
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 12:05
Joined
Apr 27, 2015
Messages
6,281
KenGooch,

Have you considered using an update query in conjunction with the Replace() Function? The format would be:
Replace ( [FieldName], ",", ", ")
 

kengooch

Member
Local time
Today, 09:05
Joined
Feb 29, 2012
Messages
137
there isn't one. why can't you do it with code? not to mention that this question is very unusual. I don't think I've ever seen it asked before. although people *have* asked it regarding excel sheets. but the bigger question is...why are you doing it? are you preparing to parse the box values out using a delimiter or something?

So, we use the Database to track info from our Medical Records system, that doesn't have a way to track this particular issue. We block and copy the name from the MRS to the Database and it put's a comma with no space between the lastname,firstname. For the ProperCase function to work there has to be a space. As to writing the VBA code, I did. but I was hoping to put it in a macro, they seem cleaner and faster. Here is the VBA code I wrote that works perfectly.

Private Sub tPatient_AfterUpdate()
If InStr(tPatient, ", ") Then
tPatient.Value = StrConv(tPatient, vbProperCase)
Else
tPatient.Value = Replace(tPatient, ",", ", ")
tPatient.Value = StrConv(tPatient, vbProperCase)
End If
End Sub
 

kengooch

Member
Local time
Today, 09:05
Joined
Feb 29, 2012
Messages
137
I saw that as a possibility, but for some reason I'm not sure how to link a form field to a query, or perhaps I should have just written it into the underlying query upon which the form is built... I guess I just haven't thought it through. My first thought is usually VBA then Macros... Thanks so much for the help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:05
Joined
Oct 29, 2018
Messages
21,358
Can the setvalue function find and replace characters?
Hi. The SetValue action can be used to set the value to almost anything you want. For example:

setvalue.png
 

vba_php

Forum Troll
Local time
Today, 11:05
Joined
Oct 6, 2019
Messages
2,884
yeah. i don't know all the macro actions, as I never use macros. i just know a part of the list. don't even remember setValue() at this point. take care Ken. nice work, guy. =)
 

kengooch

Member
Local time
Today, 09:05
Joined
Feb 29, 2012
Messages
137
Thanks everyone. Also I found out that you can convert a macro to VBA automatically from the tool bar. It is interesting to see the code that is auto generated. If you try this, I would suggest, make a copy of your database so you have all of your original work.
 

Users who are viewing this thread

Top Bottom