Browse DevX
Sign up for e-mail newsletters from DevX


Forms, Formats, and Contents

A step-by-step tutorial that shows how to take advantage of the DataFormat property exposed by data bound controls DataFormat property, and of the StdDataFormat objects, in order to get the visualization of different currencies, while keeping the data in database tables unchanged.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

was studying the excellent Francesco Balena book, "Programming Visual Basic 6.0," edited by Microsoft Press, the cost of which is widely compensated by both the book and the enclosed CD contents—:when I ran into the explanation of DataFormat property for data bound controls, and of StdDataFormat object. It seemed a very interesting subject for an article, so here it is. At the same time, I thank Francesco for this opportunity.

One of DAO (and RDO) defects is that in order to view data in a definite format we must write specific code when reading or writing data (to convert a viewing formatted value into field formatted one and viceversa), and this defect influences the data editing phase and the input one too.

ADO exposes the DataFormat property for data bound controls, that allow us to view data in the wanted format, while it remains in its native format in the table field. Custom formats are possible too. DataFormat property sets or returns a format object, not a format string, as you might think if you are familiar with Format$ function. This object has not only some properties, in which the obvious Format one, but also very useful events (which I'll speak about afterwards).

Yet, for more demanding formatting needs, such as the currency conversion ones, for example, DataFormat property may turn out to be insufficient. Let's suppose we have a products table with prices in Currency format, and set in the currency of your country. We can set DataFormat property as Currency and so we get, since our system is so set, a custom format for our currency. Everything works very well: we 'see' the prices in Currency format on the form and in numeric format in the table. Let’s make things a bit more complex now, by displaying values as Euro currency on the form while continuing to keep the value in the native currency in the database table. This task requires some code for conversions between the currencies and a different format for each one.

That's why a library provided with VB ("Microsoft Data Formatting Object Library") is useful: it exposes StdDataFormat object. Once we have instanced such an object, we can set the properties we are interested in (the Type property in particular), assign it to a data bound control DataFormat property and take advantage of Format and UnFormat events, that represent its real power.

The Format event raises when data is 'read' from the data source and is about to be displayed in the bound control (more in general, used by the data consumer), whereas the UnFormat event raises when a data bound control value (if changed) is written back to the data source. This means that we have two places which we can write code, for complex formatting operations, instead of writing in several places, as we must do with a DAO (or RDO) control.

Another very interesting aspect is that it's not necessary we set the properties of a StdDataFormat object: we can just take advantage of its events to manage by code the formatting operations, as in this case, when a currency conversion is involved.

If we don't use a StdDataFormat object (that is we rely on DataFormat property only) these properties and events are managed implicitly. Nevertheless my idea is of dealing with Euro-other currency conversion, without creating a new field in the table, and with as little code quantity as possible. As a matter of fact I need to translate the displayed data in the selected currency (Euro or my currency), keeping the original format (in my currency) in the data source table.

The Study Project, Step by Step
Create a new project, select it in Project Window and name it as EuroConvert.
Select Form1 and name it frmProdotti; set its Caption as "Products".
Add the "Microsoft ADO Data Control 6.0 (SP3)(OLEDB)" to the project components.
Add the "Microsoft Data Formatting Object Library" to the project references.
Add to frmProdotti an ADODC control (Adodc1), and set its Align property as 2 - vbAlignBottom.
Click on Adodc1 with the right mouse button and select Properties: In the General tab set the connection source as 'Use connection string', click on Generate button, select "Microsoft Jet 3.51 OLE DB Provider", then click on Next button and then on the little button on the right of the textbox to select file Nwind.mdb, that is in the same VB folder; verify the connection clicking on the proper button and then on OK button.
TAKE CARE: during this study the data will be changed. Therefore, if you want to save the original file, get a copy of it to restore the fact before the study.
In the RecordSource tab of Properties dialog for Adodc1, set the Command Type as 2 - adCmdTabel and select Products table.

Add to frmProdotti a textbox and a label for two fields of the table (ProductName, UnitPrice) setting adequately first the DataSource property for the textboxes (Text1 and Text2 ) as Adodc1 and then the DataField one to the relative field, then 'copy' the name of the field and 'paste' it in the Caption property of the relative label (Label1 and Label2).
Near the Text2 textbox, the one for the price, put an OptionButton (Option1), 'copy' it and 'paste' it and answer 'Yes' to get a two OptionButton array (Option1(0) and Option1(1)), set their Caption property as "our currency" and "Euro", respectively; the former Value property is set as True (I'm calling 'our currency' what you must intend for French, or German, or other country currency).

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date