Solved Macro command equal to VBA replace function

[kengooch]

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]

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]

Hi. Have you tried the Set Value action?

Sent from phone...

 

[NauticalGent]

KenGooch,

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

 

[kengooch]

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

 

[NauticalGent]

but I was hoping to put it in a macro, they seem cleaner and faster.

See post #4

 

[kengooch]

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!

 

[kengooch]

Hi. Have you tried the Set Value action?

Sent from phone...

Can the setvalue function find and replace characters?

 

[theDBguy]

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:

 

[NauticalGent]

Hi. The SetValue action can be used to set the value to almost anything you want. For example:

View attachment 79950

Learn something new every day...

 

[kengooch]

Hi. The SetValue action can be used to set the value to almost anything you want. For example:

View attachment 79950

That's actually exactly what I was looking for!! You guys are the best!!

 

[theDBguy]

That's actually exactly what I was looking for!! You guys are the best!!

Hi. Glad we could all assist. Good luck with your project.

 

[vba_php]

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]

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.