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.

Add Items to Drop down lists

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:

Active Labels for Dynamic Forms

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.

Labels as Links on Access Forms

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

label double click event