Date validation rule (1 Viewer)

shafara7

Registered User.
Local time
Today, 16:06
Joined
May 8, 2017
Messages
118
I have 3 forms which displays the dates of the tests that we are doing. The dates of each following test must be on the same/later date as before. All dates are from the same table.
For example, I have 5 Tests.
Date of Test 1
Date of Test 2 must same or after Test 1
Date of Test 3 must same or after Test 2
Date of Test 4 must same or after Test 3
Date of Test 5 must same or after Test 4

The subform in FormA displays the dates for Test 1 - Test 5.
The subform in FormB displays only the dates for Test 4 and Test 5.
The subform in FormC displays only the date for Test 3.

The solution from Microsoft support said that I should make a validation rule to the fields.
So on FormA, I did a Validation Rule for each dates like this: [Test2]>=[Test1].

But the problem is, I also have Form B and Form C where people can input the dates and this validation rule does not applies on these Forms B and C.

Is there any way I can put a warning sign on Form B and C, if they entered the dates wrong?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,175
on both Subforms of forms FormB and FormC you must include ALL the Test dates of your table to your subforms so that you can do the validation. But don't worry, we will not show the extra dates to the consumer of your form by hiding them.

to hide them on FormB, on your subform's Load Event:

Private Sub Form_Load()
Me.Test1.ColumnHidden=True
Me.Test2.ColumnHidden=True
Me.Test3.ColumnHidden=True
End Sub


on FormC, on your subform's Load Event:

Private Sub Form_Load()
Me.Test1.ColumnHidden=True
Me.Test2.ColumnHidden=True
Me.Test4.ColumnHidden=True
Me.Test5.ColumnHidden=True
End Sub

now you can validates each field that you like since other fields are now present.
 

shafara7

Registered User.
Local time
Today, 16:06
Joined
May 8, 2017
Messages
118
Thank you arnelgp for your suggestion.
I did what you told, except that it does not perform the validation rule and the validation text does not appear.
On FormB, the subform that shows the dates is not editable and I don't know why. There is also a background colour that shows whether the dates are on track or not. The field for the dates is not activated and locked, and when I activated it, the colour disappear, but still it is not editable. I have checked the Background Auto Formating, it does not appear to be red or green
See the screenshot below.

Also, I have created a button, so that an input window will appear and I can enter my dates there. When I do that, the validation rule and text also not showing up.

Is there any way to make the dates field editable?
 

Attachments

  • FormB.jpg
    FormB.jpg
    93.6 KB · Views: 74

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,175
so sorry, i thought you are working on Datasheet view.
for Contiuous Form, remove the code i gave you but instead, set the Visible property of the Test dates that you added to No. this will hide these test dates.

check your validation rule again if it is correct. otherwise, i suggest using vba to show the message. example of FormB, on Test4 textbox's before update event:

Private Sub Test4_BeforeUpdate(Cancel As Integer)
If Me.Test4 < Me.Test3 Then
MsgBox "Test Date Must be on or after Test3"
Cancel=True
ElseIf Me.Test4 > Me.Test5 Then
MsgBox "Test Date Must be on or before Test5"
Cancel=True
End If
End Sub
 

shafara7

Registered User.
Local time
Today, 16:06
Joined
May 8, 2017
Messages
118
Thank you. I tried it too but the validation rule/text still does not appear.
I think it is because the subform is uneditable.
Do you know why it is uneditable? I have set it to allow addition and dynaset (inconsistent). The only thing that is editable is the Test 3 column that I just added.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:06
Joined
May 7, 2009
Messages
19,175
If your subfirms recordsource is based on a query add the additional fuelds to your query to make it updatable.
 

Users who are viewing this thread

Top Bottom