Make a field mandatory if one of a series contains a value

[Maclain]

Hi all,

As per the subject I'm attempting to make Text104 mandatory if any of a series of other fields contains a value.

So far I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim addlength As Integer
addlength = Len([Text106] & [Text108] & [Text110] & [Text112] & [Text114] & [Text116])
If addlength > 0.5 Then
If IsNull([Text104]) Then
MsgBox "Accounts address Line 1 cannot be blank", vbOKOnly, "Form Not complete"
[Text104].SetFocus
End If
End If
End Sub

and this:

Private Sub Text106_AfterUpdate()
Call CheckAccountsAddress
End Sub
Private Sub Text108_AfterUpdate()
Call CheckAccountsAddress
End Sub
Private Sub Text110_AfterUpdate()
Call CheckAccountsAddress
End Sub
Private Sub Text112_AfterUpdate()
Call CheckAccountsAddress
End Sub
Private Sub Text114_AfterUpdate()
Call CheckAccountsAddress
End Sub
Private Sub Text116_AfterUpdate()
Call CheckAccountsAddress
End Sub
Private Sub CheckAccountsAddress()
If IsNull(Me.[AccountsAddress Line1]) Then
MsgBox "Acc Address Line 1 cannot be empty", vbOKOnly, "Address Incomplete"
[Text104].SetFocus
End If
End Sub

The text after update events work fine, so if a user inputs data into any of the text boxes and text104 is null it warns and sets focus.

The problem is you can then leave text104 without putting a value in.. which is where i'm stuck.

Appreciate the help

Edit: I also get an invalid use of Null when I try and enter design view, this is associated with the form BeforeUpdate line

addlength = Len([Text106] & [Text108] & [Text110] & [Text112] & [Text114] & [Text116])

 

[Minty]

I would suggest for your own sanity that you change your control names to something more meaningful.
In a months time text104 won't mean a thing to you. Something like txtAddr1 will.

Your code would be better like this - the use of the Me. means that the forms objects are being referred to.

    Dim addlength        As Integer
    
    addlength = Len(Me.Text106 & Me.Text108 & Me.Text110 & Me.Text112. & Me.Text114 & Me.Text116 & "")
    If addlength > 0.5 Then
        If len((Me.Text104 & "") < 1 Then
            MsgBox "Accounts address Line 1 cannot be blank", vbOKOnly, "Form Not complete"
            cancel = True
            Me.Text104.SetFocus
        End If
    End If

By Adding a "" to the end of the string you are checking you avoid the null problem.

 

[Maclain]

Thanks Minty,

I would change the controls, but the database is over 12 years old and I daren't change legacy items in case I break something else!

New additions to the system are much, much nicer!

Thanks for the amended code, however I still have an issue:

After the .setfocus of sub CheckAccountsAddress the user can just leave text104 without entering any information.

The form after update sub only seems to kick into play when the user tries to close the form, which will just end up with the user clicking the Yes without reading the error that tells them they will be unable to save the record.

How can I force the user to input text104 before this point?

 

[bob fitz]

IMHO the forms Before Update event is the best place for validation code. The update can then be canceled if the validation fails

 

[Minty]

The code was in the before update event I thought? Hence me putting in the
Cancel = True.

I wouldn't bother with the CheckAccountsAddress - you are achieving the same with the before update event checks.
I don't think they can leave the form without something entered in that control.

 

[Maclain]

Hi Minty,

I've removed the checkaccountsaddress. Thanks,

What I'm trying to achieve is continuity in a separate billing address for clients. So a client has a main address, and then if they require, we can add in an accounts address.

The problem is this is on occasions only being partly completed, so to try and combat this I've added in some address checking to the reports, all based on Text104 having a value.

If a user receives instruction to add in a new accounts address, theoretically they could input in some fields leaving text104 blank.

When closing the form, the code in before update catches the missing values in text104 and gives the user the message, access then pops up with this record cannot be saved at this point with an option to exit anyway.

knowing some of our users, they will click "yes" without reading, and assume the information is entered, and the task complete when infact the changes they have made have not been saved.

Is there something I can add in to the code to stop the close procedure and set the focus back into text104 so the form cannot be closed without this validation?

Edit: leaving in the checkaccountsadress on each of the other fields gives the user numerous chances to complete text104

 

[Minty]

If they exit I don't think their changes would be saved - which would at least prevent rouge entries.

There are some links in this thread that might get you to the place you need
https://access-programmers.co.uk/forums/showthread.php?t=104604

Basically if you want to ensure an entry you have to force the user to only close the form in a prescribed fashion.

And this thread https://access-programmers.co.uk/forums/showthread.php?t=104604

Edit: And I seem to have missed that you should Exit Sub after setting the focus back to the address control on the form.

 

[arnelgp]

as Minty pointed add, add Cancel=True to your code.
also you must add empty string "", to the
concatenation of Textbox ([Text108] & [Text110] & ... & "".
if all this textboxes are empty you will get
runtime error, since you cannot get the
Len() of Null.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim addLength As Integer
    addLength = Len([Text106] & [Text108] & [Text110] & [Text112] & [Text116] & "")
    If addLength > 0 And Len([Text104] & "") = 0 Then
        MsgBox "Accounts address Line 1 cannot be blank", vbOKOnly, "Form Not Complete"
        Cancel = True
        [Text104].SetFocus
    End If
End Sub

 

[Maclain]

Hi Both,

Thanks for the info, I've included the Cancel = true and Exit sub as mentioned.

You're correct in that the changes will not be saved if the user exits the form, this is the problem as the user will/may think the changes HAVE been saved.

I'll have a look into the resources you provide.

Thanks again for you help, much appreciated!