Linked Text File Clean-up Question

I have 4 text files that are linked to a access Db. In each of these files there are dates that have “-” (ex: 2019-01-01). Currently when I pull these text files down I go into each file and do a REPLACE to remove the “-” so all the dates are in YYYYMMDD format. Is there a way to have this done in ACCESS? I’m assuming a make-table query will need to be created for each one?

This Db is a source Db so other Db’s are linked to these tables as well.

submitted by /u/regmeyster
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on

Need UPSERT/MERGE two tables together in Access 2016

I have two tables. I want to update and insert all fields from the source table into target table. For 5 specific fields (all date datatype), I don’t want null fields in the source table to nullify populated data in the target table.

For the updates, I am matching the two tables on two fields (first name & last name). Target table has 349 records and source has 366 records, and 189 match (as counted on Access via query).

Where they don’t match, I want the remaining 177 records from the source table to be appended to the target table (for a total of 526 records).

The target table’s records have all the fields being updated by the source table already present in the table. Of the 37 fields being updated, only 5 fields have any data already populated in the target table. (The target table’s records have 90ish fields.) These 5 need to be overwritten (EXCEPT where the source field is null – in that case, I don’t want to lose the target table’s data). For the other 32 fields, I want it to update (and there are already empty fields in the target table for the source data to go).

I’m totally okay with updating where the names (first name field & last name field) match, and then appending where they don’t (and even doing multiple queries).

Please let me know if I can provide further information to help obtain a solution.

submitted by /u/KookooMoose
[link] [comments]

Posted on

Disable Shortcut menu in Report

Hi all,

So, I’m trying to disable the shortcut menu in a report, in order to use this:

If Button = acRightButton Then DoCmd.RunCommand acCmdFilterMenu End If 

In a form, this is pretty easy. I go to Form Properties -> Other -> Shortcut Menu -> No

But this doesn’t exist in Reports!

Can this be done with VBA?I tried googling it, but haven’t found a solution. 🙁

EDIT:

I’m currently using “DoCmd.RunCommand acCmdFilterMenu” OnDoubleClick event as a workaround, but it’s very un-intuitive when the rest of the database is set up to use rightclick, and now users have to doubleclick in reports.

submitted by /u/daedalus87m
[link] [comments]

Posted on

Need UPSERT/MERGE two tables together in Access 2016

I have two tables. I want to update and insert all fields from the source table into target table. For 5 specific fields (all date datatype), I don’t want null fields in the source table to nullify populated data in the target table.

For the updates, I am matching the two tables on two fields (first name & last name). Target table has 349 records and source has 366 records, and 189 match (as counted on Access via query).

Where they don’t match, I want the remaining 177 records from the source table to be appended to the target table (for a total of 526 records).

The target table’s records have all the fields being updated by the source table already present in the table. Of the 37 fields being updated, only 5 fields have any data already populated in the target table. (The target table’s records have 90ish fields.) These 5 need to be overwritten (EXCEPT where the source field is null – in that case, I don’t want to lose the target table’s data). For the other 32 fields, I want it to update (and there are already empty fields in the target table for the source data to go).

I’m totally okay with updating where the names (first name field & last name field) match, and then appending where they don’t (and even doing multiple queries).

Please let me know if I can provide further information to help obtain a solution.

submitted by /u/KookooMoose
[link] [comments]

Posted on

Access 2016?

At my job, I utilize Access 2003 in a Windows 7 OS as part of a large scale production environment. I need to start migrating to Windows 10 and I noticed that Access 2003 is not supported in Windows 10 and Access 2019 is not supported in Windows 7.

It looks as though Access 2016 is my best bet because it is compatible with both. I did a search for Access 2016 as a standalone and haven’t found anything yet. Does such an option exist? What’s the best ir easiest way to get Access 2016? I will need to have it installed on multiple machines: up to around 30.

submitted by /u/clkou
[link] [comments]

Posted on

Access 2016?

At my job, I utilize Access 2003 in a Windows 7 OS as part of a large scale production environment. I need to start migrating to Windows 10 and I noticed that Access 2003 is not supported in Windows 10 and Access 2019 is not supported in Windows 7.

It looks as though Access 2016 is my best bet because it is compatible with both. I did a search for Access 2016 as a standalone and haven’t found anything yet. Does such an option exist? What’s the best ir easiest way to get Access 2016? I will need to have it installed on multiple machines: up to around 30.

submitted by /u/clkou
[link] [comments]

Posted on

WithEvents in Class Module and Data Entry Form

WithEvents in Class Module and Data Entry Form

So far we have used Unbound Text Boxes on Form for Demo runs, for built-in Event capturing in Class Module Object.  Since, Text Boxes are the main control used on Forms, for data entry/display/view of information, it was the first preferred choice for demonstration of most frequently used built-in Events: AfterUpdate, OnGotFocus and OnLostFocus.

That doesn’t mean that we have ignored Command Buttons, Combo Boxes, List Boxes and others.   All these controls mostly uses the Click Event, to open Form/Report or run macros/sub-routines/functions,  select an item from  Combo box or List Box or Option Button and they are much easier to handle in their Event Procedures in the Class Module.  We will take those controls also along with Text Boxes

Once you are familiar with handling the Text Box based Events alone and understand how it works then other controls will be a simple addition to the whole scheme of things.

Adding Class Module Object instance, one instance for each Text Box on the Form, into the Array element, enabled with required built-in Events itself is somewhat confusing, but not complicated.

If you have not touched the topic of programming the stand alone Class Module I suggest you better start from there.  It will help you a lot and much easier to understand it’s usage in WithEvents, Event and RaiseEvent  programming.  Go through the following Articles to get a general idea of Class Module Programming.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation

Last few weeks we have started with the topic of Class Module and Form Controls Event Programming in Microsoft Access and  learned how to create Class Module Object Array element for each Text Box on Form.

The preparations for creating a Class Module for Text Box object is something like the following steps.

  1. Creates a Class Module with the Text Box (Access.TextBox) Property with Object (or Variable) name like txt.
  2. If the Property is declared as Private then Get/Set Property Procedures are added to manage the txt Object.
  3. Event Handling sub-routines for AfterUpdate, GotFocus and LostFocus are created in the Class Module.

When an instance of the above Class Module is created in memory  it can handle only a single Text Box’s Events (AfterUpdate, GotFocus and LostFocus) on the Form.

If there are more Text Boxes, say three Text Boxes, on the Form then we must create three instance of the same Class Module, one for each Text Box on the Form. 

To keep track of all the three instances of the Class Module they should be organized in an Array  or add them as Collection Object Items, one after the other.

When the same Class Module instance is used for more than one Text Box the Event Procedures (sub-routines) need changes in the Class Module.  We must identify the Text Box that Raised the built-in Event on the Form.  Based on that information run the validation checks or any other action on that particular Text Box within the same sub-routine.

Example: The Code given below is taken from the AfterUpdate() Event Procedure. The FirstName and Designation Text Box’s AfterUpdate Event don’t have any executable Code under them, in the Select Case . . . End Select structure, indicating that these Text Boxes exists on the Form but the AfterUpdate Event are not enabled or used for them.  If there is no executable code in them it is not necessary to add the Text Box name there.  But, adding those fields as part of the testing sequence reminds us their presence on the Form.

But these Text Boxes may be enabled with some other Event like OnLostFocus and handled in the LostFocus Event Procedure.

txtName = Txts.Name

Select Case txtName
Case "LastName" 'TextBox Name
txtval = Trim(Nz(Txts.Value, ""))

If Len(txtval) > 15 Then
msg = "LastName Max 15 chars only."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Left(Nz(txtval, ""), 15)
End If
Case "FirstName"
'
Case "Designation"
'
Case "BirthDate"
db = CDate(Nz(Txts.Value, 0))

If db > Date Then
msg = "Future Date: " & db & " Invalid."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
efrm!Age = Null

ElseIf db > 0 Then
dbage = Int((Date - db) / 365)
efrm!Age = dbage
End If
Case "Age"
Dim xage As Integer
db = CDate(Nz(efrm!BirthDate, 0))
xage = Nz(Txts.Value, 0)

If (db > 0) And (xage > 0) Then
dbage = Int((Date - db) / 365)
If xage <> dbage Then
msg = "Correct Age as per DB = " & dbage
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = dbage
End If
ElseIf (xage = 0) And (db > 0) Then
dbage = Int((Date - db) / 365)
Txts.Value = dbage
End If
Case "JoinDate"
Dim jd As Date

db = CDate(Nz(efrm!BirthDate, 0))
jd = CDate(Nz(Txts.Value, 0))

If (db > 0) And (jd < db) Then
msg = "JoinDate < Birth Date Invalid!"
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
End If
End Select

Note: If you need to Read/Write value from/to some other Text Box, other than the Event trapped Text Box, then you need an Access.Form Property also in the Text Box Class Module: ClsTxtEmployees, to reference the other Text Box to read/write values.

This Class Module can be a Template for other Form’s Text Boxes and customization of sub-routines are also required depending on that particular Form’s Text Box control’s  requirement.

The links of all the earlier Articles on this topic are given below, if you are new to the WithEvents, Events and RaiseEvents usage in User-Defined Events or built-in Event Programming in Microsoft Access, for easy access to those articles.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events

Here, we have a sample Employees Data Entry Form bound to a Table.  If you have gone through the earlier examples with Text Boxes on unbound Form you will not find any difference in this Demo.  The only idea here is to demonstrate the normal usage of Event handling in Class Module Object with the form bound to a Table and the data fields as control source to the Text Box.

The sample image of the Data Entry Form is given below.  An error message shown on the image is popped up (this is manually moved to the bottom right corner) when an invalid JoinDate (less than birth date) is entered into the field.

The Command Button Click on the form is handled normally on the Form Module itself in the first Employee Form Demo.

On the Employee Form demo the Form’s Class Module and a Class Module with two Private Properties, Txts  for Text Box Object  and efrm for Access.Form Object are used.

Insert a new Class Module and change it’s Name Property value from Class1 to ClsTextEmployees.

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

Option Compare Database
Option Explicit

Private efrm As Access.Form
Private WithEvents Txts As Access.TextBox

Public Property Get pfrm() As Access.Form
Set pfrm = efrm
End Property

Public Property Set pfrm(ByRef vNewValue As Access.Form)
Set efrm = vNewValue
End Property

Public Property Get pTxts() As Access.TextBox
Set pTxts = Txts
End Property

Public Property Set pTxts(ByRef vNewValue As Access.TextBox)
Set Txts = vNewValue
End Property

Private Sub Txts_AfterUpdate()
Dim txtName As String, txt As String
Dim msg As String, txtval As Variant
Dim db As Date, dbage As Integer

txtName = Txts.Name

Select Case txtName
Case "LastName"
txtval = Trim(Nz(Txts.Value, ""))

If Len(txtval) > 15 Then
msg = "LastName Max 15 chars only."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Left(Nz(txtval, ""), 15)
End If
Case "FirstName"
'
Case "Designation"
'
Case "BirthDate"
db = CDate(Nz(Txts.Value, 0))

If db > Date Then
msg = "Future Date: " & db & " Invalid."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
efrm!Age = Null

ElseIf db > 0 Then
dbage = Int((Date - db) / 365)
efrm!Age = dbage
End If
Case "Age"
Dim xage As Integer
db = CDate(Nz(efrm!BirthDate, 0))
xage = Nz(Txts.Value, 0)

If (db > 0) And (xage > 0) Then
dbage = Int((Date - db) / 365)
If xage <> dbage Then
msg = "Correct Age as per DB = " & dbage
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = dbage
End If
ElseIf (xage = 0) And (db > 0) Then
dbage = Int((Date - db) / 365)
Txts.Value = dbage
End If
Case "JoinDate"
Dim jd As Date

db = CDate(Nz(efrm!BirthDate, 0))
jd = CDate(Nz(Txts.Value, 0))

If (db > 0) And (jd < db) Then
msg = "JoinDate < Birth Date Invalid!"
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
End If
End Select
End Sub

Private Sub Txts_LostFocus()
Dim txtName As String
Dim msg As String, txtval As Variant

txtName = Txts.Name
txtval = Trim(Nz(Txts.Value, ""))

Select Case txtName
Case "LastName"
'
Case "FirstName"
If Len(txtval) = 0 Then
msg = "FirstName should not be Blank."
MsgBox msg, vbInformation, txtName & "_LostFocus()"
Txts.Value = "XXXXXXXXXX"
End If
Case "Designation"
If Len(txtval) = 0 Then
msg = "Designation Field is Empty."
MsgBox msg, vbInformation, txtName & "_LostFocus()"
Txts.Value = "XXXXXXXXXX"
End If
Case "BirthDate"
'
Case "Age"
'
Case "JoinDate"
'
End Select

End Sub

On the Global area of the module an Access.Form Property efrm declared as Private Property.

Next, an Access.TextBox control is declared, with WithEvents keyword and object name Txts as Private PropertyThe WithEvents keyword enables the Txts Object to capture the programmed Events when triggered on the Form. 

When Class Module Properties are declared as Private, it is accessible only within the Class Module, preventing direct access to the Objects from outside, there must be Get/Set Property Procedures with Public Scope in the Class Module to assign/retrieve values to/from it.

The first Get/Set Property Procedure pairs are for the efrm Access.Form Object.

The next set of Get/Set Property Procedure pairs retrieve/assign TextBox Control in Txts Object.

Any validation checks on the Set Property Procedure parameter can be performed before attempting to assign the value to the Property efrm or Txts.

In the Class Module we are handling only two type of built-in Events, AfterUpdate and LostFocus Events from Text Boxes on the Form.

The AfterUpdate() Event Procedures validates the Values of LastName, BirthDate, Age and JoinDate Fields and displays an appropriate message, mainly to monitor whether the programmed Event is getting captured in the Class Module instance or not.

The LostFocus() Event Procedure validates the FirstName and Designation field values and assigns a default text String “XXXXXXXXXX” to the field, if it is left empty during the Lost Focus Event.

All the Text Box names are listed in the Select Case . . . End Select structure for clarity, some without any executable code in it.  In the LostFocus Event Procedure we have written code under the FirstName and Designation fields.  Lost Focus event is enabled on the Form only for these two data fields.  Other fields are included for clarity or for future use in case if they are needed.

The first Text Box on the Form is an Auto-number field, increments it’s contents automatically and we have no intention to trap any events on this particular control.  Hence, it is not at all taken in the Select Case . . . End Select structure, or enabled any Event on that control either.

The VBA Code behind the Employees Form’s Class Module is given below:

Option Compare Database
Option Explicit

Dim tc As ClstxtEmployee
Dim C As Collection

Private Sub cmdClose_Click()
'Command Button Click Event is handled
'on the Form Module itself, the Event is
'not programmed in Class Module: ClsTxtEmployee
DoCmd.Close
End Sub

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then

Set tc = New ClstxtEmployee

'Form Object is required to read/write values
'from other TextbOX, if needed.
Set tc.pfrm = Me
'assign TextBox control to the Class Module instance's Property
Set tc.pTxts = ctl

Select Case ctl.Name
Case "FirstName", "Designation"
'enable LostFocus Event for FirstName and Designation
tc.pTxts.OnLostFocus = "[Event Procedure]"
Case Else
'enable AfterUpdate for all other Text Boxes
tc.pTxts.AfterUpdate = "[Event Procedure]"
End Select
End If
C.Add tc 'add ClstxtEmployee instance as Collection Object Item
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
'when the form is closed erase Collection Object from memory
Set C = Nothing
End Sub

There is a Demo Database attached at the end of this Page with the sample Forms and Class Modules, you may download and try out them.

After going through them you may try something similar on your own, using the demo database as a reference point so that you can be sure what you have learned so far.

As we did in the earlier cases we will create a Derived Class Object (ClsTxtEmployeeHeader) and transfer above Form Module Code into the new Class Module and leave the Form’s Class Module with the bare minimum of few lines of Code.

We will also transfer the Command Button Click Event handling into the Derived Class Module Object.

The Derived Class Module (ClsTxtEmployeeHeader) VBA Code is given below:

Option Compare Database
Option Explicit

Private tc As ClstxtEmployee
Private Col As New Collection

Private fm As Access.Form
Private WithEvents btn As Access.CommandButton


Public Property Get oFrm() As Access.Form
Set oFrm = fm
End Property

Public Property Set oFrm(ByRef vNewValue As Access.Form)
Set fm = vNewValue
Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In fm.Controls
Select Case TypeName(ctl)
'If TypeName(ctl) = "TextBox" Then
Case "TextBox"
'create a new instance of Class Module Object
Set tc = New ClstxtEmployee
'assign common property values
Set tc.pfrm = fm 'pass Form Employyes object to the new instance
Set tc.pTxts = ctl 'pass text control

'enable required event procedures for Text Boxes
Select Case ctl.Name
'lostfocus event controls
Case "FirstName", "Designation"
tc.pTxts.OnLostFocus = "[Event Procedure]"
Case Else
'after Update Event
tc.pTxts.AfterUpdate = "[Event Procedure]"
End Select
Col.Add tc 'add to the collection object
Case "CommandButton"
Set btn = ctl
btn.OnClick = "[Event Procedure]"
End Select
Next

End Sub

Private Sub btn_Click()
If MsgBox("Close the Form?", vbYesNo, btn.Name & "_Click()") = vbYes Then
DoCmd.Close acForm, fm.Name
End If
End Sub

In the above derived Class Module the first two object declarations for Text Box Class Module and Collection Object we have done on the Employees Form’s Class Module is brought down to this new Class Module.

Next, an Access.Form object fm is declared to hold the Employees form object. This is required to read the date of birth (the BirthDate) Text Box and calculate the Age of the employee and update the value in the Age Text Box on the Form.

Next, a Command Button control Object is declared within the derived Class Module Object with the WithEvents key word to take control of the Command Button click on the Form.

Once the Form Object’s reference is received in the Public Property Set oFrm() as parameter and after assigning it to fm Object  the Class_Init() Subroutine is called to run the Code to enable the AfterUpdate and LostFocus Event, earlier we run from the Form_Load() Event Procedure.

Now the only Code we run from the Form Class Module, on the Form_Load() Event Procedure, is to pass the reference of the current Form Object (Me) to the oFrm() Set Procedure in the derived Class Module Object ClsTxtemployeeHeader.  This Form reference is passed to the ClsTxtEmployee Class Module instance, through the statement  Set tc.pfrm = fm.

The Command Button Click Event Procedure is enabled and captured in the btn_Click() Event Procedure in the Derived Class Module ClsTxtEmployeeHeader itself.

Image of the second sample form, after transferring all it’s Form Module Code into the Derived Class Module ClsTxtEmployeeHeader.

The EmployeeHeader (a copy of the Employees Form) Form’s Class Module VBA Code is given below.

Option Compare Database
Option Explicit

Dim T As New ClsTxtEmployeeHeader

Private Sub Form_Load()
Set T.oFrm = Me
End Sub

The Dim statement instantiates the derived object of ClsTxtEmployeeHeader in memory.

The Form_Load() Event Procedure passes the current form object to the class object T.oFrm() Property Procedure as it’s parameter.

The entire Code written otherwise on the Form Class Module is away and safe in the Class Module(s) and in the Collection Object to execute, when the Form is open. 

When you create another Form with features of similar nature copy the Class Modules and customize it.  This will keep things well organized and easy to implement form rather than writing fresh code on all Form’s Class modules.

Posted on

WithEvents in Class Module and Data Entry Form

WithEvents in Class Module and Data Entry Form

So far we have used Unbound Text Boxes on Form for Demo runs, for built-in Event capturing in Class Module Object.  Since, Text Boxes are the main control used on Forms, for data entry/display/view of information, it was the first preferred choice for demonstration of most frequently used built-in Events: AfterUpdate, OnGotFocus and OnLostFocus.

That doesn’t mean that we have ignored Command Buttons, Combo Boxes, List Boxes and others.   All these controls mostly uses the Click Event, to open Form/Report or run macros/sub-routines/functions,  select an item from  Combo box or List Box or Option Button and they are much easier to handle in their Event Procedures in the Class Module.  We will take those controls also along with Text Boxes

Once you are familiar with handling the Text Box based Events alone and understand how it works then other controls will be a simple addition to the whole scheme of things.

Adding Class Module Object instance, one instance for each Text Box on the Form, into the Array element, enabled with required built-in Events itself is somewhat confusing, but not complicated.

If you have not touched the topic of programming the stand alone Class Module I suggest you better start from there.  It will help you a lot and much easier to understand it’s usage in WithEvents, Event and RaiseEvent  programming.  Go through the following Articles to get a general idea of Class Module Programming.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation

Last few weeks we have started with the topic of Class Module and Form Controls Event Programming in Microsoft Access and  learned how to create Class Module Object Array element for each Text Box on Form.

The preparations for creating a Class Module for Text Box object is something like the following steps.

  1. Creates a Class Module with the Text Box (Access.TextBox) Property with Object (or Variable) name like txt.
  2. If the Property is declared as Private then Get/Set Property Procedures are added to manage the txt Object.
  3. Event Handling sub-routines for AfterUpdate, GotFocus and LostFocus are created in the Class Module.

When an instance of the above Class Module is created in memory  it can handle only a single Text Box’s Events (AfterUpdate, GotFocus and LostFocus) on the Form.

If there are more Text Boxes, say three Text Boxes, on the Form then we must create three instance of the same Class Module, one for each Text Box on the Form. 

To keep track of all the three instances of the Class Module they should be organized in an Array  or add them as Collection Object Items, one after the other.

When the same Class Module instance is used for more than one Text Box the Event Procedures (sub-routines) need changes in the Class Module.  We must identify the Text Box that Raised the built-in Event on the Form.  Based on that information run the validation checks or any other action on that particular Text Box within the same sub-routine.

Example: The Code given below is taken from the AfterUpdate() Event Procedure. The FirstName and Designation Text Box’s AfterUpdate Event don’t have any executable Code under them, in the Select Case . . . End Select structure, indicating that these Text Boxes exists on the Form but the AfterUpdate Event are not enabled or used for them.  If there is no executable code in them it is not necessary to add the Text Box name there.  But, adding those fields as part of the testing sequence reminds us their presence on the Form.

But these Text Boxes may be enabled with some other Event like OnLostFocus and handled in the LostFocus Event Procedure.

txtName = Txts.Name

Select Case txtName
Case "LastName" 'TextBox Name
txtval = Trim(Nz(Txts.Value, ""))

If Len(txtval) > 15 Then
msg = "LastName Max 15 chars only."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Left(Nz(txtval, ""), 15)
End If
Case "FirstName"
'
Case "Designation"
'
Case "BirthDate"
db = CDate(Nz(Txts.Value, 0))

If db > Date Then
msg = "Future Date: " & db & " Invalid."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
efrm!Age = Null

ElseIf db > 0 Then
dbage = Int((Date - db) / 365)
efrm!Age = dbage
End If
Case "Age"
Dim xage As Integer
db = CDate(Nz(efrm!BirthDate, 0))
xage = Nz(Txts.Value, 0)

If (db > 0) And (xage > 0) Then
dbage = Int((Date - db) / 365)
If xage <> dbage Then
msg = "Correct Age as per DB = " & dbage
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = dbage
End If
ElseIf (xage = 0) And (db > 0) Then
dbage = Int((Date - db) / 365)
Txts.Value = dbage
End If
Case "JoinDate"
Dim jd As Date

db = CDate(Nz(efrm!BirthDate, 0))
jd = CDate(Nz(Txts.Value, 0))

If (db > 0) And (jd < db) Then
msg = "JoinDate < Birth Date Invalid!"
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
End If
End Select

Note: If you need to Read/Write value from/to some other Text Box, other than the Event trapped Text Box, then you need an Access.Form Property also in the Text Box Class Module: ClsTxtEmployees, to reference the other Text Box to read/write values.

This Class Module can be a Template for other Form’s Text Boxes and customization of sub-routines are also required depending on that particular Form’s Text Box control’s  requirement.

The links of all the earlier Articles on this topic are given below, if you are new to the WithEvents, Events and RaiseEvents usage in User-Defined Events or built-in Event Programming in Microsoft Access, for easy access to those articles.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events

Here, we have a sample Employees Data Entry Form bound to a Table.  If you have gone through the earlier examples with Text Boxes on unbound Form you will not find any difference in this Demo.  The only idea here is to demonstrate the normal usage of Event handling in Class Module Object with the form bound to a Table and the data fields as control source to the Text Box.

The sample image of the Data Entry Form is given below.  An error message shown on the image is popped up (this is manually moved to the bottom right corner) when an invalid JoinDate (less than birth date) is entered into the field.

The Command Button Click on the form is handled normally on the Form Module itself in the first Employee Form Demo.

On the Employee Form demo the Form’s Class Module and a Class Module with two Private Properties, Txts  for Text Box Object  and efrm for Access.Form Object are used.

Insert a new Class Module and change it’s Name Property value from Class1 to ClsTextEmployees.

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

Option Compare Database
Option Explicit

Private efrm As Access.Form
Private WithEvents Txts As Access.TextBox

Public Property Get pfrm() As Access.Form
Set pfrm = efrm
End Property

Public Property Set pfrm(ByRef vNewValue As Access.Form)
Set efrm = vNewValue
End Property

Public Property Get pTxts() As Access.TextBox
Set pTxts = Txts
End Property

Public Property Set pTxts(ByRef vNewValue As Access.TextBox)
Set Txts = vNewValue
End Property

Private Sub Txts_AfterUpdate()
Dim txtName As String, txt As String
Dim msg As String, txtval As Variant
Dim db As Date, dbage As Integer

txtName = Txts.Name

Select Case txtName
Case "LastName"
txtval = Trim(Nz(Txts.Value, ""))

If Len(txtval) > 15 Then
msg = "LastName Max 15 chars only."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Left(Nz(txtval, ""), 15)
End If
Case "FirstName"
'
Case "Designation"
'
Case "BirthDate"
db = CDate(Nz(Txts.Value, 0))

If db > Date Then
msg = "Future Date: " & db & " Invalid."
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
efrm!Age = Null

ElseIf db > 0 Then
dbage = Int((Date - db) / 365)
efrm!Age = dbage
End If
Case "Age"
Dim xage As Integer
db = CDate(Nz(efrm!BirthDate, 0))
xage = Nz(Txts.Value, 0)

If (db > 0) And (xage > 0) Then
dbage = Int((Date - db) / 365)
If xage <> dbage Then
msg = "Correct Age as per DB = " & dbage
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = dbage
End If
ElseIf (xage = 0) And (db > 0) Then
dbage = Int((Date - db) / 365)
Txts.Value = dbage
End If
Case "JoinDate"
Dim jd As Date

db = CDate(Nz(efrm!BirthDate, 0))
jd = CDate(Nz(Txts.Value, 0))

If (db > 0) And (jd < db) Then
msg = "JoinDate < Birth Date Invalid!"
MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
Txts.Value = Null
End If
End Select
End Sub

Private Sub Txts_LostFocus()
Dim txtName As String
Dim msg As String, txtval As Variant

txtName = Txts.Name
txtval = Trim(Nz(Txts.Value, ""))

Select Case txtName
Case "LastName"
'
Case "FirstName"
If Len(txtval) = 0 Then
msg = "FirstName should not be Blank."
MsgBox msg, vbInformation, txtName & "_LostFocus()"
Txts.Value = "XXXXXXXXXX"
End If
Case "Designation"
If Len(txtval) = 0 Then
msg = "Designation Field is Empty."
MsgBox msg, vbInformation, txtName & "_LostFocus()"
Txts.Value = "XXXXXXXXXX"
End If
Case "BirthDate"
'
Case "Age"
'
Case "JoinDate"
'
End Select

End Sub

On the Global area of the module an Access.Form Property efrm declared as Private Property.

Next, an Access.TextBox control is declared, with WithEvents keyword and object name Txts as Private PropertyThe WithEvents keyword enables the Txts Object to capture the programmed Events when triggered on the Form. 

When Class Module Properties are declared as Private, it is accessible only within the Class Module, preventing direct access to the Objects from outside, there must be Get/Set Property Procedures with Public Scope in the Class Module to assign/retrieve values to/from it.

The first Get/Set Property Procedure pairs are for the efrm Access.Form Object.

The next set of Get/Set Property Procedure pairs retrieve/assign TextBox Control in Txts Object.

Any validation checks on the Set Property Procedure parameter can be performed before attempting to assign the value to the Property efrm or Txts.

In the Class Module we are handling only two type of built-in Events, AfterUpdate and LostFocus Events from Text Boxes on the Form.

The AfterUpdate() Event Procedures validates the Values of LastName, BirthDate, Age and JoinDate Fields and displays an appropriate message, mainly to monitor whether the programmed Event is getting captured in the Class Module instance or not.

The LostFocus() Event Procedure validates the FirstName and Designation field values and assigns a default text String “XXXXXXXXXX” to the field, if it is left empty during the Lost Focus Event.

All the Text Box names are listed in the Select Case . . . End Select structure for clarity, some without any executable code in it.  In the LostFocus Event Procedure we have written code under the FirstName and Designation fields.  Lost Focus event is enabled on the Form only for these two data fields.  Other fields are included for clarity or for future use in case if they are needed.

The first Text Box on the Form is an Auto-number field, increments it’s contents automatically and we have no intention to trap any events on this particular control.  Hence, it is not at all taken in the Select Case . . . End Select structure, or enabled any Event on that control either.

The VBA Code behind the Employees Form’s Class Module is given below:

Option Compare Database
Option Explicit

Dim tc As ClstxtEmployee
Dim C As Collection

Private Sub cmdClose_Click()
'Command Button Click Event is handled
'on the Form Module itself, the Event is
'not programmed in Class Module: ClsTxtEmployee
DoCmd.Close
End Sub

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then

Set tc = New ClstxtEmployee

'Form Object is required to read/write values
'from other TextbOX, if needed.
Set tc.pfrm = Me
'assign TextBox control to the Class Module instance's Property
Set tc.pTxts = ctl

Select Case ctl.Name
Case "FirstName", "Designation"
'enable LostFocus Event for FirstName and Designation
tc.pTxts.OnLostFocus = "[Event Procedure]"
Case Else
'enable AfterUpdate for all other Text Boxes
tc.pTxts.AfterUpdate = "[Event Procedure]"
End Select
End If
C.Add tc 'add ClstxtEmployee instance as Collection Object Item
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
'when the form is closed erase Collection Object from memory
Set C = Nothing
End Sub

There is a Demo Database attached at the end of this Page with the sample Forms and Class Modules, you may download and try out them.

After going through them you may try something similar on your own, using the demo database as a reference point so that you can be sure what you have learned so far.

As we did in the earlier cases we will create a Derived Class Object (ClsTxtEmployeeHeader) and transfer above Form Module Code into the new Class Module and leave the Form’s Class Module with the bare minimum of few lines of Code.

We will also transfer the Command Button Click Event handling into the Derived Class Module Object.

The Derived Class Module (ClsTxtEmployeeHeader) VBA Code is given below:

Option Compare Database
Option Explicit

Private tc As ClstxtEmployee
Private Col As New Collection

Private fm As Access.Form
Private WithEvents btn As Access.CommandButton


Public Property Get oFrm() As Access.Form
Set oFrm = fm
End Property

Public Property Set oFrm(ByRef vNewValue As Access.Form)
Set fm = vNewValue
Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In fm.Controls
Select Case TypeName(ctl)
'If TypeName(ctl) = "TextBox" Then
Case "TextBox"
'create a new instance of Class Module Object
Set tc = New ClstxtEmployee
'assign common property values
Set tc.pfrm = fm 'pass Form Employyes object to the new instance
Set tc.pTxts = ctl 'pass text control

'enable required event procedures for Text Boxes
Select Case ctl.Name
'lostfocus event controls
Case "FirstName", "Designation"
tc.pTxts.OnLostFocus = "[Event Procedure]"
Case Else
'after Update Event
tc.pTxts.AfterUpdate = "[Event Procedure]"
End Select
Col.Add tc 'add to the collection object
Case "CommandButton"
Set btn = ctl
btn.OnClick = "[Event Procedure]"
End Select
Next

End Sub

Private Sub btn_Click()
If MsgBox("Close the Form?", vbYesNo, btn.Name & "_Click()") = vbYes Then
DoCmd.Close acForm, fm.Name
End If
End Sub

In the above derived Class Module the first two object declarations for Text Box Class Module and Collection Object we have done on the Employees Form’s Class Module is brought down to this new Class Module.

Next, an Access.Form object fm is declared to hold the Employees form object. This is required to read the date of birth (the BirthDate) Text Box and calculate the Age of the employee and update the value in the Age Text Box on the Form.

Next, a Command Button control Object is declared within the derived Class Module Object with the WithEvents key word to take control of the Command Button click on the Form.

Once the Form Object’s reference is received in the Public Property Set oFrm() as parameter and after assigning it to fm Object  the Class_Init() Subroutine is called to run the Code to enable the AfterUpdate and LostFocus Event, earlier we run from the Form_Load() Event Procedure.

Now the only Code we run from the Form Class Module, on the Form_Load() Event Procedure, is to pass the reference of the current Form Object (Me) to the oFrm() Set Procedure in the derived Class Module Object ClsTxtemployeeHeader.  This Form reference is passed to the ClsTxtEmployee Class Module instance, through the statement  Set tc.pfrm = fm.

The Command Button Click Event Procedure is enabled and captured in the btn_Click() Event Procedure in the Derived Class Module ClsTxtEmployeeHeader itself.

Image of the second sample form, after transferring all it’s Form Module Code into the Derived Class Module ClsTxtEmployeeHeader.

The EmployeeHeader (a copy of the Employees Form) Form’s Class Module VBA Code is given below.

Option Compare Database
Option Explicit

Dim T As New ClsTxtEmployeeHeader

Private Sub Form_Load()
Set T.oFrm = Me
End Sub

The Dim statement instantiates the derived object of ClsTxtEmployeeHeader in memory.

The Form_Load() Event Procedure passes the current form object to the class object T.oFrm() Property Procedure as it’s parameter.

The entire Code written otherwise on the Form Class Module is away and safe in the Class Module(s) and in the Collection Object to execute, when the Form is open. 

When you create another Form with features of similar nature copy the Class Modules and customize it.  This will keep things well organized and easy to implement form rather than writing fresh code on all Form’s Class modules.

Posted on