Search code examples
vbams-access

How to create like an option group on a subform in MS Access so that only one subrecord can be selected at a time?

let's say

I have a customer form and a subform of customer addresses. I would like to be able to mark only one subrecord as the default so that it is used for all mailings. How can I do this?

Solution

  • There are many possibel solutions for this!

    One is to have a combobox in the main form, where you can select a record from the subform.

    See the green marked field in the picture below.

    enter image description here

    ALTERNATIVE 2: Use a checkbox to mark the default mailing address.

    enter image description here

    With this one you may want to make sure that not two addresses can be checked. That can be done in the BeforeUpdate event for the checkbox with code like:

    Private Sub DefaultAddress_BeforeUpdate(Cancel As Integer)
    ' If checkbox is set to true
    If Me!DefaultAddress Then
        ' If there if an other address already chosen
        If DCount("*", "CompanyAddresses", _
        "DefaultAddress AND CompanyID = " & Me!CompanyID) > 0 Then
            Cancel = True ' Cancel this checkbox
            MsgBox "You have to uncheck the previous default address " & _
            "before selecting a new one!", vbExclamation, "Set default address"
        End If
    End If
End Sub

    ALTERNATIVE 3: Is like alt 2 but with code that automatically uncheck previous checked address, before selecting a new default address. This option needs more advanced VBA-code.

    ALTERNATIVE 4: Is like alt 2 but instead of a checkbox you can use combobox like the one in the picture labled 'Befattning'.