• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 43
  • Last Modified:

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).

4 Solutions
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

dbfromnewjerseyAuthor Commented:
Gustav BrockCIOCommented:
You are welcome!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now