Hello Group. I need to change the current currency from US$ to GBP. I know I need to update the text boxes in the forms and reports, but the problem is there are almost 100 objects. Is there a function that can iterate through the objects (forms and reports), update the currency fields, and save the changes?
With basic out-of-the box Access, no. However there are third party Access project tools with varying degrees of editing support. One that has been mentioned many times is MZ-Tools, which you can look up online. It is a commercial product and is not free to obtain. I cannot personally recommend it for a couple of reasons, one of which is that I don't have it myself and have never used it. Therefore, my knowledge of it is strictly second-hand information. But various members have spoken in favor of it. I also want to clarify that the forum does not officially support any particular toolkit. I cannot speak for the forum when discussing a 3rd-party package. But if mentioning its name helps you, I can go that far.
Given the range of objects you have to change, I wonder, though, if you are facing a lot of manual edits anyway. I can only wish you luck on that.
It's not clear what this means.
• Maybe you need to do a currency conversion from US$ to GBP at the current conversion rate?
• Maybe the currency symbol can just be changed in Windows regional settings?
• Maybe your textboxes have a custom TextBox.Format property value that explicitly uses the "$" character in that format?
Each of those might be what you mean. Each of those require a different fix.
Traversing all objects is easy, but what you want to do with that is tricky. For example, you can use Application.CurrentProject, then...
If it's forms, use AllForms:
Code:
Sub TraverseForms()
Dim f As Object
For Each f In Application.CurrentProject.AllForms
'do something with the object
Next f
End Sub
If it's reports, same, but use AllReports:
Code:
Sub TraverseReports()
Dim f As Object
For Each f In Application.CurrentProject.AllReports
'do something with the object
Next f
End Sub
By the way, there are other All~ collections, as you can see in the following screenshot from the debugger:
Just beware, objects from the All~ collections are not actually their type of objects, they are either Variants, simple Objects or AccessObjects, as you can see in the following screenshot from the debugger:
It's very common to try to traverse the All~ collections using, say, a form variable in the AllForms collection. That results in an error because objects in the AllForms collection are Variant/Object/AccessObject, not Form objects.
So what can you do with this information? Two things come to mind:
1. If you wish to use, say, the Forms collection like this: Forms("MyForm"). You would need the forms to be open, that's because the Forms collection can only see open forms, so using a loop like this would let you change a group of open forms to design view, for example:
Code:
Sub TraverseForms()
Dim f As Object
For Each f In Application.CurrentProject.AllForms
Forms(f.Name).SetFocus
DoCmd.RunCommand acCmdDesignView
Next f
End Sub
2. A cool trick to leverage the All~ collections is to use SaveAsText and LoadFromText to make your changes to something that isn't actually open. But that comes with the requirement that you must know how the SaveAsText output for the object looks. Only then could you reliably output the blueprint with SaveAsText, make your change using string handling, and finally replace the original through LoadFromText. Here's a function that changes a property in a SaveAsText output, particularly, it looks for a particular type of control with a certain name and modifies the value of the property you want with the value you choose.
Code:
Check the attachment, AWF does not let me post this code for its length
You would have to output the file first, modify the file then import the file, as follows:
Code:
Sub test()
Dim filePath As String
filePath = Application.CurrentProject.path
Dim f As Object
For Each f In Application.CurrentProject.AllForms
SaveAsText acForm, f.Name, filePath & "\" & f.Name
ChangePropertyValue filePath & "\" & f.Name, "DefaultValue", "Texto0", "Textbox", f.Name & " " & "hello"
LoadFromText acForm, f.Name, filePath & "\" & f.Name
Next f
End Sub
The routine above traverses all the forms in your database, looks for a textbox named "Texto0" and, if it has a default value, it will replace it with the name of the form and a hello. I'm using this example because you seem to want to modify something in all your forms. This is a way to do it.
But I know, it's tricky, and it is tricky for a bunch of reasons. One of them is that the output sometimes breaks long instructions into multiple lines, but you can work around that. Another headache is that even though a lot of properties use strings, user-input strings need escaped quotes. DefaultValue needs the double quotes escaped, but something like FontName doesn't.
Anyway, I added some code so you can handle these weird cases whenever you run into them. Just add them to the ReplacePropertyValue function. Also, some objects use their own Begin...End patterns, like Labels, which makes things even messier. Honestly, stuff like this is probably why Microsoft doesn't update Access as often as they should. They made a bit of a mess, in my opinion.
Still, you can do a lot with just what’s in this post.
Hello Group. I need to change the current currency from US$ to GBP. I know I need to update the text boxes in the forms and reports, but the problem is there are almost 100 objects. Is there a function that can iterate through the objects (forms and reports), update the currency fields, and save the changes?
You could use DAO to iterate through the tables and their columns, which is actually what you need to do (the forms and reports should follow from that).
But imagine the consequence of dropping and adding columns with data in it?
You can easily iterate through the forms collection and open them in acdesign and iterate through controls, testing for the control type, and if so, change its format
