Option Group on a Form

I have an Access form whose record source is MyTable.

On the form, I have a textbox named Pay_Date used for selecting/entering a date value.

It does not require that a value be entered (to be explained below).

The Pay_Date textbox's control source is the Pay_Date field whose data type is Date/Time and is in MyTable.

There are 4 possible scenarios that can exist as far as Pay_Date goes.  They are:

1. The date is known, therefore the user can select/enter it in the Pay_Date textbox.
2. The date is missing.
3. The date is not legible.
4. The date is not applicable.

Because I cannot set up a situation (which would be the ideal) where the Pay_Date textbox alone
can be used to either enter a date or select "missing", "not legible" or "not applicable"  
(because its data type is Date/Time), I'm going to try what seems to me to be the next best thing.

That is:

Have the user either enter a date value into the Pay_Date textbox or, if applicable, leave it blank
and make a "Missing", "Not Legible" or "Not Applicable" selection from an option group placed to the
right of the Pay_Date textbox.

Which leads me to the following:

I need code that will make the option group inactive (and none of the buttons populated) if the user
selects/enters a value in the Pay_Date textbox


If the Pay_Date textbox is left blank or populated and then blanked out (if for example the user enters
something and then erases/deletes it), I want to have the option group activated.  Of course from the start
both the Pay_Date textbox and the option group will need to be active.  At that initial stage, I don't want
any of the buttons to be selected (because it is not known yet whether the user will enter a date or make
an option group selection).

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
Try: In te textbox after update event
Private Sub txt1_AfterUpdate()
    If Len(Nz(txt1)) = 0 Then
        optgroup.enabled = True
        optgroup.enabled = False
    End If
End Sub

Open in new window

You also need code in the Form's BeforeUpdate event to ensure that either a date was entered or one of the options from the option group was chosen but not both.  Remember, NO control events will fire for a control if that control never receives the focus.
If IsDate(Me.txtPay_Date) then
    If Me.optPay & "" = "" Then
        Cancel = True
        MsgBox "Only the date or an option may be selected but not both.",vbOKOnly
        Exit Sub
    End If
    If Me.optPay & "" = "" Then
        Cancel = True
        Msgbox "Either the date or an option must be entered.",vbOKOnly
        Exit sub
    End If
End If

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
Well your first thinking is not entirely wrong...if you use the Form's Error Event you can trap the error due to entering unrelated values to a bound field and thus triggering  what you want if PayDate is not a date
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2113 Then
if Screen.ActiveControl.Name = "nameOfControl" then  '<-- Here you will check if the error was triggered from the control you want
    Me.Undo  '<--- Erase the offending value
    Response = acDataErrContinue
' Now here you can put the code you wanted to activate the section you wanted
End if
End If
End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
You can and should make this a bit more intuitive to allow for maximum flexibility for the user when entering data.

With a bit of code, there is no reason to disable anything - just follow the user's intention, and set values and controls accordingly.
If attached a demo to illustrate this. The entry form looks like:

and these few lines of code controls it all without disabling anything:
Option Compare Database
Option Explicit

Public Enum PDOption
    Known = 0
    Missing = 1
    NotLegible = 2
    NotApplicable = 3
End Enum

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.PayDateSelect.Value = PDOption.Known Then
        If IsNull(Me!PayDate.Value) Then
            Me.PayDateSelect.Value = PDOption.Missing
        End If
    End If
End Sub

Private Sub PayDate_AfterUpdate()

    Dim Value       As Variant
    Dim Selection   As PDOption
    Value = Me!PayDate.Value
    If IsNull(Value) Then
        Selection = PDOption.Missing
        Selection = PDOption.Known
    End If
    Me!PayDateOption.Value = Selection
End Sub

Private Sub PayDateSelect_AfterUpdate()

    Dim Selection   As PDOption
    Selection = Me!PayDateSelect.Value
    Select Case Selection
        Case PDOption.Known
        Case Else
            Me!PayDate.Value = Null
    End Select
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
Gustav BrockCIOCommented:
You are welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.