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

MS Access Recordset and Class Module.

Sorting and Updating the Table Records.

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. 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. 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 in this tutorial is to update the TotalPrice field with the product of Qty * UnitPrice in our Class Module.
The Class Module includes a subroutine that sorts the records on a user-specified column, and the output is listed in the Debug Window.
Another subroutine creates a copy of the Table with a new name for the sorted data.

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 indexes) as parameters to calculate and update the third column with the product of the 'first column' x '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, meaning the first column number is 0, the second column 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 specified as a parameter and creates a new data 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. 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 result will be Column3 = Column1 * Column2. 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

Passing a Two-Dimensional Array Function.

First of all, I have some good news for you: our website, LEARN MS-ACCESS TIPS AND TRICKS, has been selected by the https://blog.feedspot.com panel 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 other sources. Subscribe to blogs, news feeds, or topics in any other area of interest, and receive regular updates from www.blog.feedspot.com in your Inbox as they become available by providing your email address.

Returning to our VBA lessons, last week we briefly discussed passing arrays as parameters to a Function using the ByRef method. This allowed us to work directly with the original array inside the called function and sort its values in descending order.

In our example, we used array values for only five elements, but in practice, an array can hold many rows and columns of data.

The Re-Dimension (ReDim) Statement.

An array can be re-dimensioned multiple times during program execution to increase or decrease the number of rows if its size cannot be determined in advance. In such cases, you should omit the element count in the initial Dim 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 is that the array re-dimensioning should be done in the calling procedure itself—before passing it to the called function if a size change is required. Although arrays are passed by reference and the called function can work directly with the passed variable, it cannot re-dimension the array to increase or decrease its number of elements from within the called function.

Two-Dimensional Array as Function Argument.

Now that we are aware of the limitations of passing arrays to a called function, let’s try passing a two-dimensional array containing Sales data to a function and printing its values in the Debug Window. Each record in the Sales data array will contain the following values:

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

The sales records contain fields with different data types. Normally, to pass these values individually to a called function, we would need four separate array variables with different data types — for example:

String for Product Name
Integer for Quantity
Double for Unit Price
Double for Total Price

Each field’s values would be stored in its own array variable and passed separately to the function.

However, we will do this differently. Instead of using four separate arrays, we will use a single variable: a two-dimensional array of the Variant data type, with four rows (each row representing one sales record) and four columns (each column representing a field). This way, we can pass all four fields of each record together as a single variable to the called function.

The Variant Data Type.

We are not storing the column names anywhere within the array. It is simply assumed that:

The first column holds the Product Name
The second column holds the Quantity
The third column holds the Unit Price
The fourth column holds the Total Price

Since we are using a Variant data type for the array, each array element can hold different data types. A Variant variable automatically adapts its data type to match the type of value assigned to it. This flexibility allows us to store text, integers, and floating-point numbers together in the same array.

Before writing the complete function, let’s first examine how the Dim statement defines the two-dimensional array and how the sales values are assigned to each array element.

' 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 (rows) in our table, and each record contains four fields (columns). Every cell in this two-dimensional array is identified by a pair of numbers—the row index and the column index—separated by a comma. The index number on the left represents the row index, and the number on the right represents the column index. Since our array has 4 rows and 4 columns, both index ranges go from 0 to 3.

Here is how the columns are arranged:

Column 0: Product Name
Column 1: Quantity
Column 2: Unit Price
Column 3: Total Value (to be calculated and assigned later)

We can pass the entire array to a function as a ByRef parameter, allowing the function to work directly with the original data.

If you are new to two-dimensional arrays, it can feel a bit confusing at first to understand how the values are arranged and how to refer to each cell. This becomes even trickier when performing calculations across multiple cells within the same row.

Fortunately, there is a better way to handle this complexity—by using User-Defined Variables. Yes, you can actually define your own variable type, in addition to the built-in types provided by VBA.

We will explore this concept in detail next week. Once you get familiar with it, you’ll find it much easier and more intuitive than juggling rows and columns. The best part is that this method scales effortlessly—you can work with 5 rows, 500 rows, or even 5000 rows using the same statements in your 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, so it can hold values of different data types in each cell.

In the next line, we declare three more variables:

j and k as control variables for the For…Next loops
strIdx as a String variable for building text to display the index numbers of each cell in the InputBox() prompt.

We then set up two nested 'For … Next' loops to control the index row and column numbers. The outer loop (j) controls the row index, while the inner loop (k) controls the column index.

Inside the inner loop, we use a Select Case…End Select structure to determine which field is being processed based on the current value of k:

When k = 0, the InputBox() function prompts for the Product Name and assigns it to Products(j, 0).
When k = 1, it prompts for the Quantity and assigns the value to Products(j, 1).
When k = 2, it prompts for the Unit Price and assigns it to Products(j, 2).
When k = 3, it initializes Products(j, 3) = 0 (this will later hold the calculated Total Price).

The outer loop repeats this process four times (for j = 0 to 3). For each row, the inner loop repeats four times (for k = 0 to 3), collecting input for each element.

The Output ProductPrint () Function.

When the control exits the loop, the ProductPrint() function is called, passing the Products variable as a parameter.

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 receives the Products Array reference (address) through the ByRef method. If the ByVal or ByRef keyword is not explicitly specified before the parameter variable, then ByRef is the default.

As in the earlier program, two integer variables, j and k, are declared as control variables for the outer and inner For…Next loops. These loops are required to navigate the array using its row and column index numbers. The loop starts at 0, and to determine the end value dynamically, we use the Ubound() (Upper Bound) function on the array dimension. In the previous example, we used values 0 to 3, but here we use Ubound() to make the program flexible. This ensures that if the array size changes later through ReDim statements, the loop still calculates the correct number of rows and columns.

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

UBound(Array,1).

The Ubound(List, 1) function returns the number of rows in the array, which is 4. However, array row indexes start from 0 in memory, so the valid index numbers are 0 to 3. The second argument (1) in the Ubound() function specifies that we want the upper bound of the first dimension (rows) of the array. Because the row index starts at 0, we subtract 1 from the total number of rows (4 − 1) when using these indexes in a For…Next loop.

UBound(Array,2).

The UBound(List, 2) function returns the number of columns in the array. The second parameter is optional—if omitted, UBound will return only the upper bound of the first dimension (rows). For single-dimensional arrays, this second parameter is never used.

The statement immediately following the first For…Next loop — List(j, 3) = List(j, 1) * List(j, 2) — calculates the total price of each item and stores it in the rightmost cell. These updated values are then printed in the Debug window during the next 'For… Next' loop, which outputs the complete sales record for each item.

Controlling the Print-Head

Placing a comma at the end of a 'Debug.Print' statement aligns the next item in the 14th column on the same line, following the previously printed item.

An empty 'Debug.Print' statement, placed immediately after the inner Next statement (without a trailing comma), moves the print cursor back to the first column of the next line. This ensures the output for the next sales record begins at the correct position.

If a semicolon (;) is placed at the end of a 'Debug.Print' statement, the print cursor advances to the very next character position, without leaving any space between the printed items.

Next week, we will explore user-defined variables that can hold mixed data types. With them, we can assign meaningful names to each collection element instead of array position, as we did in the examples above. This will make it much easier—and more intuitive—than trying to memorize each array element based solely on its index.

Attachment Field in Access2007

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

Attachment Field in Access 2007.

Working with images or animations in applications like Microsoft Access has always been enjoyable. I have used the Office Assistant for message boxes in all my Access applications, particularly when creating and deploying common VBA library programs across the network.

Not everyone may know that it is possible to display custom images in the Office Assistant control. I have leveraged this feature to display custom greetings to Access users on special occasions such as Christmas, Eid, and Onam simply by replacing the standard company logo image on the server. I was extremely disappointed when Microsoft removed this feature in Office 2007.

For those still using Microsoft Access 2003 or earlier, the following links provide tips and tricks for using the Office Assistant with MsgBox:

The Attachment field type in Access 2007 has significant flexibility, allowing multiple external documents or images to be stored in a single record without inflating the database size. This overcomes the limitations of the older Object Linking and Embedding (OLE) method, which was commonly used in earlier MS Access Versions for storing, editing, or displaying images. While a Hyperlink field can link to only one external file or a single internal object (such as a form or report), the Attachment field supports multiple items per record.

This feature is useful for storing and retrieving essential documents related to a record, such as project site plans, diagrams, contract agreements, engineering drawings, or employees’ family photos. Each attached document or image can be edited in its native application, preserving full functionality.

A Sample Demo.

Open Microsoft Access 2007.
If you have already created the Northwind 2007 sample database, open it; otherwise, select Local Templates from the Template Categories.
Click on the Northwind 2007 Template to select it.
Click the Folder icon on the right side of the File Name control, select the required folder, and save Northwind 2007.accdb Database.
Open Northwind 2007.accdb Database,
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 (field type: Attachment), into view.
Now that we have seen the Attachment Field in the 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:

Right-click to open Large Image in New Tab/Window.
The second column (highlighted) is the attachment field where a paper clip image and a number in brackets (zero) display how many attachments are in each record.
There are no attachments in any of those records so far, so we will add one; double-click the attachment field in 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 to the same field.
Click OK to close the dialog box. You will now see a number appearing in brackets, indicating how many attachments are in that field of the record.
Double-click 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 the earlier dialog box we have seen for attaching /removing/opening external files.