Problems imposing constraints on a form

[DanEaton]

I have a data entry form. It has many fields describing a catalog and how it is filed. Many of the catalogs we receive are in different formats. There are catalogs, cd's and what we call "datasheets". Datasheets (as the name suggests) are simply single, or groups of sheets with product-specific data. When we file these datasheets, we do so alphabetically within binders corresponding to the vendor name on their cover. In other words, we have binders for every letter of the alphabet. On my form, I have a list box that constrains the user to select from either of these three formats. I have it so that if datasheet is selected, the focus jumps to the binder combo box. One problem I have noticed is, when people are entering in many catalogs, datasheets and cd's they may forget to choose the binder that it's in if datasheet is the format. This screws up our labels, resulting in mis-filing. I can't make the binder field required because it isn't; catalogs would not fit in this category. I do, however, want to make them required if datasheet was selected as the format. I want a msgbox defined by myself to warn users if they overlook the binder field, and I want the required property set to true if the datasheet is selected. I've written some code already, but nothing has worked (not that I'm exceptionally good at VBA). Anyway, I'll show you what I tried, I won't be surprised if it's got bad syntax or bad logic. I would love some help. thanks

(after update event)

Dim Msg, Style
Me!cboBinder.Required = True
    If Me!cboBinder = Null Then
        
        Msg = "It is required that you enter which binder the datasheet will be filed under!"
        Style = vbInformation
        
        If MsgBox(Msg, Style, "Binder Requirement") = vbOK Then
        
            Me!cboBinder.SetFocus
            
        End If
        
    End If

Dan Eaton
deaton@caemachinery.com

 

[SBendBuckeye]

Dan,

Put your logic in the On Exit event for the binder. The On Exit code includes a Cancel parameter which you can set to True. Once you have set it to True, it will automatically return the focus to the Binder control regardless of user tabs, mouse clicks, etc. Use something like the following:

If DataSource = "Data Sheet" Then
If IsNull(BinderControlName) Or _
IsEmpty(BinderControlName) Or _
BinderControlName = vbNullString Then
Cancel = True
MsgBox "Binder required if using DataSheets"
Exit Sub
End If
End If

You might want to play around with IsNull, IsEmpty, etc. to see which ones you really need.

The Before Update event also returns a Cancel parameter, but I suggested On Exit because the user may not change anything, in which case the Before Update event would not fire.

Good Luck!

 

[DanEaton]

Thank you. It works just as I'd liked.

Take it easy Dan Eaton
deaton@caemachinery.com