Make a text box required if a different text box is not null

[charlie442]

Hi gurus,

I am trying to figure out how to make a text box value required only if another text is completed. Lets call them [txt1] and [txt2]. So if [txt1] is filled in then [txt2] becomes necessary for the user of the form to fill in too. I am sure this must be easy enough but my limited knowledge has led me into a corner. I have tried entering various expressions in the validation rule property as well as numerous attempts at coding it in the VBA window but alas.
Any help greatly appreciated

Thanks
Charlie

 

[Galaxiom]

You are probably running into trouble with the difference between Null and the Null String.

Definitely the problem if you are using code like:

If txt1 = "" Then

That won't find an empty textbox because its value is Null

This is one of the more common expressions that finds both Null and Null String.

If Len(txt1 & "") = 0 Then

 

[charlie442]

Thanks Galaxiom

I was using the former above. I am still unsure of what code should follow the latter though. I have tried

If Len(txt1 & "") = 0 Then
Len(txt2 & "") <> 0

But then it is telling me Expected Identifier?

 

[Galaxiom]

If Len(txt1 & "") <> 0 Then
   If Len(txt2 & "") = 0 Then
      msgbox "No way Jose"
   End If
End If

 

[Galaxiom]

BTW This is often done in the Form_BeforeUpdate Event Procedure.

Along with the message, another line cancels the update.
Cancel = True

It can be also be done in the AfterUpdate of txt1 by simply checking for a value. Then your code is designed to move the focus automatically to txt2 if there is a value. The AfterUpdate of txt2 then simply refuses to let Jose leave without entering a value.

It is really about defining what you what the user experience to be. Do you want them forced to enter immediately or wait until they try to save the record. Then think through the logic.