Shifting Focus from one sub-form to the other.

Introduction.

Sample Form with two Sub-Forms:

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

The Focus movement Requirement

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

Last week we saw how to set focus to a particular field in a sub-form from the main form field.  I have not mentioned anything about the relationships between these three forms in the earlier example.  But, here it is important to know before we attempt to leave Focus from a record in the first sub-form and set focus to its corresponding record in the second sub-form.

Two related Sub-Forms and two issues to solve.

1. Two related sub-forms need to link together, on the common Main Form. The first sub-form is directly linked to the main form.
2. Setting focus on the Amount text box on the second sub-form, when Tab-key press transfers focus from the last text box (the School-Year) on the first sub-form.

Assume that the current record on the first sub-form (frm_Session) is the Session ID (Primary Key) field value 1.  Since the first sub-form is directly linked to the second sub-form (we will explore this aspect, i.e., establishing direct links between two sub-forms, a little later on this page) frm_Payments, the payment record with the SessionId (Foreign Key) field value 1 is displayed on that form too.  We want to transfer focus from the first sub-form Session record to its corresponding Payment record Amount field in the second sub-form. 

Normally, when you press Tab-Key on the last field of the current record, the insertion point moves to the first field of the next record on the same form (with SessionID value 2). Automatically the Payment record, linked to the Session record, also changes to synchronize with the Session ID value 2, because it is directly linked with the frm_session.  So the control is lost from the first record with SessionID field value 1 and we could not enter the Payment Value in the Amount field of the frm_Payment, for the same SessionID. We don't want this to happen, instead, we want the focus to move on to the frm_Payment's Amount field, to enter the payment value of SessionID 1. If you could not make out the total picture of the problem, then check the second image diagram.

So, the question is how to keep the Focus on the first record itself, when the focus is lost from the last field, and transfer the Focus to its corresponding record's Amount field on the second subform?

Linking Both Sub-Forms Together.

Before going into that, let us learn how to link both sub-forms directly, to synchronize related records on both subforms.

The first sub-form (frm_Session) is directly linked with the Main Form (frm_Students) on the common field Student ID (Primary Key) field, on Students Table, and Student ID (Foreign Key) on Session Table.

The current student record on the main form can have several session records on the frm_Session Form.  Each Session record on the Session sub-form will have one or more Payment records on the frm_Payment sub-form and have SessionID as Foreign Key.

The frm_Payments sub-form is directly linked to the frm_Session sub-form of the common SessionID field value. 

Linking Both Sub-Forms Together.

Two sub-forms cannot be linked together directly because a sub-form cannot be considered a Master Form by another Sub-Form.  When this kind of link (relationships) becomes necessary, like the above sub-forms, we can do it by simply transferring the first sub-form Key-field value in an Unbound Textbox on the Main form, and this Unbound Textbox that holds the SessionID value becomes part of the Main Form.  The Name of this Textbox can be used in the Link Master Field property of the second sub-form to establish a relationship with the first sub-form.

Check the following Design View of the above Forms:

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

You can see an Unbound TextBox with the yellow background, created specifically for linking the second sub-form (frm_Payments) to the first sub-form (frm_Session) through the Unbound Textbox on the frm_Students main Form.  This Textbox Name Property value is set as Session_ID, somewhat different than the actual field names in the sub-forms: SessionID. The child Label Caption I have changed to Session_ID for information.

The Session_ID TextBox’s Control Source Property is set with the following expression, to copy the SessionID value automatically from the current record on the frm_Session sub-form:

=[frm_Session].Form!SessionID

Once this is done, you can link the frm_Payments sub-form with the frm_Session sub-form through the Session_ID Texbox by setting the Link Master Field and Link Child Field properties of the frm_Payments sub-form, as shown below.

Link Master Field = Session_ID (the unbound Textbox name)
Link Child Field = SessionID  (the Payments Form’s Foreign Key field name)

Tackling the Real Problem

If you understood the subform's relationships then we can proceed with the real issue I have pointed out at the beginning of this Article.  We must be able to transfer control from the last field of a record in the first sub-form to its corresponding record in the second sub-form, without changing the current record on the first sub-form.  We need a small VBA program to do that job successfully.

Tip: You can try this out with three simple tables (Students, Session & Payments) with sample fields as shown on the Forms.  You may download a sample database, with the sample tables and forms, from the download link given at the bottom of this Article.

If you have the sample Tables and Forms organized as per the design shown above, you may open the frm_Students in normal view to do a sample run of what we are trying to achieve, without the VBA Code.

Tip:  If you have downloaded the sample database from the link given at the bottom of this article, then open the frm_Session in the design view, press F4 to display the Property Sheet, and Click on the School Year Field.  You will find [Event Procedure] in the On Got Focus event property.  Click on this property and click on the Build (. . .) Button to open the VBA Module.  Highlight and delete the entire code, except the first two lines: Option Compare Database & Option Explicit.  Save and close the form. You can copy and paste the deleted code from this page.

1. Open the frm_Students in Normal View.

2. Click on the first record on the frm_Session form to select the record with SessionID value 1.

3. Check the presence of the related record on the frm_Payments sub-form, with the Session ID value 1.

4. Press Tab Key to move the control to the last field School Year. 

   Note:  What we want at this point is, when you press the Tab Key one more time the control should jump from the current record on the frm_Session sub-form and set focus on its corresponding record with SessionID value 1, on the frm_Payments sub-form. 

5. Now, press Tab Key to move out of the last data Field - School Year from frm_Session to go to Amount Field of frm_Payments.

But, it didn’t happen as we have expected, instead, the cursor moved down to the next record on the frm_Session, with SessionID value 2.  The related records on the frm_Payments also changed to Foreign-Key SessionID value 2 to match with the record on frm_Session.

We can do this task only with the help of the VBA Code.  The steps of our program are given below:

1. When the Focus arrives on the School Year field on frm_Session, save the SessionID value into a memory variable SID.

2. When the Focus is Lost (i.e., when the user presses Tab Key again) from the School Year, the Focus moves to the next record and at this point, the VBA Code searches through the form’s RecordsetClone for the SessionID value in memory Variable SID.

3. When the record is found then the RecordsetClone Bookmark is saved into the bkmk String Variable.

4. Copy the record set Bookmark into the frm_Session’s Bookmark control. 

   These steps reset the focus back to the previous record, from the second record, on the frm_Session sub-form. The frm_Payment record earlier changed, and before executing the code,  returns to the one matching the SessionID on the frm_Session record.

5. Set Focus on the frm_Payments.

   When this happens the frm_Payments field with Tab Index value 0 receives the Focus.  At this point, we can move the Focus to any other field, if needed.  We will try this by setting the focus on the Amount field.

6. Set focus on the Amount field on frm_Payments.

If you have the above Form ready, then copy and paste the VBA Code given below into the frm_Session’s VBA Module.

1. Open the frm_Session form in the design view.

2. Click on the School Year field to select it.

3. Press F4 to display the Property Sheet of the School Year field.

4. Find the On Got Focus event property and click to select it.

5. Select [Event Procedure] from the drop-down control.

6. Click on the Build (. . .) Button to open the Form’s VBA Module (known as Class Module).  You may find the following lines of Code in the Class Module.

   The VBA Code.

   Option Compare Database
   Option Explicit
   
   Private Sub SchoolYear_GotFocus()
   
   End Sub

7. Copy and paste the following lines of Code overwriting the existing above lines of code in the Module:
   

   Option Compare Database
   Option Explicit
   'SID is declared as a global variable
   Dim SID As Long
   
   Private Sub SchoolYear_GotFocus()
   'Save the SessionID value in a Global variable
   SID = Me!SessionID
   End Sub
   
   Private Sub SchoolYear_LostFocus()
   '----------------------------------------------
   'This subroutine runs when the Focus is shifted 
   'from the SchoolYear field.
   '----------------------------------------------
   'Author : a.p.r. pillai
   'Date   : Jan/2013
   'All Rights Reserved by www.msaccesstips.com
   '----------------------------------------------
   Dim ctrl As Control, ctrl2 As Control
   Dim bkmk As String, rst As Recordset, j As Long
   Dim rstSID As Long
   
   'The following lines of code prevents shifting the focus
   'to the next record on the frm_Session sub-form, when the focus
   'is lost from the last field of frm_Session, in preparation to set focus
   'on a particular field on the corresponding record on the
   'frm_Payments sub-form.
   Set rst = Me.RecordsetClone 'frm_session's recordset
   rst.MoveFirst
   For j = 1 To rst.RecordCount
   rstSID = rst![SessionID]
   If rstSID = SID Then 'find the record matching the current record on frm_session
      'when match found save the record's recordset bookmark
      bkmk = rst.Bookmark
      'copy the recordset bookmark to the form
      'this will set the focus back on the same record
      'this will also ensure that the SessionID related Payment record
      'will be current on the frm_Payment form
      Me.Bookmark = bkmk
      'set focus on the first field
      Me.SessionID.SetFocus
      'break the loop
      Exit For
   End If
   rst.MoveNext
   Next
   rst.Close
   'transfer control to the frm_Payments Sub-form
   'Now the field with Tabindex number 0 have the default focus
   Set ctrl = Forms![frm_Students].Controls("frm_Payments")
   ctrl.SetFocus
   'Once the focus is shifted on the field with tabindex 0 within frm_Payment sub-form
   'we can move the focus to any other field within that form, if required
   Set ctrl2 = Forms![frm_Students]![frm_Payments].Form.Controls("Amount")
   ctrl2.SetFocus
   
   End Sub
   

8. Save and Close the Form.

   Try out your Forms.

9. Open the frm_Students in the normal view.

10. Click on the first record on the frm_Session Form.

11. Press Tab Key to move the focus to the School Year field and check the corresponding record on the frm_Payments form.

12. Press Tab Key one more time to jump the control onto the frm_Payments sub-form record, with the same Session ID value,  and to set the Focus directly on the Amount field.

Download Demo-Database.

Download Demo SubForm.zip

• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code

Read More

Setting Focus on a field inside a Sub-Form

Introduction.

The Main Form (frm_Students) has two Sub-Forms (frm_Sessions and frm_Payment).  A sample image of such a Form is given below:

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

Sub-Form Container and Sub-Form.

Each Sub-Form on the main form is placed within a Sub-Form Container on the main form.  The Sub-Form has other controls (like Textboxes) within it and we cannot set focus directly on any of these controls, from outside the sub-form container.  That doesn’t mean that we cannot address the controls directly to retrieve or set the value into that control through VBA.

The Difference Between Setting Focus and Retrieving Values

Setting focus in the Amount field of frm_Payments directly, when frm_Payments doesn't have the focus on, through VBA is not possible.

Example:

'this statement will not work when frm_Payments doesn't have focus
Forms![frm_Students]![frm_Payments].Form.Amount.SetFocus

The above statement may highlight the Amount field, but the focus will not change to that field.

But, we can retrieve the value directly from the Amount field of frm_Payments, even when the focus is not ON on that form, with the following statement.

Example-2:

'this statement retrieves the Amount field value directly.
m_Amt = Forms![frm_Students]![frm_Payments].Form!Amount

When the frm_Payments sub-form has the focus ON you can address a control (say the Amount field) within that form to set the real focus on it.

It simply means that it takes two-step action to address a control within a sub-form to set focus on:

1. Set focus on the sub-form container first.  Setting the Tab Index Value of the frm_Payment to 0 also works.
2. Set focus on any control within the frm_Payments sub-form.

So, it is a two-step process and the following two VBA statements do the job:

Me.Payments.form.SetFocus
Me.Payments.form.Amount.SetFocus

The following version of the above statements is also valid:

Forms![frm_Students]![frm_Payments].SetFocus
Forms![frm_Students]![frm_Payments].Form.Amount.SetFocus

What Next...

Next, we will see how to jump from the last field of one sub-form record to its corresponding record field on the second sub-form directly.  If you think it is so easy after learning the above two lines, then think twice.

• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code
• Prepare a List of Procedure names from a Module

Read More

Conditional Formatting in Continuous Form

Introduction.

Conditional formatting is a magical option to change the color (background/foreground) of textbox controls on Forms or Reports.  The Color change is possible when one or more field values meet a specific condition.  The specific condition can be tested in the field, where we want to set the color or can be based on values in any other field.

The color change has three different possible choices and their variations:

1. Color changes when the field has focus.
2. Color changes when the field value meets a specific condition.
3. The Color changes depending on conditional values in some other field.

Changing the Background Foreground Color.

We will try out an example, to learn how to change the background/foreground colors of all fields of certain records, which meet a specific condition on a single field value, on a continuous form.

A sample image of a Form with conditional formatting is given below for reference:

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

The records are formatted based on values in the Unit Price field.  The background color is set on records with a Unit Price value greater than $30.  The font is set with Red color on records with Unit Price value falling between 20 and 30.

Design a Continuous Form.

1. Import the Order Details and Products tables from the Northwind sample database.

2. Design a continuous form on the Order Details Table, like the sample image below:

   

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

   Setting up the Conditions.

3. Open the Order Details Form in Design View.

4. Select the OrderID field and open the Conditional Formatting dialog control.

5. Select Expression Is under Condition 1 and type [UnitPrice]>30 in the right-side control.

6. Select Fill/Back Color to display the color palette and select a light color for the background.

7. Click on the Add>> button to display options for a second condition for the same field.

8. Select Expression Is in Condition 2 Control.

9. Type the expression [UnitPrice]>=20 And [UnitPrice]<=30 in the next text control.

10. Select Red Font Color for formatting from the color palette and click OK to close the formatting dialog box.

11. Repeat Steps-4 to Step-10 for ProductID, Quantity, and Discount fields.

12. Select the Unit Price field and display the Conditional Formatting dialog control.

13. Select Field Value Is in the Condition 1 control.

14. Select the same background color selected, for other fields for formatting, from the Color Palettes.

15. Click on the Add>> Button to add a new condition for the same field.

16. Select 'Field Value Is' in Condition 2 Control.

17. Select Between from the drop-down list in the next control.

18. Type 20 in the next control and type 30 in the last control.

19. Select the Red Font Color for formatting from the color palettes and click OK to close the conditional formatting dialog box.

    Test Run your Creation.

20. Save the Form and open it in the normal view.

You should see the form view similar to the sample image shown above.  Scroll the form down to view more records with conditional formatting.

• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code
• Prepare a List of Procedure names from a Module
• VBA Module Object and Methods

Read More

Autonumber with Date and Sequence Number-2

Introduction.

Perhaps you may be wondering why we need something different when there is an Autonumber feature already built into Microsoft Access?  Well, the built-in feature may not be suitable for all situations, like patients' Unique Registration Numbers in hospitals, with the change of date and sequence numbers. We have created a function earlier for generating auto-numbers with date and sequence numbers, and you can have that from the first link given below. You may visit the other related links for some different approaches for generating Auto-numbers in Queries, too.

• Autonumber with Date and Sequence Number
• Product Sequence with Auto numbers.
• Autonumbering in the Query Column.

We are now going to take a different approach to generate auto-numbers with the date and sequence numbers. Let us take a re-look at the last method we have created with date & sequence numbers, and how we are going to reformat the same thing in the new method with a lesser number of digits as follows:

Sample Data Image.

Sample Dates: 30-10-2012 and 31-10-2012

The Auto-number generated for patient registration looks like the following: the format used in the earlier article (first link above):

Format: yyyymmdd-999

Autonumber-with-Date method-1

Saved Number	Display with an Input mask
20121030001	20121030-001
20121030002	20121030-002
20121030003	20121030-003
20121030004	20121030-004
20121030005	20121030-005
20121031001	20121031-001
20121031002	20121031-002
20121031003	20121031-003

The dash in the number is inserted using the input mask for better readability in the display control. In the above example, it uses eight digits for displaying the date part and three digits for serial numbers. This method requires a total of 11 digits for the auto-number.

In the following new method, we are going to create date-wise changing auto-numbers, which take only eight digits, like the example shown below:

Sample Date: 30-10-2012 and 31-10-2012

New display format: yyddd-999

The first two digits (yy) store the year (12), the next three digits (ddd) are the day number from 1st January (is 001) onwards. October 30th, 2012, is the 304th day from January 1st, 2012.

Autonumber-with-Date method-2

Saved Number	Display with an Input mask
12304001	12304-001
12304002	12304-002
12304003	12304-003
12304004	12304-004
12304005	12304-005
12305001	12305-001
12305002	12305-002
12305003	12305-003

The sequence numbers reset to 001 when the date changes.  The new method's result is somewhat compact in size and takes only eight digits to store the auto-number in place of eleven digits in the earlier method.

DaysAsOnMonth() Function.

Copy and paste the following VBA Code into a Standard Module of your Database:

Public Function DaysAsOnMonth(ByVal dt As Date) As Long
Dim i As Integer, j As Integer, tdays As Long, d As Long

On Error GoTo DaysAsOnMonth_Err
i = Month(dt)
d = DatePart("d", dt)

For j = 1 To i - 1
tdays = tdays + Choose(j, 31, 28 + IIf(Year(dt) / 4 = Int(Year(dt) / 4), 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
Next
If (Year(dt) Mod 400) = 0 And Month(dt) > 2 Then
  tdays = tdays - 1
End If
tdays = Val(Right(Year(dt), 2)) * 10 ^ 3 + tdays
tdays = tdays + d
DaysAsOnMonth = tdays

DaysAsOnMonth_Exit:
Exit Function

DaysAsOnMonth_Err:
MsgBox Err & " : " & Err.Description, , "DaysAsOnMonth()"
Resume DaysAsOnMonth_Exit

End Function

The AutoNumber() Function.

Public Function AutoNumber(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

On Error GoTo AutoNumber_Err

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(DaysAsOnMonth(Date) * 10 ^ 3 + 1)
   AutoNumber = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 5)
'get today's date
dt2 = Format(DaysAsOnMonth(Date))
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   AutoNumber = Format(Val(dt1) * 10 ^ 3 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   AutoNumber = Format(Val(dt2) * 10 ^ 3 + 1)
End If

AutoNumber_Exit:
Exit Function

AutoNumber_Err:
MsgBox Err & " : " & Err.Description, , "AutoNumber()"
Resume AutoNumber_Exit

End Function

How it works.

The first function, DaysOfMonth(), is called from the AutoNumber() Function to calculate the number of days from January 1st to the date passed as a parameter to the function.  The input date 30-10-2012 will return the result value 304, i.e., 31+29+31+30+31+30+31+31+30+30 = 304.

The trial run procedure for the new method is already published in an earlier article. I will take you to the exact point in that Article, from where you can continue reading and prepare yourself for the demo. All that you should do is to change the Function name Autonum(), appearing in those sample run lines, to AutoNumber().

Click to continue...

Download the Demo Database and Modify.

DateAndSeq.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Read More

Command Button Color Change on Mouse Button-down Action

Introduction.

After working with the Command Button and Message Box Controls of MS-Access I really got bored with their styles. So, I set out to do something about it and created some animation styles for the command buttons and used Office Assistant (valid only for Access2003 and earlier versions) for Message Boxes. The Links to those Articles and free downloads links are given below:

Command Button Styles

1. Command Button Animation
2. Command Button Animation-2
3. Colorful Command Button

   Message Box Styles (Access2003 or earlier versions only)

4. Message Box using the Office Assistant
5. MsgBox with Options Menu
6. MsgBox with Checkbox Menu

Some New Trick on Command Button.

Here is a new trick for you to use on Command Button Control. Command Button will change the color on the mouse button down the action and will restore the original color on the mouse-up action.  You can do this with a Label control, with the same dimension as the Command Button, filled with the color you like, and by placing it behind the Command Button Control.  You need two lines of VBA Code on the Form Module as well.

Design the New Animation Style.

Let us go through with this simple design and enjoy the new animation style of the Command Button Control.

1. Open a new Form.

2. Create a Command Button Control on the Detail Section of the Form.

3. Display the Command Button’s Property Sheet (F4).

4. Change the Name Property Value to cmdClose.

5. Change the Caption Property Value to Close.

6. Create a Label Control with some caption text near the Command Button.

7. Select the Command Button and the Label Control together.

8. Right-click on it and highlight the Size option on the Shortcut Menu and select To Tallest.

9. Repeat step 8 above and select To Widest.

10. Click on the Label Control alone and display its Property Sheet (F4).

11. Remove the text from the Caption property.

12. Change the Special Effect Property value to Raised.

13. Select the Back Color Property and click on the Build ( . . .) button to display the Color Palette.

14. Select the color you like to fill the background of the label control.

15. Move the Label control behind the Command Button control and place it completely hidden.

16. If the label control is overlapping the Command Button then display the Shortcut Menu by right-clicking on it and selecting Send to Back from the Position group.

17. Click on the Command Button and display the property sheet (F4).

18. Click on the Event Tab and select the Mouse down Event, select [Event Procedure] from the drop-down control and click on the build (. . .) button to open the Form’s VBA Module Window.

19. Copy and Paste the following Code into the Form Module, over-writing the existing two empty lines that appeared there:

    Private Sub cmdClose_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
      Me.cmdClose.BackStyle = 0 'transparent
    End Sub

20. Repeat step 18 for Mouse Up [Event Procedure] and copy and paste the following Code into the Form Module:

    Private Sub cmdClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) 
        Me.cmdClose.BackStyle = 1 'Normal 
    End Sub

Test Run.

Now, it is time for the test run of our design. 

1. Save the Form with the name frmButton.

2. Open frmButton in Form View.

3. Click on the Command Button and hold the mouse button down.  You will see the Command Button’s Color changes to the background fill color you have selected for the label control and Command Button caption-text Close appearing on it.

4. Release the mouse button.  The original state of the Command Button is restored.

The trick works by changing the Back Style of the Command button to transparent, forcing it to display the color of the Label control behind it and changing to a Normal view when the mouse button is released. 

You can learn another trick from an earlier Article on Transparent Command Button, here.

1. Command Button Animation
2. Double Action Command Button
3. Colorful Command Buttons
4. Transparent Command Button
5. Command Button Animation-2
6. Creating Animated Command Button with VBA
7. Command Button Color Change on Mouse Move

Read More

Time-bound Form Mode Change

Introduction.

Sometimes, you may want to allow data entry or editing only during specific time periods, and at all other times keep the form locked in a read-only mode. This requirement usually arises in time-bound workflows, where users are permitted to update records only within fixed working slots.

A similar request was once raised in a Microsoft Access discussion forum, and I decided to explore a practical solution.

In the following example, we will enable data entry and editing on a form only during these time periods:

• 06:00 – 07:00 Hours

• 11:00 – 13:00 Hours

• 17:00 – 19:00 Hours

At any other time, the form will remain restricted to data view only. The Data Entry Control Function.

The function written for this task is given below. Copy it into the Standard Module:

Public Function Data_Entry(ByVal frmName As String)
Dim T1S, T1E, T2S, T2E, T3S, T3E
Dim frm As Form

Set frm = Forms(frmName)

D = Date
T1S = TimeValue("06:00:00")
T1E = TimeValue("07:00:00")

T2S = TimeValue("11:00:00")
T2E = TimeValue("13:00:00")

T3S = TimeValue("17:00:00")
T3E = TimeValue("19:00:00")

Select Case time
      Case T1S To T1E, T2S To T2E, T3S To T3E
          With frm
            If .AllowAdditions = False Then
               .AllowAdditions = True
               .AllowEdits = True
               .lblMsg.Visible = False
'change .subFrmName to match the control (window) name of the sub-form
               .subFrmName.Enabled = True
            End If
          End With
          frm.Refresh
      Case Else
          With frm
            If .AllowAdditions = True Then
                .AllowAdditions = False
                .AllowEdits = False
                .lblMsg.Visible = True
 'change the next line to set focus on any field on the main form
                .EmployeeID.SetFocus
'change .subFrmName to match the control (window) name of the sub-form
                .subFrmName.Enabled = False
            End If
          End With
          frm.Refresh
End Select

Set frm = Nothing
End Function

NB: You must make changes wherever applicable to point the code to correct control names on your Form, which I have marked with comments.

Some Changes to the Form.

1. Open your Form in Design View.

2. Add a Label control on the main Form where you want to display 'Entry not allowed', change the Name property value to lblMsg, and write the message in the Caption property.

3. Display the Form Property Sheet.

4. To implement this feature, we can take advantage of the Form’s Timer event.

   1. Set the Timer Interval property of the form to 60000 (i.e., 60,000 milliseconds = 1 minute).

      • This means Access will automatically check the current system time once every minute.

      • If you want more frequent checks, increase the interval in 1000 millisecond increments (1,000 = 1 second).

   2. In the Form_Timer event procedure, we can write a simple VBA routine to check whether the current time falls within the allowed edit periods. If it does, the form will be set to data entry/edit mode. Otherwise, it will automatically switch to view-only mode by disabling edits.

   This way, the form will self-adjust every minute, ensuring that users can only enter or edit data during the specified hours. Select the On-Timer() Event, select [Event Procedure] from the drop-down control, and click on the build (...) Button to open the VBA module.

5. Copy and paste the following lines of Code, replacing the existing two lines displayed there:

   Code:

   Private Sub Form_Timer()
      Data_Entry Me.Name
   End Sub

6. Save and Close the Form.

Tracking the Time for Form Mode Change.

The Timer setting ensures that the program checks every minute to determine whether the current time falls within the allowed time slots specified in the code. If the condition is true, both the main form and its subform will be enabled for data entry and editing; otherwise, they will remain locked in read-only mode.

When the form is opened, however, there will be an initial delay of one minute before Access performs the first check. To avoid this delay, you can call the Data_Entry() function from the Form_Current event procedure. This ensures that the time-check routine runs immediately upon opening the form, rather than waiting for the first timer tick.

Earlier Post Link References:

• Prepare a List of Procedure names from a Module
• VBA Module Object and Methods
• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database

Read More

Missing Lines in Line-Chart

Introduction.

You spent several hours preparing the data for your line chart.  Designed the Line-Chart on a Report with Title, Data Labels, and Legends, and it looks nice except for one big problem with the Profit/Loss line.  The Line doesn’t show up on the graph except for two marker points on Qrtr1 and Qrtr3 value points, and nothing shows on Qrtr2 and Qrtr4 value locations.

Check the sample Graph Chart Image shown below with the points marked with yellow color on the Profit/Loss line:

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

Take a look at the following Graph Chart Image with the Source Table displayed:

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

Tracking Down the Real Issue.

Did you notice where the actual problem is?  In the Profit/Loss row, in Qrtr2 and Qrtr4 cells have Null values in the table, resulting in the Graph Chart ignoring these cell values and not connecting other values with lines, without breaks in between. While preparing data (source Table/Query) for the Graph Chart, ensure that none of the cells end up with a Null value. If there are Cells with Null values, then fill them with Zeros.

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

The corrected Chart Table, filled with zero values in empty cells, resulted in connecting the points with the line correctly on the Graph Chart image shown above. 

You can modify the Chart Source Value by modifying the Row Source Property SQL value, without directly updating zeroes on the Source Table.

Modifying the Chart Data Source Query.

1. Open the Report with the Graph Chart in Design View.

2. Click on the Chart’s outer frame to select it.

3. Display the Property Sheet.

4. Click on the build (...) button on the Row Source Property to open the Graph Chart Source Query in Design View.

5. Modify the Query Columns to get the SQL modified as shown below:

   

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

   SELECT Chart.Desc, Val(nz([qrtr1],0)) AS [Qrtr-1], 
     Val(nz([qrtr2],0)) AS [Qrtr-2],
     Val(nz([qrtr3],0)) AS [Qrtr-3],
     Val(nz([qrtr4],0)) AS [Qrtr-4] FROM Chart;
   

6. Save and close the Query.

7. Open the Report with the Graph Chart in Print Preview mode to view the effect of the change.

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line-Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

Back Tracking Open Forms

Introduction

On the Internet, when we browse from one webpage to another, the browser provides Back and Forward buttons to move through previously visited pages one at a time.

A similar approach can be applied in Microsoft Access. During startup, we can open several forms sequentially and keep them hidden in memory. The question is: how do we move back and forth between these forms—just like navigating web pages? Fortunately, there is a way to achieve this.

When multiple forms are required for day-to-day operations, it is often a good practice to open them all at once (immediately after the application starts) and keep them hidden. Although this may introduce a slight delay during startup, it significantly improves performance later, since forms are displayed directly from memory rather than being opened and closed repeatedly. All hidden forms can also be closed easily with a short VBA routine before shutting down the application.

If navigation from one form to another is needed in a predictable sequence, you can automate the process with a simple macro by arranging the forms in the desired order. The sample macro shown below demonstrates how to open multiple forms in sequence.

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

In the sample macro, the first OpenForm action opens the Employees4 form in Normal View mode, while the other forms are opened in Hidden mode so they remain in memory. When needed, any of these hidden forms can be made visible, while the currently active form can be set to hidden at the same time. This ensures that only one form is visible at a time, preventing the application window from becoming cluttered with multiple open forms.

Prepare for a Trial Run.

Let us try an example before exploring other aspects of this interesting method.

1. Import the Employees Table and Employees Form from Northwind.mdb (or Northwind) sample database.

   Check the sample image given below.

   

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

2. Rename the Employees form as Employees1.

3. Open the 'Employees1' Form in Design View.

4. Add a label control in the Header of the Form and change the Caption value to 1, change the font size to 16, and the foreground color to White or any other bright color suitable for the background.

5. Expand the Footer of the Form.

6. Add two Command Buttons in the Form Footer as shown above.

7. Click on the left side Command Button to select it.

8. Display its Property Sheet (F4).

9. Change the Name property value to Back and change the Caption property value to << (two less than symbols).

   Two Button-Click Event Sub-Routines.

10. Select the OnClick Event property and select [Event Procedure] from the drop-down list, and click on the build (...) Button to open the VBA editing window with the empty Sub-routine stub: Private Sub Back_Click() . . . End Sub.

11. Copy the following Code and paste it, overwriting the sub-routine lines in the VBA Module:

    Private Sub Back_Click()
      ForwardBack "B", Me.Name
    End Sub

    Note: ForwardBack() is a Function we will write and add to the Standard Module.

12. Repeat steps 7 to 10 for the right-side Command Button by changing the Name property value to Forward() and the Caption property value to >> (two greater than symbols).

13. Copy the following Code and paste it, overwriting the sub-routine starting and ending lines in the VBA Module:

    Private Sub Forward_Click()
      ForwardBack "F", Me.Name
    End Sub

14. Save and close the Form.

    The Move ForwardBack() Function

15. Copy and paste the following Code into a Standard Module in your Database and save it:

    Public Function ForwardBack(ByVal strStatus As String, ByVal strForm As String)
    Dim frmCount As Integer, j As Integer
    
    On Error GoTo ForwardBack_Err
    
    'get count of open forms in memory
    frmCount = Forms.Count - 1
    For j = 0 To frmCount
    Select Case strStatus
          Case "B" 'Move Back
            If Forms(j).Name = strForm And j - 1 >= 0 Then
               DoCmd.SelectObject acForm, Forms(j - 1).Name, False
               Forms(strForm).Visible = False
               Forms(j - 1).Visible = True
               Exit For
            End If
         Case "F" 'Move Forward
            If Forms(j).Name = strForm And frmCount > j Then
               DoCmd.SelectObject acForm, Forms(j + 1).Name, False
               Forms(strForm).Visible = False
               Forms(j + 1).Visible = True
               Exit For
            End If
    End Select
    Next
    
    ForwardBack_Exit:
    Exit Function
    
    ForwardBack_Err:
    MsgBox Err & ":" & Err.Description, , "ForwardBack()"
    Resume ForwardBack_Exit
    End Function

    The ForwardBack() Function needs two parameters when called:

    The first parameter can be either "B" or "F".

    • Use “B” as the first parameter value when called from the << (Go Back) labeled Command Button Click Event Procedure and with "F" for >> (Go Forward) labeled Command Button Click Event Procedure.

    • The second parameter is the active Form's name, which can be passed with the 'Me.Name' statement.

16. Make 4 more copies of the Employees1 Form and name them as Employees2 to Employees5. The index in the header labels should also change to 2, 3, 4, and 5 on their respective Forms.

    Since all the forms are copies of the same form, this number will help us to distinguish one from the other.

17. Create a Macro similar to the sample image shown at the top of this page to open the forms, and keep them hidden in memory except for one.  You may define any one of the form’s Window Mode as Normal to make that form visible in the Application Window, while all other forms stay hidden.

18. Save the Macro with the name Macro1.

Test Run our Creation and Program.

Now, it is time to test our Project.  First, let us test our Project manually without using Macro1.

1. Open Forms Employees1 to Employees5 manually, one by one, from the Navigation Pane.

2. You now have all the Forms opened in the Application Window.  The Employees5 form is on top, with the form header label displaying the number 5.

   When multiple forms are opened in this manner, they are stored in memory within the Forms Collection Object, arranged in the order they were opened. The first form opened can be accessed using index 0 (e.g., Forms(0) or Forms("Employees1") in VBA). You can retrieve the form’s name with the Name property (Forms(0).Name), and in the same way, access other properties of the form. The second form opened will have index 1, the third will have index 2, and so on.

   Keep in mind that the suffix numbers we added to the Employees forms (e.g., Employees1, Employees2, …) have nothing to do with these internal index numbers. Forms can be opened in any order you like; you don’t need to start with Employees1 and end with Employees5. However, following a simple naming sequence at the beginning can make it easier to test and understand the program.

   Click on the Command Button with the >> (Go Forward) symbols on it to move forward to the next form, but nothing will happen because this is the last form in the opened Forms Collection now.

3. Click on the Command Button with the << (Go Back) symbols to make the Employees4 form visible and to place the current form Employees5 in the hidden state in memory.

4. Repeat step 2 to make the Employees3 form visible and continue doing this till you reach Form Employees1. 

   At this stage, clicking the Back button (<<) on this form will not produce any response because it is the first form we opened. However, if the forms were opened in a different order, the button would work as expected. When you arrive at the Employee1 form, all the other forms remain in memory in a hidden state, since our main program is designed to keep them that way.

5. Try to move forward by clicking on the >> button to make the other forms become visible one by one, hiding the earlier forms.

Closing All Open Forms - The CloseAllForm() Function

Tip: If you want to make changes to any of these forms while they are hidden in memory, simply right-click the form’s name in the Navigation Pane and select Design View. The form will open directly in Design View.

To close all the open forms (both hidden and visible) in one go while shutting down the application, you can use a simple VBA routine named CloseAllForms(). This routine can be called from a command button’s Click event procedure, right before executing the DoCmd.Quit statement. You may also run the program directly from the VBA window while testing the procedure.

Copy and paste the following code into a Standard Module of your project:

Public Function CloseAllForms()

Dim j
'when no forms are in open state
'then forms.count -1 returns -1 and
'the For...Next loop is not executed

'When a form is closed
'other forms in memory are re-indexed automatically
For j = 0 To Forms.Count - 1
  DoCmd.Close acForm, Forms(0).Name
Next
End Function

Click anywhere inside the code and press F5 to run it. This will close all the open forms.

In the earlier test, we opened all the forms manually. Instead, you can run the macro (Macro1) we created earlier to open all the forms at once, keeping all of them hidden except one.

If you want this macro to run automatically when the database opens—so that all forms are loaded into memory and hidden—rename Macro1 to AutoExec.

During normal operations, users can open forms in any order, and they will remain in memory in the sequence in which they were opened. Users can then navigate through the open forms by clicking command buttons: >> (Go Forward) or << (Go Back).

• VBA Module Object and Methods
• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File

Read More

Stretching Controls when resizing Form View

Introduction

A sample image of an Employee's Form Design is given below:

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

When you view this screen in a maximized Application Window, the view will be something like the following image:

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

The normal view of the Employees Form, in maximized Application Windows, shows an empty stretch of space to the right of the data fields.  The Form Title Employees stay where it is placed in a Label control, centralized over the data field controls.

Now, look at the following image taken after the Anchor Settings to the controls, which respond dynamically to move or stretch across the screen based on the resizing of the screen:

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

Compare the two images shown above. Notice how the layout changes automatically when the form window is maximized:

• The second column of controls shifts neatly to the right edge of the screen.

• The controls on the left side expand horizontally, filling up unused space and giving more room for data entry or viewing.

• The form heading (“Employees”) realigns itself to the center of the screen.

• The Note field (a memo field) stretches both downward and across, making it much easier to view and edit larger amounts of text.

Curious to know how this neat effect works in MS Access 2007? If you already have a form with a design similar to the one shown in the first image, you can try it out yourself — and here’s the best part: this trick works with any form!

Design a Form.

Let us design a Form with the Employees Table from the sample database Northwind.

1. Import the Employees Table from the Northwind sample database.

2. Create a form with a similar Design shown at the top of this page.  You can use the Form Wizard to create the form in Column format and rearrange the controls.

3. Select all the controls of the second column together and move them down to get enough space for the Fax Number and Address fields, which we will bring from the first column, and place them on top of the second column.

4. Select Fax Number and Address Fields, and right-click on them to display the shortcut menu.

5. Select Cut from the menu to remove both text boxes and their child labels from the first column controls group.

6. Right-click somewhere in the Detail Section and select Paste to paste them back into the detail section.

7. Move the pasted controls and place them on top of the second column of controls.

8. Select and cut the Note Field and its child label from the second column and place them below the first column of controls.

Up to this point, everything we did was part of a standard form design process — arranging controls into columns as usual. However, there’s one important exception: the Note field. Instead of keeping it in the second column’s control group, we deliberately moved it below the first column as a standalone control. This way, it is no longer tied to the group’s layout behavior and can be assigned a different Anchoring property, allowing it to stretch independently when the form is resized.

1. Save the Form and open it in Form View to check what the current design looks like.

   Implementing the Trick.

2. Change the Form in Design View.

   Let’s begin applying these tricks to the controls on our form, starting from the top.

   Step 1: Centering the Heading Label
   We want the form heading to remain horizontally centered whenever the form is resized. If the text “Employees” in the header label is not already centered within its current width, select the label and click the Center button on the Design tab of the ribbon.

   Step 2: Making the Heading Responsive
   Next, we want the label itself to stretch across the available width of the form so that the caption “Employees” always stays centered—whether the form is maximized or manually resized. To achieve this:

   1. Select the heading label control.

   2. Open the Property Sheet (if not already open).

   3. Locate the Anchoring property.

   4. Change the setting to Stretch Across Top.

   With this setting, the label automatically resizes with the form, and its caption remains centered no matter how the window is adjusted.

3. Click on the heading label to select it.

4. Select Arrange ->Anchoring -> Stretch Across Top.

   Testing the Heading Label Behavior

   1. Open the form in Normal View.

   2. Maximize the window and check whether the form title Employees moves to the center across the expanded screen.

   3. Next, manually resize the form:

      • Move the mouse pointer to the right edge of the form until it changes to the horizontal sizing arrow.

      • Hold down the left mouse button and slowly drag the edge inward.

      • Watch how the heading label automatically adjusts its width and keeps the caption centered.

   4. For a quicker test, minimize the Navigation Pane and then display it again. The form window will expand and shrink instantly, showing the anchoring effect in action.

   Moving On

   Now that the header label behaves as expected, let’s play some anchoring tricks with the other controls in the Detail Section. This is where we’ll make the second column “stick” to the right edge and stretch the first column controls for better usability when resizing.

5. Place the Form back into Design View.

6. Highlight and select all the second column TextBoxes.

7. Select Arrange ->Anchoring -> Top Right or right-click on the selected controls and select Anchoring -> Top Right from the shortcut menu.

8. Change the form in Form View and preview the effect of our setting. Change the form back into Design View again.

9. Select the TextBoxes in the left column, except the Note field.

10. Right-click on the controls and select Anchoring -> Stretch Across Top.

11. Select the Notes field, right-click on the control, and select Anchoring -> Stretch Down and Across.

All the anchoring settings are now complete. Save the form and switch to Normal View. Try resizing the form window manually—dragging the edges slowly—to watch how each control responds in slow motion. You’ll see the title label, the right-side controls, and the Note field all adapt smoothly to the changing form size.

• Writing VBA Code with VBA
• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram

Read More

Defining Pages on Form

Introduction.

When designing a form, we usually place controls within the visible area of the screen for ease of use. However, an Access form can actually be as large as 22 x 22 inches, giving you plenty of real estate to work with. The challenge is not in placing controls on this large surface but in arranging them in a well-organized way. Without careful planning, the form may feel cluttered and inconvenient for users to navigate.

Page Usage Plan.

Let’s plan a four-page layout on a sample form to experiment with control placement and resizing. Assuming the visible area of the form is approximately 10 inches wide, we can define two logical pages side by side, each about 10 inches wide. Then, leaving about 5 inches from the top of the form, we can define another two pages below the first row. The diagram below illustrates this four-page layout for quick reference in our project.

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

We have planned four logical page areas as shown above. Physically, however, there are only two pages:

• The first-page area spans the top, divided into two segments.

  • The first segment (logical page 1) starts at coordinates (0,0) — 0 inches from the left and 0 inches from the top of the form.

  • The second segment (logical page 2, top right) starts at coordinates (10,0) — 10 inches from the left and 0 inches from the top.

Each logical page is approximately 10 inches wide and 5 inches tall. For the macro that moves controls between pages, we only need to specify the top-left corner coordinates of each logical page.

• The second logical page area (top right) begins at (10,0).

• The command buttons located at the bottom-right corner of each logical page will run a macro to jump between pages.

The Page-Break control.

The dotted line on the left at the 5-inch vertical position is a Page Break control, inserted from the Toolbox, to define the second physical page area (which contains the third and fourth logical pages).

• The third logical page (first segment of the second physical page) starts at 0 inches from the left and 0 inches vertically on the second physical page (i.e., 5 inches down from the top of the form).

• The fourth logical page (second segment of the second physical page) starts at 10 inches from the left and 0 inches vertically on the second physical page.

The 10-inch width and 5-inch height for logical pages are arbitrarily chosen. You may adjust them if the pages overlap on your screen. Using the same rules, you can define additional logical pages on the form if needed.

The Page Navigation Plan.

Clicking on the first Command Button moves the focus to the second page at the top-right corner. The Command Button on this page will send the focus to the bottom-left page, and the button there will move the focus to the bottom-right page. Finally, the Command Button on the bottom-right page will bring the focus back to the top-left page, completing the loop.

Tip: You can also connect the macro to the Lost_Focus() event of the last textbox or control on each logical page. This way, when the user presses the Tab key, the focus will automatically move from one page to the next without needing to click a Command Button.

Now that the master plan and diagram are ready, we can implement them on a form. Let’s start by writing the macros to control the Command Buttons.

Create Macros.

1. Select Macro from the Create menu to open a new Macro in the design view.

2. Click on the Macro Names control to open the Name column of the macro.

3. Select the first row in the Name column and type Page1-1.

4. Select GotoPage from the drop-down control in the Action column.

5. Select the Arguments column.

6. Type 1 in the Page Number Action Arguments property below.

7. Type 0 in the Right property and 0 in the Down property.

8. Create the other three lines with the Argument values as shown in the image below:

   

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

9. Save and close the Macro with the name MacPages.

The Form Design.

10. Open a new Form in Design View.

11. Display the Property Sheet (F4).

12. Click on the top left corner of the Form (Access2003) to select the Form’s Property Sheet or select Form in the Selection Type box (Access2007) of the Property sheet.

13. Change the Width Property value of the Form to 20 inches.

14. Click on the Detail Section to display its property sheet.

15. Change the Height Property Value to 10 inches.

16. Select the Page-break Control from the Toolbox.

17. Find the 5-inch position from the left side Scale and click near the left border on the Detail Section to place the Page-break control there.

18. Create a Textbox control, about half an inch down on the top left corner of the Form, and change the child label Caption value to Page-1.

19. Create a Command Button below the Textbox and change the following property values as shown below:

    • Caption:  Go to 2
    • On Click:  MacPages.Page1-2

      Tip: You may select the Macro Name from the drop-down list to avoid typing mistakes.

20. Create a second Textbox to the right of the first one, about half an inch to the right of the 10-inch scale location on top of the Form, and change the child label Caption to Page-2.

21. Create a Command Button below the Text and change the following property values as shown below:

    • Caption:  Go to 3
    • On Click:  MacPages.Page2-1

22. Create a Textbox below the Page-break control and change the child label Caption to Page-3.

23. Create a Command Button below the Textbox and change the following property values as shown below:

    • Caption:  Go to 4
    • On Click:  MacPages.Page2-2

24. Create a Textbox after the 10-inch location to the right and change the child label Caption to Page-4.

25. Create a Command Button below the Textbox and change the following property values as given below:

    • Caption:  Go to 1
    • On Click:  MacPages.Page1-1

26. Save the Form with a name.

Test Run the Form.

27. Open the Form in normal view and click on the Command Button to jump to the second page to the right.

    You will find that the form jumps to the second logical page to the right, and the Textbox on this page is the active control.

28. Try clicking on other Command Buttons to transfer control from one page to the next.

In all cases, you will find that the textbox on the active logical page is the active control.

• Control Setfocus on Tab-Page Click
• Adding Data directly into an external Database
• Users and Groups Listing from Workgroup Information File
• Access Security Key Diagram
• Updating Sub-Form Recordset from Main Form

Read More