Make a field required dependent on another field's value

[steveg0702]

I have an Access 2002 database with data integrity problems. There are 3 types of files I'm tracking:

"TYPE" = NEW, REOP, or REAS

My "REAS FROM" and "REAS FROM (CS ID#)" fields can be blank if the "TYPE" is NEW or REOP, but I need data to be required if the "TYPE" is REAS.

I'm not good at all with the VB coding, so please don't assume I know things about arrays and such when you're responding

Is there anyway to add code to "TYPE" like:

after update: [REAS FROM].Required=Yes
[REAS FROM (CS ID#)].Required=Yes


Thank you for any help! This will save me a few hours a week of cleaning the data if anyone can provide some help with this!

Thanks,
Steve

 

[MoLaker]

This is totally untested, but you can test for the conditions before update and that way stop the update if conditions aren't met.

In form's BeforeUpdate event, place this routine. It checks to see if Type = "REAS". If it does, it checks to see if either of the other 2 fields are null. If either is null, it cancels the update and pops a message to remind the user to complete the form.

Private Sub [i]formname[/i]_BeforeUpdate(Cancel As Integer)
     If me!type = "REAS" then
        if isnull(me[REAS FROM]) or isnull(me![REAS FROM (CS ID#)] then
            cancel = true
            dim msg as string
            'msg s/b message you want displayed
            msg = "The type is 'REAS'.  ID and From must be completed"
            msgbox(msg)
        end if
     end if
End Sub

 

[steveg0702]

MoLaker, thanks for the suggestion. I couldn't get that to work, though. Maybe part of the problem is that the "TYPE" input field comes first and the "REAS FROM" and "REAS FROM (CS ID#)" input fields come after.

Is there anything I can do to add some sort of validation rule when the user clicks the Save button?

Thanks,
Steve

 

[MoLaker]

Click what 'Save' button? Are you using a bound or unbound form? With a bound form using a 'Save' button is usually not necessary. Please explain how your form is constructed before we go any further.

 

[steveg0702]

MoLaker,

Sorry about that. I'm not sure what the difference is between bound and unbound forms. The form input is meant for new data, though, if that helps. It's strictly new input. Users are not updating existing records.

More about the form:
TYPE is a combo box that selects from a short list I give it of the choices NEW, REOP, or REAS.

Next in my form is REASFROM, which is a combo box that selects from a different short list of choices.

The last input field is REAS FROM (CS ID#) which is just a text field.

When all of this is complete, the user clicks on a SAVE button that closes the form and saves the record to a table (TRIAGE_LOG).

Currently, the SAVE button calls the following function:

Private Sub SaveButton_Click()
On Error GoTo Err_SaveButton_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close
    
Exit_SaveButton_Click:
    Exit Sub

Err_SaveButton_Click:
    MsgBox Err.Description
    Resume Exit_SaveButton_Click
    
End Sub

I hope that helps.
Thanks,
Steve

 

[MoLaker]

That would seem to indicate the form is 'bound' - which means the controls are assigned, if you would, to specific fields in a table or query.

Perhaps the problem is in field names since I see two different spellings of one field

My "REAS FROM" and "REAS FROM (CS ID#)" fields can be blank if the "TYPE" is NEW or REOP, but I need data to be required if the "TYPE" is REAS.

and

Next in my form is REASFROM, which is a combo box that selects from a different short list of choices.

Where I refer to field control names in my code MUST match the names you gave them in the form - precisely.

if isnull(me[red][b]![/b][/red][REAS FROM]) or isnull(me![REAS FROM (CS ID#)] then

Certainly, change the names in the code to match the actual names. Also, I noticed I left out a "!" sign in my code. I show it corrected in red in the code above.