Form Data Validation off of other form data

[Colombo]

Hey All,

I've been parusing the forums the past day or so, since I've been working on a customer support database. I've found some of my answers, however I'm struggling with one in particular.

Here's the setup. I have a pretty typical customer support form, gather all the typical info. Then I have a check box to indicate whether the issue is closed or not. I also have a closed date text & resolution description text box.

What I would like to do is make it so the record can not be saved if the closed checkbox is checked, and the closed date & resolution description boxes are blank.

I have messed around with the "On Exit", "Before Update" commands in the Event tab.

I am not too strong on VB or programming commands/syntax. But working with another coworker, we came up with this.

Private Sub ClosedDate_Exit(Cancel As Integer)

If Closed = -1 Then
  If IsNull(ClosedDate) Then
    MsgBox "you blew it"
    Cancel = True
    Exit Sub
  End If
End If

End Sub

This works, but if you click in the closed date box, you can not get out unless you type a date in......also....you can go back and uncheck the closed box...in which case you end up with a closed date and no closed checkmark.

I've come to a brick wall here....

Any help or suggestions would be extremely helpful.

Josh

 

[Fornatian]

It appears that you have a duplication of effort in your field setup, is it true that a field should always have a checkbox ticked if there is a date in the closed date? If so you don't need to record two fields. For data management use DateClosed and use a query to extract the empty(open) closeddate fields indicating an empty record.

It is typical to use the forms before update event to validate form data before a save - and yes you can get into an endless prison loop of you put your validation on a field that you RELY on the user being in before exiting the form.

 

[Colombo]

It appears that you have a duplication of effort in your field setup, is it true that a field should always have a checkbox ticked if there is a date in the closed date?

Yeah I guess you could say that. I'll probably get rid of the check box, and just keep the date closed & resolution description boxes there. that'll save a little headache on trying to link the three boxes together.

Thanks

josh

 

[Colombo]

next question is.....I know I can just put an Is Null in the criteria field for DateClosed, however I would like to us a prompt box for the user where it says, enter yes for closed, no for open, and blank for all.

When I was using the closed check box to determine whether it was closed or not, I had used an IIF statement to conver the 0 or -1 into a yes or no.

I figured I could just change the value to null of what the IIF field is equal to and it would work. But it's not working.

Sorry if this is a mundain question.....but access and I have are not getting along too well now...hehe

 

[Seasider]

Checkbox and required date

I had a similar problem to yours. I had a checkbox on a form and a date field where I wanted to force the user to enter a date when the checkbox was checked. Conversely, I did not want the user to be able to enter a date if the checkbox was not checked.

After many hours of struggling, I finally worked out a solution that works great for me. Following is the code for both controls with comments that should give you the idea of how it works. Note that the code goes in the Exit event of each control.


Private Sub DAG_Appvd_Exit(Cancel As Integer)
'DAG_Approved is a checkbox user can check when Guidelines have been approved
'If the checkbox is checked, that is -1, they display a message to user that
' a date of approval must be entered in DateDAG_Appvd field
If Me.DAG_Appvd = -1 Then
MsgBox "You must now enter the date of approval", vbOKOnly, "DATE REQUIRED"
' move to the DateDAG_Appvd field to enter a date
DoCmd.GoToControl "DateDAG_Appvd"
Else
' if the checkbox in not checked, that is 0, then do not go to the DateDAG_appvd
'field and set focus to the next field in the tab order
If Me.DAG_Appvd = 0 Then
Me.DateDAG_Appvd = Null
Me.BudgetAmount.SetFocus
End If
End If
End Sub

Private Sub DateDAG_Appvd_Exit(Cancel As Integer)
'if the DAG_Appvd is checked and user entered a date in the date approved field
'then use a requery to immediately update both fields
If Me.DAG_Appvd = -1 And Not IsNull([DateDAG_Appvd]) Then
Me.DAG_Appvd.Requery
Me.DateDAG_Appvd.Requery
Else
'if DAG_appvd is checked but user does not enter an approval date
'then display a message and cancel the event, including resetting
'the DAG_Appvd field back to 0. Focus is set to the next field in the tab order
If Me.DAG_Appvd = -1 And IsNull([DateDAG_Appvd]) Then
MsgBox "You did not enter an approval date!"
Me.BudgetAmount.SetFocus
DoCmd.CancelEvent
Me.DAG_Appvd = 0
Else
'If user tries to enter a date of approval while DAG_Appvd is not checked
'then display a message and cancel the whole event and set focus back to
'the DAG_Appvd field and set DAG_Appvd to unchecked
If Me.DAG_Appvd = 0 And Not IsNull([DateDAG_Appvd]) Then
MsgBox "DAG Approved must be checked before you can enter a date", vbOKOnly, "Must Check DAG Approved"
Me.DateDAG_Appvd = Null
Me.DAG_Appvd.SetFocus
DoCmd.CancelEvent
End If
End If
End If
End Sub

Hope this is of some use to you. Good luck.

Seasider