VBWrapp.dll and Sample project.| VBWrapp.dll By Halvor Nyberg |
VbWrapp.dll for
Visual Basic 5.0 contains a lot of useful functions that makes advanced programming tasks easy and powerful.By adding 7 lines of code to your project (calling functions from VbWrapp), You have given your users flexibility to customize all Captions, Headers, ToolTips, Default Values and Grid Column widths. You have made your form seekable on all fields or the combination of all fields, displayed the result of the seek/query in an elastic Form with a DBGrid or MSFlexGrid, sorting by the Column that is "HeadClicked". You have added Copy, Paste and Multi Delete functionality to Your DBGrid. Some more lines of code and You have the Combo In Grid functionality. The Good news is that these functions are fast in performance and is illustrated in the demo project that You may download here.
VBWrapp.dll and Sample project.
First I give a short description on some of the functions, and follow up with a more detailed description of functions that I think is not so obvious in the demo example you may Download.
VbWrapp provides a function for dynamically generating the WHERE-Clause based on the values that the users types or chooses (in textboxes, combo boxes, checkboxes) at runtime. This is a dynamic and easy method for making your form seekable on all fields. You just have to put the Select part in front of the generated WHERE-Clause, because I can't know which field You want to present for the user. This method supports seeking with Wildcards on String Fields, and operators as "=>", "<=", ">", "<" (e.g. <2000) is supported on Numeric, Date and Text Data types Fields.
If you need to merge information from your VB application to your Word processor, VbWrapp can create a ready to use merge source (as text file) that you can dynamically use with your Word processor Templates. I find it useful and resource saving to use templates that has a text file as data source. To produce a document from the template I use OLE automation. To Merge updated information with the template I just use the VbWrapp function SaveMergeFieldsFromTags that changes the content of a text file (data source file). This method makes it very easy to maintain and develop new templates that use fields from the text file. I just have to do changes in the templates, not in the VB code as you have to if you're going to insert text into the Word document based on bookmarks etc..
VbWrapp also provides powerful and fast functions that lets the user Customize your application captions, ToolTips, grid widths and headers (by editing ini files). Are you tired of rewriting, compiling and distributing when you have to change grid captions, label captions, menu items etc., or you want to provide support for multiple languages? This functions solves your problems by letting your users customize:
Captions
ToolTips
Column widths
VBWrapp helps you to satisfy the users need for automation (use of Default values) when registering values in forms. Based on a value that is registered in a field (textbox, DBCombo, Combo, Check) a easy function call writes this value to an ini file. The next time the user e.g. want to add a new record, all the default values are automatically assigned. (In the sample: see the Mouse Down event of a textbox).
I often use the DBGrid control to let the user edit lookup tables etc, or the MSFlexGrid to present data. Therefor I have developed a functionality that already exist in Delphi and PowerBuilder, namely Combo boxes in the Grid. Another functionality I provide with Grids is sorting by Columns, for example when the user clicks a column header. If you use forms that just consists of a grid and Command buttons I have a function for making it Elastic. I missed the Clip property when I started working with the DBGrid, so I developed to functions. CopyFromDBGrid that puts the selected region of the DBGrid to the Clipboard, ready to paste into e.g. Excel or paste into another DBGrid by using my other function PasteToDBGrid. Finally I wrote a function for deleting all selected cells from the DBGrid.
Okay again, so by adding 7 lines of code to your project (calling functions from VBWrapp), You have given the user flexibility to customize your Forms Captions, Headers , ToolTips, Default Values and Column widths. You have made your form seekable on all fields, displayed the result of the seek/query in a elastic Form with a DBGrid or MSFlexGrid sorting by the Column that is "HeadClicked", and You have added Copy, Paste and Multi Delete functionality to Your DBGrid. The Good news is that these functions are very fast in performance.
Function Description
Functions for Customizing The basic principle for all this functions is that ini files holds pointers (ini key) to each control on a form. Customizing is done by editing/setting values to this keys.Customizing Captions Applies to all object with a Caption property (Labels, Menu items, Commands, SSCommands, SSPanels, Tabs, DBGrid Columns, every control with a Caption property and MSFlexGrid and MSGrid), may be customized by Users via the Ini file (Captions.ini, located in the Windows directory).
The syntax in Captions.ini is like this:
CAPTIONS.INI
[ Form1]CustomCaptions=True
Original Caption=User Assigned Caption
MSFlexGrid1 1 2=This is MSFlexGrid1, |with Index = 1| And Column=2
MSFlexGrid1 2=This is MSFlexGrid1, |with no Index| And Column=2
Here is an example related to the Demo. (Captions.ini is produced when choosing the menu item Generate Ini captions. I have then set CustomCaption=True And placed a "c" in front of each value).
[frmSuppliers]
CustomCaption=True
ComboInGrid=cComboInGrid
&Add=cAdd
&Update=cUpdate
&Delete=cDelete
&Refresh=cRefresh
&Close=cClose
Seek=cSeek
Mode=cMode
&Service=cService
Record: 1=cRecord 1
grdDataGrid 0=cProductID
grdDataGrid 1=cProductName
grdDataGrid 2=cSupplierID
grdDataGrid 3=cCategoryID
grdDataGrid 4=cQuantityPerUnit
grdDataGrid 5=cUnitPrice
grdDataGrid 6=cUnitsInStock
grdDataGrid 7=cUnitsOnOrder
grdDataGrid 8=cReorderLevel
grdDataGrid 9=cDiscontinued
Fax:=cFax:
Phone:=cPhone:
Country:=cCountry:
PostalCode:=cPostalCode:
Region:=cRegion:
City:=cCity:
Address:=cAddress:
ContactTitle:=cContactTitle:
ContactName:=cContactName:
CompanyName:=cCompanyName:
SupplierID:=cSupplierID:
mnuCustomize=cCustomize
mnuGenerateIniCaptions=cGenerate Ini Captions
mnuGetIniCaptions=cGet Ini Captions
mnuGenerateIniToolTips=cGenerate Ini ToolTips
mnuGetIniToolTips=cGet Ini ToolTips
mnuGenerateIniGridWidths=cGenerate Ini GridWidths
mnuGetIniGridWidths=cGet Ini GridWidths
mnuPopUp=cmnuPopUp
mnuSetAsDefaultValue=cSet As Default Value
mnuGetDefaultValues=cGet Default Values
[ComboInGrid]
CustomCaption=False
Before any Customizing You have to produce the basic Captions.ini with a call to the function GenerateIniCaption(). You just pass the form that you want be able for customizing as a parameter. You may program the call to generate the ini file on a popup menu when the user right clicks the mouse or like in the sample, from a menu.
The users or developers may now edit this ini file by changing captions, and set CustomCaptions=True. Calling the function GetIniCaption() during Form.Activate will now produce the Captions based on Captions.ini, if it contains CustomCaptions=True below the Name of the Form.
This principles are also applying to the next customizing functions, namely ToolTips and Column widths.
Customizing ToolTips
Applies to every object with a ToolTip property. Note! The keys in INITIPS.INI is based on the caption properties of the controls. Therefor You have to call GetIniTips before calling GetIniCaptions.
Here is an example related to the Demo. (IniTips.ini is produced when choosing the menu item Generate Ini ToolTips. I have then set CustomTip=True and added values on the right side).
INITIPS.INI
[frmSuppliers]Customizing Column widths of Grids This function applies to the good old Grid, the MSFlexGrid and DBGrid.
COLWIDTHS.INI
Here is an example related to the Demo. (ColWidths.ini is produced when choosing the menu item "Generate Ini Column Widths".)
[ComboInGridDBGrid1]
CustomWidht=True
[frmSuppliersgrdDataGrid]
CustomWidht=True
0=299,9055
1=1728
2=383,811
3=552,189
4=1056,189
5=887,8111
6=1056,189
7=1056,189
8=1307,906
9=1307,906
Use default values when adding new records
Calling the function OcxWrapp.SetAsDefaultValue(Me.Name, Activecontrol.Name + Str$(Activecontrol.Index), tValue, "wDefVal.ini"), writes a reference to the control and the default value of that control to an ini file (here wDefVal.ini). When adding a new record, You may call OcxWrapp.GetDefaultValues(Me, "wDefVal.ini") that reads the default values from the ini file into the controls. Run the sample and "right click" a control to see how it functions. Function description Function: GetWhereClauseFromTags_s provides a function for generating the SQL WHERE-Clause based on the values that the users types in Textboxes and Combo boxes, at runtime. This is a dynamic and easy method for making your form seekable on every field. All You have to do is to assign a tag property (see assigned Tag properties in the sample) to each control you want to make seekable. Syntax for the tag property is: S=TableName("FieldName")!DataType;. Example: S=tblCompany("Name")!Text;. Valid Data Types is Number (All numeric types included Boolean), Text (String, Char) and Date. Function GetWhereClauseFromTags_s(vArrayControlArrays, bDaoOrCrystal As Boolean, bSqlCriteria As Boolean, ParamArray NotSeekThisTaggers() As Variant) As String Returns: Where Clause or SQL StatementParameters:
vArrayControlArrays = An array of Control Arrays that is to form the Where Clause. Example Set vArrayControlArrays=Array(txtFields(), DBCombo()), where txtFields() and DBCombo() are Control Arrays. bDaoOrCrystal = If True, DAO syntax is used in the returned SQL/WHERE statement. If False Crystal Report syntax is used in the returned SQL/WHERE statement.bSqlCriteria = If True, the returned statement is a complete Select statement. If False, the returned statement consists only the WHERE-Clause.
ParamArray NotSeekThisTaggers() = If any Field that is referred to in a tag property isn't to be used to form the SQL/WHERE statement
Example: Call GetWhereClauseFromTags_s(vArrayControlArrays, True, False, "Project ID").
Function:
GetBetweenWhereClauseFromTags_s provides a function for generating the SQL WHERE-Clause with the Between operator based on the values that the users types in Text boxes, at runtime. You have to use two Textboxes (as a control array incremented by one) for each Date Field You want to form the Between statement (e.g txtDate(0) and txtDate(1)). How it works at runtime: If the user types a date (1.1.98) only in the first of the textboxes (let say txtDate(0) with a tag property pointing to Field "InvoicedDate" ), GetBetweenWhereClauseFromTags_s returns "InvoicedDate > Datevalue("1.1.98")". If the user types "1.1.98" only in the second Textbox (let say txtDate(1) with a tag property pointing to Field "InvoicedDate"), GetBetweenWhereClauseFromTags_s returns "InvoicedDate < Datevalue("1.1.98")". If He or She types Date values in both of the textboxes, GetBetweenWhereClauseFromTags_s returns "InvoicedDate Between Datevalue("1.1.98") AND Datevalue("1.1.98")".
You have to do is to assign a tag property (see assigned Tag properties in the sample) to each control you want to form the Between statement. Syntax for the tag property is: S=TableName("FieldName")!DataType;. Example: S=tblCompany("InvoicedDate");. Valid Data Types is Date.
Function GetBetweenWhereClauseFromTags_s(caControlArray, iLbound As Integer,
iUbound As Integer, bDaoOrCrystal As Boolean, bSqlCriteria As Boolean) As String
Member of VBWrapper.DataAccessWrapper
Function:
With the function SaveMergeFieldsFromTags You can generate a semicolon delimited text file with fieldnames and values based on the controls on a form. This file can be used as a Merge Source in e.g. MSWord Templates. This is a very fast way of merging information on a VB form to your Word processor. All You have to do is to assign a tag property (see assigned Tag properties in the sample) to each control you want to add to the Merge file. Syntax for the tag property is: M=FieldName. Example: M=Company Name (place this at the end of the tag not followed by semicolon). Note! This property setting have to be placed at the end in the Tag property.
Function SaveMergeFieldsFromTags(vArrayControlArrays, tFileToSave As String,
[tAddressFields], [tAddressvalues]) As Integer
Member of VBWrapper.OcxWrapper
Returns: Error Number (If not used right)
Function:
FillRSFromUnboundControls_s makes it easy to Save Values of Unbound Controls to a recordset. You may call this function to Save the Values of a Control Array (e.g of Textboxes) to the Current Recordset. Requires that the DataField property is assigned to an Unbound Control even if it's missing a DataSource, which still makes it Unbound.
Function FillRSFromUnboundControls_s(objDatacontrol As Object, objTextBoxUnbound As
Object, iLbound As Integer) As String
Member of VBWrapper.DataAccessWrapper
Returns: Error Message (If not used right)
Parameters:
objDatacontrol = Datacontrol
objTextBoxUnbound = Control Array of Textboxes that aren't data bound. Requires that DataField property is assigned even if it's missing a DataSource.
iLbound = First element (e.g 2)of the objTextBoxUnbound that is passed.
Function:
FillUnboundControlsFromRS_s makes it easy to synchronize Unbound Controls to a recordset. When moving the Record pointer (bookmark) in a Recordset You may call this function to assign Current Recordset values to Unbound controls. Requires that the DataField property is assigned to an Unbound Control even if it's missing a DataSource, which still makes it Unbound.
Function FillUnboundControlsFromRS_s(objDatacontrol As Object, objTextBoxUnbound As
Object, iLbound As Integer) As String
Member of VBWrapper.DataAccessWrapper
Returns: Error Message (If not used right)
Parameters:
objDatacontrol = Datacontrol
objTextBoxUnbound = Control Array of Textboxes that aren't data bound. Requires that DataField property is assigned even if it's missing a DataSource.
iLbound = First element (e.g 2)of the objTextBoxUnbound that is passed.
Function:
ValidateDataTypeOnLostFocus_s checks if the typed text in a textbox is of right Data type (has the right format) required by the underlying Recordset. Requires that the DataField property is assigned to an Unbound Control even if it's missing a DataSource, which still makes it Unbound.
Function ValidateDataTypeOnLostFocus_s(objDatacontrol As Object, objControl As
Object, tDateFormatString As String) As String
Member of VBWrapper.DataAccessWrapper
Returns: Error Message (If not used right)
Parameters:
objDatacontrol = Datacontrol that is the Datasource to Controls in Your Form.
objControl = Control Array of Textboxes/Comboboxes or a singel Textbox/Combobox. Requires that DataField property is assigned even if it's missing a DataSource.
tDateFormatString = Date format that You're using (e.g "mm.dd.yy").
bControlArray = If true, Tells that the objControl is a Control Array.
Function:
SortRecordset_rs sorts the a Recordset by the Fields You pass as parameters.
Function SortRecordset_rs(objRecordset As Object, tFieldToSort As String) As
Recordset
Member of VBWrapper.DataAccessWrapper
Returns: Sorted Recordset
Parameters:
objRecordset = Recordset
tFieldToSort = Fields to Sort on (e.g. "DESCENDING FirstName, LastName")
Function:
ComboInGridRowColChange used in the DBGrid RowColChange Event gives Combo In Grid functionality. For details study the VBWrapper Sample Project.
Function ComboInGridRowColChange(objDBGrid As Object, objDBCombo As Object,
[iAdjustTop]) As Integer
Member of VBWrapper.OcxWrapper
Returns: Error Number (If not used right)
Parameters:
objDBGrid = DBGrid control
objDBCombo = DBCombo control that is to be displayed in the DBGrid.
iAdjustTop may be used to adjust the top position of the DBCombo to better fit the current Cell of the DBGrid. It may be necessary when Screen.TwipsPerPixelY is 15 or higher.
Function:
SizeGridForm is useful when you need a Form containing a Grid and Command buttons. Call this function in the Resize event of the Form to provide that the Grid and Command buttons is sized perfect related to the size of the Form. See the VBWrapper Sample Project for details.
Function SizeGridForm(objGrid As Object, ParamArray vControls() As Variant) As Integer
Member of VBWrapper.OcxWrapper
Returns: Error Number (If not used right)
Parameters:
objDBGrid = DBGrid, Grid or MSFlexGrid control
vControls = Optional Command buttons followed by position information.
Example: Call SizeGridForm(DBGrid1, Command1(0), "BottomRight", Command1(1), "BottomRight", Command1(2), "BottomLeft", Command1(3), "TopRight", Command1(4), "TopRight")
Function:
SizeGridColumns is used to Size the Columns related to the Width of the Grid.
Function SizeGridColumns(objGrid As Object, bVerticalScrollBar As Boolean,
iVisibleCols As Integer, [vArrayColWidth]) As Integer
Member of VBWrapper.OcxWrapper
Returns: Error Number (If not used right)
Parameters:
objDBGrid = DBGrid, Grid or MSFlexGrid control
bVerticalScrollBar = If True, Makes room for a Vertical Scrollbar.
iVisibleCols = Count of Visible Columns in the Grid
vArrayColWidth = The Columns is sized with values from this array. Example: IF You have a Grid with 4 visible rows and sends vArrayColWidth=array(1000,1500,2000) as a parameter, the 3 first Columns of the Grid is sized to 1000, 1500, 2000 and the fourth column is sized to fill the rest of the available Grid width.
Epost: hnyberg@eunet.no