Active Labels: Form Links Using Labels

To give your application more flexibility you can create links out of the labels on fields where appropriate. This is most useful when you have a dropdown list and the information you have to enter is not in the list.When this is the case, making the label of the list a link to an input form to enter the information needed is easy and user friendly.In this screen shot you see a form showing a Company drop down and a Contacts dropdown. The functionality is to select a Company from the list. If the company you need is not listed then you double click Company and a company input form pops up. When you have selected the company the Contacts dropdown lists contacts associated with that company. If your contact is not listed double click the Contacts label and enter the contact information.

The code to open the Contact form is in the ‘On Double Click’ event of the Contact Label. That code is:

Private Sub lblContact_DblClick(Cancel As Integer)
On Error GoTo Err_lblContact_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “CompanyContact”

stLinkCriteria = “”
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, , “Add”

Exit_lblContact_DblClick:
Exit Sub

Err_lblContact_DblClick:
MsgBox Err.Description
Resume Exit_lblContact_DblClick
End Sub

Note that on the form open a parameter of ‘Add’ is passed to the Contact form. This tells the form what function is needed so that it can set appropriate items in the ‘On Open’ event and when the form closes it can process appropriately. Here is the ‘On Open’ code:

Private Sub Form_Open(Cancel As Integer)

If Me.OpenArgs = “Tracking” Then
txtCompanyLocationID.DefaultValue = [Forms]![CompanyTracking]![txtLocationID]
Else
If Me.OpenArgs = “Add” Then
txtCompanyLocationID.DefaultValue = [Forms]![Inquiry]![cmbCompanyLocationID]
End If
If Me.OpenArgs = “AddReg” Then
txtCompanyLocationID.DefaultValue = [Forms]![Registration]![cmbCompanyLocationID]
End If
If Me.OpenArgs = “AddDetails” Then
txtCompanyLocationID.DefaultValue = [Forms]![InquiryDetails]![cmbCompanyLocationID]
End If
End If
End Sub

When the Contacts input form pops up enter the information for the new contact:

Now when you are done and you close the Contact form the ‘On Close’ event processes.

Private Sub Form_Close()
On Error GoTo Error_Handler

If Me.OpenArgs = “Tracking” Then
Forms!CompanyTracking!subCompanyLocations.Requery
Forms!CompanyTracking!subContacts.Requery
Else
If Me.OpenArgs = “Add” Then
Forms!Inquiry!cmbContacts = Me!ContactID
Forms!Inquiry!cmbContacts.Requery
Forms!Inquiry!subContactInfo.Requery
End If
If Me.OpenArgs = “AddReg” Then
Forms!Registration!cmbContacts = Me!ContactID
Forms!Registration!cmbContacts.Requery
End If
If Me.OpenArgs = “AddDetails” Then
Forms!InquiryDetails!cmbContacts = Me!ContactID
Forms!InquiryDetails!cmbContacts.Requery
End If
End If

Exit Sub

Error_Handler:
If Err.Number = 2450 Then
Resume Next
Else
MsgBox “Unexpected error on close of form Company Contact.”
End If
End Sub

Note that the code in the if statement ‘If Me.OpenArgs = “Add” Then’ sets the item you have entered in the list on the calling form. Now when you return what you have added is displayed in the drop down list.

Select that entry and the information you entered floods into the form:

 

Microsoft Office: MS Access 2000 Through 2016 and Office 365 & SharePoint

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016