Populate if another field is Yes, leave blank if No

[Diana van den Berg]

I would like help with the last part of the following query parameter:

=IIf([LMIPequalsUP]="Yes",[UserPassword],[this is the part I need help on])

I have the fields:

ID_Test
UserLogInName
UserPassword
LMIPequalsUP
LogMeInPassword

What I would like the query parameter to do is to poplulate the LogMeInPassword with the UserPassword if LMIPequalsUP is Yes (a text combo box) – (which it does), but the part I don’t know how to get correct is the next bit, which is: if No, then leave it blank so that the user can fill in the LogMeInPassword him/herself.

Thank you for any help.

[HansV]

If this expression is used in the Criteria line of the LogMeInPassword column of a query, use

=IIf([LMIPequalsUP]="Yes",[UserPassword],[Please enter your password])

The user will be prompted to enter the password if LMIPequalsUP doesn't equal Yes.
If you use the expression in another way, please provide more detailed information.

[Diana van den Berg]

I am so sorry. I said query parameter by mistake. I meant in the LogMeInPassword field of a form.

I am attaching a small database with what I am trying to do.

Thank you for any help.

[HansV]

You can't use a combo box for LogMeInPassword (and certainly not a combo box with choices Yes and No).
And you can't set the Control Source of LogMeInPassword to a formula: it is not possible to enter anything in a control if it has a formula.

So change LogMeInPassword to a text box, and set its Control Source to LogMeInPassword.
You need to create an After Update event procedure for the LMIPequalsUP combo box: if it is set to Yes, fill in the user password in the LogMeInPassword text box and lock it, otherwise clear the LogMeInPassword text box and unlock.
You also need an On Current event procedure for the form as a whole, to lock/unlock the LogMeInPassword text box as needed when the user moves to a different record.

Code: Select all

Private Sub Form_Current()
    Me.LogMeInPassword.Locked = (Me.LMIPequalsUP = "Yes")
End Sub

Private Sub LMIPequalsUP_AfterUpdate()
    If Me.LMIPequalsUP = "Yes" Then
        Me.LogMeInPassword = Me.UserPassword
        Me.LogMeInPassword.Locked = True
    Else
        Me.LogMeInPassword = Null
        Me.LogMeInPassword.Locked = False
    End If
End Sub

[Diana van den Berg]

Thank you so very much, Hans! I am thrilled with this.

I suspected that it would need code, but, not speaking Visual Basic, I wanted to try to see how far I could get without it. I would never have guessed that it would have been in either of those 2 places, nor would I have been able to guess in a million years how to translate what I wanted into code.

I actually need this (modified as regards the fieldname of course) for another pair of fields too, well one of that pair is also UserPassword (the other being EmailPassword), but I am assuming that looking at the code and because the specific code is in the LogMeInPassword field, that it won’t interfere with the same procedure with UserPassword and EmailPassword. The two procedures are in separate subforms, but from the same table. Am I correct about that, there should be no problem with that?

Thank you again, Hans!

[HansV]

Different subforms have different code modules, so the procedures won't bite each other.

[Diana van den Berg]

Thank you very much, Hans. I am glad then, that they are in separate subforms. Would they have "bitten" (lol!) each other if they had been in the same subform?

[HansV]

No, but since you can have only one On Current procedure for the form, you'd have had to combine the code for the two sets of controls in the On Current event procedure:

Code: Select all

Private Sub Form_Current()
    Me.LogMeInPassword.Locked = (Me.LMIPequalsUP = "Yes")
    Me.EmailPassword.Locked = (Me.EPequalsUP = "Yes")
End Sub

I assumed that you have a combo box EPequalsUP to specify whether the e-mail password is equal to the user password.

EPequalsUP would have its own After Update event procedure.

[Diana van den Berg]

Thank you very much for that Hans and for the combined code for the form.

I have added this last part to the rest in my PC Problems and Solutions db, (acknowledging you and Eileen's Lounge, of course) in case, either the user wants me to make a change, or in case I need something similar elsewhere.

[Diana van den Berg]

Hans, I made another error above. I had been working on the sample database and didn't check before I said they were on separate subforms. They are actually, indeed, on the same subform - just on different tab pages of it. So I used the combined On Current (and the AfterUpdate with the relevant name changes). However, although both procedures worked, the subform and main form behaved erratically and kept coming up with the error message:
Runtime error 94
Invalid use of Null

In case it is relevant, here are some (perhaps all) of the times the error message sometimes does and sometimes doesn't comes up:

* tabbing from EPequalsUP (sometimes)
* tabbing from LMIPequalsUP (sometimes)
* trying to put a 2nd PC (the subform on which both of the above are) on to a company (the main form) (always)
* trying to add a new company record (the main form) (always)

However, when I clicked on Debug, it didn't highlight either of the Null rows, but instead, the first line of the combined On Current.

I am wondering if I really need the On Current because I removed it all and no errors are coming up, although I realise that there are perhaps some situations that I haven't taken into account.

So, my question is, do I really need the On Current? If I do, is there a change that I can make to it so that the error message doesn't come up?

Thank you again for your help.

[HansV]

Hi Diana,

You do need the On Current event procedure, otherwise the text boxes might remain locked when they should be unlocked. You can change the code as follows:

Code: Select all

Private Sub Form_Current()
    Me.LogMeInPassword.Locked = (Nz(Me.LMIPequalsUP, "") = "Yes")
    Me.EmailPassword.Locked = (Nz(Me.EPequalsUP, "") = "Yes")
End Sub

Private Sub EPequalsUP_AfterUpdate()
    If Nz(Me.EPequalsUP, "") = "Yes" Then
        Me.EmailPassword = Me.UserPassword
        Me.EmailPassword.Locked = True
    Else
        Me.EmailPassword = Null
        Me.EmailPassword.Locked = False
    End If
End Sub

Private Sub LMIPequalsUP_AfterUpdate()
    If Nz(Me.LMIPequalsUP, "") = "Yes" Then
        Me.LogMeInPassword = Me.UserPassword
        Me.LogMeInPassword.Locked = True
    Else
        Me.LogMeInPassword = Null
        Me.LogMeInPassword.Locked = False
    End If
End Sub

That will - hopefully - take care of the "Invalid use of Null" error messages.

[Diana van den Berg]

Perfect! Thank you Hans.

I just had to change the row:

Me.LogMeEmailPasswordInPassword.Locked = False

to

Me.EmailPassword.Locked = False

I think that was just a slip.

Everything is working perfectly now. This is so exciting. Thank you!

[HansV]

Yes, sorry - I pasted a string but apparently forgot to remove the original one. I'll correct that now. Thanks for pointing out the mistake!

[Diana van den Berg]

It was a pleasure, Hans. When Hans makes a mistake, no matter how little, it makes me feel a tiny, weeny bit better about all of mine, lol!

Thank you for empowering me (again)!