• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Macro command equal to VBA replace function (2 Viewers)

kengooch

New member
Local time
Today, 13:52
Joined
Feb 29, 2012
Messages
24
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, 15:52
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, 13:52
Joined
Oct 29, 2018
Messages
12,456
Hi. Have you tried the Set Value action?

Sent from phone...
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 16:52
Joined
Apr 27, 2015
Messages
3,011
KenGooch,

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

kengooch

New member
Local time
Today, 13:52
Joined
Feb 29, 2012
Messages
24
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

New member
Local time
Today, 13:52
Joined
Feb 29, 2012
Messages
24
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, 13:52
Joined
Oct 29, 2018
Messages
12,456
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, 15:52
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

New member
Local time
Today, 13:52
Joined
Feb 29, 2012
Messages
24
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