LEARN MS-ACCESS TIPS AND TRICKS: Field Type

Showing posts with label Field Type. Show all posts

MS-Access Recordset and Class Module

a.p.r. pillaiNovember 25, 2018Class Module, Data Type, Field Type, Recordset No comments

Introduction.

In this section, we will create a Class Module designed for data processing tasks. An DAO.Recordset object will be passed to the custom class object. Since we are passing an object to our custom class, we need to implement a pair of Property Set and Property Get procedures to assign the object to the class and to retrieve the object or its property values when required.

We have a small Table: Table 1, with a few records on it. Here is the image of Table 1.

Right-click to open Large Image in New Tab/Window.

The table above has only four fields: Description, Quantity, Unit Price, and Total Price. The Total Price field is empty.

One of the tasks of our Class Module is updating the TotalPrice field with the product of Qty * UnitPrice.
The Class Module includes a subroutine that sorts the data based on a user-specified field and outputs the sorted listing to the Debug Window.
Another subroutine creates a copy of the Table with a new name, after sorting the data based on the column number provided as a parameter.

ClsRecUpdate Class Module.

Open your Access Database and open the VBA Window.
Insert a Class Module.
Change the Name Property Value to ClsRecUpdate.

Copy and Paste the following Code into the Class Module and save the Module:

Option Compare Database
Option Explicit

Private rstB As DAO.Recordset

Public Property Get REC() As DAO.Recordset
   Set REC = rstB
End Property

Public Property Set REC(ByRef oNewValue As DAO.Recordset)
If Not oNewValue Is Nothing Then
   Set rstB = oNewValue
End If
End Property

Public Sub Update(ByVal Source1Col As Integer, ByVal Source2Col As Integer, ByVal updtcol As Integer)
'Updates a Column with the product of two other columns
Dim col As Integer

col = rstB.Fields.Count

'Validate Column Parameters
If Source1Col > col Or Source2Col > col Or updtcol > col Then
    MsgBox "One or more Column Number(s) out of bound!", vbExclamation, "Update()"
    Exit Sub
End If

'Update Field
On Error GoTo Update_Err
rstB.MoveFirst
Do While Not rstB.EOF
   rstB.Edit
     With rstB
      .Fields(updtcol).Value = .Fields(Source1Col).Value * .Fields(Source2Col).Value
      .Update
      .MoveNext
     End With
Loop

Update_Exit:
rstB.MoveFirst
Exit Sub

Update_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "Update()"
Resume Update_Exit
End Sub

Public Sub DataSort(ByVal intCol As Integer)
Dim cols As Long, colType
Dim colnames() As String
Dim k As Long, colmLimit As Integer
Dim strTable As String, strSortCol As String
Dim strSQL As String
Dim db As Database, rst2 As DAO.Recordset

On Error GoTo DataSort_Err

cols = rstB.Fields.Count - 1
strTable = rstB.Name
strSortCol = rstB.Fields(intCol).Name

'Validate Sort Column Data Type
colType = rstB.Fields(intCol).Type
Select Case colType
    Case 3 To 7, 10
        strSQL = "SELECT " & strTable & ".* FROM " & strTable & " ORDER BY " & strTable & ".[" & strSortCol & "];"
        Debug.Print "Sorted on " & rstB.Fields(intCol).Name & " Ascending Order"

    Case Else
        strSQL = "SELECT " & strTable & ".* FROM " & strTable & ";"

        Debug.Print "// SORT: COLUMN: <<" & strSortCol & " Data Type Invalid>> Valid Type: String,Number & Currency //"
        Debug.Print "Data Output in Unsorted Order"
End Select

Set db = CurrentDb
Set rst2 = db.OpenRecordset(strSQL)

ReDim colnames(0 To cols) As String

'Save Field Names in Array to Print Heading
For k = 0 To cols
   colnames(k) = rst2.Fields(k).Name
Next

'Print Section
Debug.Print String(52, "-")

'Print Column Names as heading
If cols > 4 Then
   colmLimit = 4
Else
   colmLimit = cols
End If
For k = 0 To colmLimit
    Debug.Print colnames(k),
Next: Debug.Print
Debug.Print String(52, "-")

'Print records in Debug window
rst2.MoveFirst
Do While Not rst2.EOF
  For k = 0 To colmLimit 'Listing limited to 5 columns only
     Debug.Print rst2.Fields(k),
  Next k: Debug.Print
rst2.MoveNext
Loop

rst2.Close
Set rst2 = Nothing
Set db = Nothing

DataSort_Exit:
Exit Sub

DataSort_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "DataSort()"
Resume DataSort_Exit

End Sub

Public Sub TblCreate(Optional SortCol As Integer = 0)
Dim dba As DAO.Database, tmp() As Variant
Dim tbldef As DAO.TableDef
Dim fld As DAO.Field, idx As DAO.Index
Dim rst2 As DAO.Recordset, i As Integer, fldcount As Integer
Dim strTable As String, rows As Long, cols As Long

On Error Resume Next

strTable = rstB.Name & "_2"
Set dba = CurrentDb

On Error Resume Next
TryAgain:
Set rst2 = dba.OpenRecordset(strTable)
If Err > 0 Then
  Set tbldef = dba.CreateTableDef(strTable)
  Resume Continue
Else
  rst2.Close
  dba.TableDefs.Delete strTable
  dba.TableDefs.Refresh
  GoTo TryAgain
End If
Continue:
On Error GoTo TblCreate_Err

fldcount = rstB.Fields.Count - 1
ReDim tmp(0 To fldcount, 0 To 1) As Variant

'Save Source File Field Names and Data Type
For i = 0 To fldcount
    tmp(i, 0) = rstB.Fields(i).Name: tmp(i, 1) = rstB.Fields(i).Type
Next
'Create Fields and Index for new table
For i = 0 To fldcount
   tbldef.Fields.Append tbldef.CreateField(tmp(i, 0), tmp(i, 1))
Next
'Create index to sort data
Set idx = tbldef.CreateIndex("NewIndex")
With idx
   .Fields.Append .CreateField(tmp(SortCol, 0))
End With
'Add Tabledef and index to database
tbldef.Indexes.Append idx
dba.TableDefs.Append tbldef
dba.TableDefs.Refresh

'Add records to the new table
Set rst2 = dba.OpenRecordset(strTable, dbOpenTable)
rstB.MoveFirst 'reset to the first record
Do While Not rstB.EOF
   rst2.AddNew 'create record in new table
    For i = 0 To fldcount
        rst2.Fields(i).Value = rstB.Fields(i).Value
    Next
   rst2.Update
rstB.MoveNext 'move to next record
Loop
rstB.MoveFirst 'reset record pointer to the first record
rst2.Close

Set rst2 = Nothing
Set tbldef = Nothing
Set dba = Nothing

MsgBox "Sorted Data Saved in " & strTable

TblCreate_Exit:
Exit Sub

TblCreate_Err:
MsgBox Err & " : " & Err.Description, vbExclamation, "TblCreate()"
Resume TblCreate_Exit

End Sub

The 'rstB' Property is declared as a DAO.Recordset Object.

Through the Set Property Procedure, a Recordset object can be passed to the ClsRecUpdate Class Object.

The Update() Subroutine accepts three-column numbers (0-based column numbers) as parameters to calculate and update the third parameter column with the product of the first column and * second column.

The DataSort() subroutine sorts the records in ascending order based on the Column Number passed as a parameter.

The Sorting Column data type must be either Number, Currency, or String. Other data types are ignored. The Recordset column numbers are 0-based, which means the first column number is 0, the second column is 1, and so on.

A listing of the records will be displayed in the Debug Window. The output will be limited to the first five fields; if the record source contains more than five fields, the remaining fields will be ignored.

The TblCreate() Subroutine sorts the data based on the column number provided as a parameter and creates a new table with a modified name. The parameter is optional; if no column number is specified, the data will be sorted by the first column (provided its data type is valid). The new table will retain the original table name with “_2” appended to it. For example, if the source table is namedTable1 The newly created table will be named Table1_2.

The Test Program for ClsUpdate.

Let us test the ClsRecUpdate Class Object with a small Program.

The test program code is given below:

Public Sub DataProcess()
Dim db As DAO.Database
Dim rstA As DAO.Recordset

Dim R_Set As ClsRecUpdate
Set R_Set = New ClsRecUpdate

Set db = CurrentDb
Set rstA = db.OpenRecordset("Table1", dbOpenTable)

'send Recordset Object to Class Object
Set R_Set.REC = rstA

'Update Total Price Field
Call R_Set.Update(1, 2, 3) 'col3=col1 * col2

'Sort Ascending Order on UnitPrice column & Print in Debug Window
Call R_Set.DataSort(2)

'Create New Table Sorted on UnitPrice in Ascending Order
Call R_Set.TblCreate(2) 
Set rstA = Nothing
Set db = Nothing
xyz:
End Sub

You may pass any Recordset to test the Class Object.

You can specify any column numbers when updating a particular column; they do not need to be consecutive. The third column number parameter identifies the target column to be updated. The values from the first column parameter are multiplied by the values from the second column parameter, and the resulting value is written to the target column. You may modify the Class Module code to perform any other operation on the table as needed.

List of All the Links on this Topic.

Passing Two Dimensional Array to Function

a.p.r. pillaiAugust 02, 2018Array, Calculation, Data Type, Field Type, Fields, Functions 8 comments

Introduction.

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS, is selected by https://blog.feedspot.com panelist as one of the Top 5 Microsoft Access Blogs on the Web and awarded the Badge given below.

You can find some of the top-ranked Blogs on a variety of subjects, RSS Feeds, YouTube Channels, top News Websites, and others. Subscribe to Blogs/News Feeds, or topics of any other area of interest, and get regular updates from www.blog.feedspot.com to your Inbox, as and when it happens, by providing your E-mail address.

Coming back to our VBA lessons, last week we have briefly touched on the topic of passing Arrays as Parameter to Function ByRef method. We were able to work with the singly dimensioned Array in its original location within the called function, to sort the values in descending order. For this example, we have loaded the array with values for only five elements, but the array can have many rows and columns of values.

The Re-Dimension (ReDim) Statement.

An Array can re-dimension, for more rows or fewer rows later on in the program more than once, if we cannot determine the length of the array in advance. In that case, you should not specify the number of elements in advance in the initial Dimension Statement.

Example:

'Cannot Re-dimension pre-defined Arrays
.
Dim Products(1 to 5) as String
.
or
.
Dim Products(5) as String'The number of elements are predefined

'Re-dimension this Array later for required  
'Number of elements, not known in advance. 
.
Dim Products() as String
.
'Re-Dimension the Array for required number of elements
'Remember the array index numbers will be 0 to 4, total 5 elements
ReDim Products(5) As String
'
'OR
'In this case Array Index Number Range 1 to 5
ReDim Product(1 to 5) As String

'later on in the program
'all the values assigned to first 5 elements will be lost.
.
ReDim Products(7) As String 
.
or
.
ReDim Products(Ubound(Products)+2) As String
.
'To preserve the values already assigned to first 5 elements
.
ReDim Preserve Products(7) As String

Note:

The important point to note here is that the ReDimension should take place in the calling program itself, if the need arises in the called function, before passing the Array to the called program. Even though the Array is passed to the called function ByRef and we are able to work with the passed variable directly, we cannot re-dimension the array to increase/decrease the number of elements in the array, from within the called function.

Two-Dimension Array as Function Argument.

Now, that we are aware of the limitation of the array when passed to the called function, we will try to pass a two-dimensional array of Sales data to a function and print the values in the Debug Window. The Sales data array has the following values in a record:

Product Name - Text
Quantity - Integer
Unit Price – Double
Total Value - Double (will be calculated in the called function)

The sales record shows that the data fields have different data types. To pass values for each field separately to the called function, we need four different array Variables with different data types (1. The String data type for Product-Name, 2. Integer for Quantity, 3. Unit Price & 4. Total Price with Double precision number) and load each field data into separate Array Variables.

We are going to do it differently here. We will be using only one Variable to pass all four field values to the called function. We will define a single Variable as a two-dimensional Array of Variant data types, with four rows (each row represents a single record) and four columns (each column is a field).

The Variant Data Type.

We are not storing the above column names anywhere in the array and it is assumed that the first column is Product name, the next column to the right of the first one is Quantity, the next column is Unit Price and the last column is Total Price. Since the Variant Data Type variable has a peculiar behavior, the data type of the cell changes automatically to match the data type assigned to it.

Before writing it as a complete function, we will look at the dimension statement and how the sales values are assigned to each element of the array.

' variant Variable can hold different data types in each element 
Dim Product(4,4) as Variant 
'
Product(0, 0) = "Hard Disk": Product(0, 1) = 5: Product(0, 2) = 125.5: Product(0, 3) = 0

Product(1, 0) = "Keyboard": Product(1, 1) = 2: Product(1, 2) = 25.25: Product(1, 3) = 0

Product(2, 0) = "Mouse": Product(2, 1) = 3: Product(2, 2) = 13.75: Product(2, 3) = 0

Product(3, 0) = "DVD Writer": Product(3, 1) = 10: Product(3, 2) = 30: Product(3, 3) = 0

In the above example, we have only four records (or four rows or lines of data) in the Table. There are four fields (four columns) in each record. Each cell is numbered with two numbers (row index number, column index number), separated by a comma. The left-side number is the row index number of the column and the number to the right side of the comma is the column index number. Both number range is 0 to 3 (4 rows and 4 columns). The first column (column index 0) is Product Name, 2nd Column (column index 1) Quantity, 3rd Column (index number 2) Unit Price and the last one is (index number 3) Total Value, which will be calculated and assigned later.

The entire array of these values can be passed to a function as the ByRef parameter and we can work with the array directly from within the called function. If you are new to two-dimensional arrays, it will be a little confusing at first to comprehend the arrangement of values and how to address each cell to work with it. This becomes more difficult when there are calculations involving cells of the same row.

We have a better way to deal with this problem with User-Defined Variables. Yes, you heard me correctly, we can define our own Variable Type, besides the built-in variables with default data types. We will explore this topic further next week and I am sure you will be happier with this new idea, after struggling with these rows and columns set up. Believe me, this is a very powerful feature once you are familiar with these kinds of data arrangements. You can work with 5 rows, 500 rows, or 5000 rows with the same statements in the function.

Create the Product List Data.

Public Function ProdList()
Dim Products(4, 4) As Variant
Dim j As Integer, k As Integer, stridx As String
' 0 = Description
' 1 = Quantity
' 2 = Unit Price
' 3 = Total Price to be calculated
'Array elements index numbers are 0 to 3
For j = 0 To 3
 For k = 0 To 3
    stridx = "(" & j & "," & k & ")"
    Select Case k
        Case 0
          Products(j, k) = InputBox("Product Name" & stridx)
        Case 1
          Products(j, k) = InputBox("Quantity" & stridx)
        Case 2
          Products(j, k) = InputBox("Unit Price" & stridx)
        Case 3
          Products(j, k) = 0 'total value will be calculated
    End Select
    Next k
Next j

Call ProdPrint(Products)

End Function

VBA Code Line by Line

We have defined the Products variable as a Variant data type with 4 rows, and 4 columns for assigning values of different data types in them. The next line sets up three more variables: j & k as a control variable for For … Next loops, the variable stridx for building a string to display the index numbers of cells when displayed in the InputBox() function Prompt text.

Two nested For … Next loops are set up to control the Variable index numbers of rows and column values. The outer loop controls the row number and the inner loop with the k control variable is used for the column index number.

Next, we used the Select Case ... End Select statements to run several other statements depending on the value in the control variable j. If the value in variable k=0 (and j=0) then the Inputbox() function runs below the Case 0 tests and gets the Product Name and assigns it to the Products(0,0) cell. When k=1 then the InputBox() gets the value of the Quantity and assigns it to the Products(0,1) cell. When k=2 gets Unit Price and in the next step assigns Products(0,3)=0. The outer loop with the control variable runs only once with zero value as the row index number.

This action repeats 3 more times for the outer For…Next loop to control the row index number and each time the inner For … Next loop runs four times to control the column numbers to get values from the User for each cell for the row number in the j control variable.

The Output Function ProdPrint().

When control comes out of the loop the ProductPrint() Function is called by passing the Products variable as a parameter to the function.

Public Function ProdPrint(List As Variant)
Dim j As Integer, k As Integer

'Ubound() function will get the
'total rows in the array - first value in the Dim statement
For j = 0 To UBound(List, 1) - 1
      List(j, 3) = List(j, 1) * List(j, 2)
    For k = 0 To UBound(List, 2) - 1 'get second value in Dim statement
        Debug.Print List(j, k),
    Next k: Debug.Print
Next j

End Function

The ProductPrint() function takes the Products Array's location address (ByRef) method. If you omit the ByVal or ByRef keyword before the Parameter variable it assumes that Variable List holds the location reference of the Products (parameter passed ByRef).

As in the earlier program, two integer variables j & k are defined as control variables for outer and inner For … Next loops. We need these For … Next loops to control the index numbers (rows & columns) to access each element of the array. The starting value of the Loop is 0 but to calculate the end value we have used another function Ubound() (get Upper Boundary) value of the Array dimension. In the first program, we have written the control value as 0 to 3. Here also we could do that, but here we have used the Ubound() function to find the row and column numbers. This will calculate the Array size correctly if the Array size is changed through ReDim statements.

Usage UBound() Function to get Two-Dimension Index Numbers.

UBound(Array,1)

The Ubound(List, 1) gets the number of rows to value, which is 4. But the row index numbers start from 0 in memory so we have used index numbers 0 to 3 in the For … Next loop. The second value 1 in the bracket of the Ubound() function asks for the number of rows in the array. Since the row index number starts from 0 we are subtracting 1 from the number of row values (4-1).

UBound(Array,2)

The UBound(List, 2) gets the number of column values. The second parameter value is optional, if it is omitted, it will only get the row value. If the variable is a singly dimensioned array, then the second value is never used.

The statement immediately after the first For … Next loop ‘List(j, 3) = List(j, 1) * List(j, 2)’ calculates the Total Price of each item and assigns it to the rightmost cell before printing the values of the Sales record item within the next For…Next loop, on the debug window.

Controlling the Print-Head

The comma at the end of the Debug.Print statement positions the next item in the 14th column on the same line, after printing the earlier item.

The empty Debug.Print statement, immediately after the inner Next statement without a comma at the end brings the print control back to the first column of the next line, positions correctly to start printing the next Sales Record.

If we place a semi-colon (;) at the end of the Debug.Print statement the print-head positions to the next character position, without leaving any space between the items printed.

Next week we will explore the User-Defined Variable with the mixed data type. We can give appropriate names for each element of the array rather than assuming names as we did in the above examples. I am sure it will be very interesting to compare the difficulty we had in memorizing each array element's logical name according to its array position.

Attachment Field in Access2007

a.p.r. pillaiFebruary 28, 2011Attachment, External Links, Field Type 1 comment

Working with images or animations in Applications like MS-Access was always fun. I have used Office Assistant for Message Boxes in all my Microsoft Access Applications by creating and installing common VBA library programs on the network.

I don't know how many of you know that you can display your own custom images in the Office Assistant control. I have used this feature to display custom-made greetings to my MS-Access Application Users on special occasions like Christmas, Eid, Onam, etc., by simply replacing a company logo image (used for normal display in the message box) on the Server. I was totally disappointed when Microsoft discarded this feature from Microsoft Office 2007.

Those who are still using Microsoft Access2003 or earlier versions can check the following links for tricks with Office Assistant for MsgBox:

The Attachment Field type in Access2007 gives much-needed flexibility in storing several external documents or images in one record without inflating the database size, as it does with the Object Linking and Embedding (OLE) method widely used in the earlier version of Access for storing/editing/displaying images. Hyperlinks in a data field can be used for linking only one external file or one internal object like Form or Report.

Attachment Field gives the much-needed advantage in storing and retrieving essential documents like Project site plans, diagrams, Contract Agreements, Engineering Drawings, employees’ family photos, or whatever related to a particular record in the database. The attached document or image can be edited in its own native Application.

A Sample Demo.

Open Microsoft Access2007.
If you have already created the Northwind 2007.accdb sample database, open it; otherwise, select Local Templates from the Template Categories.
Click on the Northwind 2007 Template to select it.
Click on the Folder icon on the right side of the File Name control and select the required folder to save the Northwind 2007.accdb database.
Click on the Create Command Button to create the sample database and open it.
Close the Home Form.
Select Object Type from the drop-down list in the Navigation Pane and select Tables.
Right-click on the Employees table and select Design View from the Shortcut Menu.
Use the right scroll bar to move the field list up and bring the last field Attachments, with field type Attachment, into view.
Now that we have seen the Attachment Field in Employees Table (or you can create a new Table with the Attachment Field if you prefer) close the design view.
Open the Employees Table in Datasheet View.
Move the horizontal scroll bar and bring the attachment field into view, see the sample image shown below:
The second column (highlighted) is the attachment field where a paper clip image and a number in brackets (zero) show how many attachments are there in each record.
There are no attachments added in any of those records so far, so we will do that; double-click in the attachment field of the first record.
The Attachment control opens up. Click on the Add… Command Button to browse for files on the hard disk. You may select a Word Document, Excel File, PDF file, or Image.
Repeat this action to attach more files in the same field.
Click OK to close the dialog box. You will now see a number appearing in brackets, indicating how many attachments are there in that field of the record.
Double-click on the attachment field to open and show the attached files.
Click on one of the files to select it.
If you click on the Remove Command Button you can remove the selected attachment or click Open to open the document in its parent/preview Application.
If you right-click the attachment field the Manage Attachment shortcut menu is displayed. Selecting this option will open up the earlier dialog box we have seen for attaching /removing/opening external files.