Checkbox=True make another field requried? (1 Viewer)

Hello,

I have been searching for awhile and can't quite make this work for me. Would anyone have an idea why.

And before I go on, everything I have read says to put the code on the Form's "Before Update" event but I have Audit Trail code there and can't seem to add additional code or maybe I am just not doing it correctly. I have some code in the DB (audit trail being the big one), but that was with help from people such as yourself.

Here is my issue:

I have a form, and a field called "PendingAction" which is a checkbox and if it is set True, I want to make the "PendingActionDate" required meaning they have to put in a date or they can't exit but can't seem to make it work.

I would like to use the OnClick property of the Pending Action checkbox…is that doable?

Any help would be appreciated…I have tried.

Currently, I have the below and it will give a message when I check the box but doesn't require the user to fill in the date before exiting the form.

This is on the OnClick Event of the checkbox:

Private Sub PendingAction_Click()
If Nz(Me.PendingActionDate, "") = "" Then
Cancel = True
MsgBox "Pending Action Date is required, please fill in the date.", vbExclamation, "Data Required"
End If

End Sub

Thank your for any help.

Lilly

Hi Lily. Another way to approach this is to use the LostFocus event to check if the field is empty as well as if the checkbox is checked. If the field is required based on matching conditions, then you should be able to keep the focus on the same control. Also, to make sure the user doesn't skip the field, in the AfterUpdate of the Checkbox, you can send the focus back to the required field.

Lilly420 said:

everything I have read says to put the code on the Form's "Before Update" event but I have Audit Trail code there and can't seem to add additional code or maybe I am just not doing it correctly. I have some code in the DB (audit trail being the big one), but that was with help from people such as yourself.

Click to expand...

That is my first thought about where the code should go, so maybe you should post your attempt at adding it. I'd say this test would go first, and if it fails set cancel = true and skip the audit trail code.

Hi,

Thank you both.

I do not know what to do on the LostFocus even...anyway you can walk me thru with what I gave you above?

So I tried to add the code for the field in the code window before the audit trail code that I am using. I found this code on line and tried to use my fields to make it work...not sure if it is correct. AND I need the Audit Trail code to work as it is tracking user's changes on the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Pending_Action And Len(Trim(Me.Pending_Action_Date.Value & vbNullString)) = 0 Then
MsgBox "Pending Action Date is required."
Cancel = True
End If
End Sub
___________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call Audit_Trail(Me, "OpenRespID", OpenRespID.Value)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit
End Sub

I tried putting it on the top and bottom and get an error "Ambiguous name dedected" and it is on the second Private Sub Form_BeforeUpdate (Cancel As Integer).

Thank you for any help.

Lilly

You can only have one event; you'd add code to the existing event. Like:

OM goodness, it worked...thank you so much...I just had to add End If before the End Sub and it works...thank you so much for your help, you are a life saver. So it is Else that separates the code on an event is that correct?

Lilly

Hi Lilly. Glad to hear you got it sorted out. Good luck with your project.

Lilly420 said:

OM goodness, it worked...thank you so much...I just had to add End If before the End Sub and it works...thank you so much for your help, you are a life saver. So it is Else that separates the code on an event is that correct?

Lilly

Click to expand...

You added End If where? The code posted was complete, unless I'm blind. Adding an End If would have thrown an error. It shouldn't have been moved, either.

Else just provides a logical branch in the code. In this case "if the validation fails tell the user and stop else call the audit code".