Date validation problem

[Nvijayk]

I have 2 tables set up.The first table has among many,2 fields,one for 'Start Date' and the other 'End Date'.This is populated by Form 1.

The other table 2 has many fields and one field is 'Transaction Date'.This is populated by form 2.I would like to achieve 2 things.

1.When the user inputs a 'Transaction Date' in form 2,the date should be between 'Start Date' and 'End Date' as per available date in Table 1.A message should prompt the user if he enters an invalid date.

2.When the user does not input a date in form 2,it should allow that and for such a 'null' case the date should automatically take the 'Start date' in Table 1.

Is this possible at all and if so can anyone help me please.I would like details to actually execute it.

Many thanks

Vijay

 

[BlackKnight]

Hi,

Try this. I assume there will be only record in the startdate-enddate table since you didn't specify a linking field for it. If there is let me know and I can give you the new SQL statement.

Private Sub txtTransDate_BeforeUpdate(Cancel As Integer)
Dim conn As Connection
Dim rs As Recordset
Dim TransDate As Variant

Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open "SELECT * FROM table1", conn
With rs
TransDate = IIf(IsNull(txtTransDate), !Startdate, txtTransDate)
If TransDate < !Startdate Or TransDate > !EndDate Then
MsgBox &quot;Invalid date&quot;
Else
'do what you want.
End If
End With
rs.Close: Set rs = Nothing: conn.Close: Set conn = Nothing
End Sub

Have a good one!
BK

 

[Nvijayk]

Hi BK

Many thanks. I'll try this and hopefully should be ok. I'll come back for advise if I run into some problem.
As VB learner, I could have never written this but do understand it generally now that it is written.

regards

Vijay

 

[danvlas]

You can also try:

Private Sub TransactionDate_BeforeUpdate(Cancel As Integer)
If Len(TransactionDate)=0 Then
TransactionDate = DMin(&quot;StartDate&quot;,&quot;Table1&quot
ElseIf ((TransactionDate<DMin(&quot;StartDate&quot;,&quot;Table1&quot) Or _
(TransactionDate<DMin(&quot;EndDate&quot;,&quot;Table1&quot)) Then
MsgBox &quot;Invalid Date&quot;
Cancel=True
End If
Exit Sub
End Sub

 

[Nvijayk]

Kind Attn: Danvlas

Many thanks for your response.
I tried it and it works fine. However, when there is no date entered, it allows the transaction but does not update the TransactionDate field in the underlying table with the StartDate.If this will be a problem, I would be happy if it will not allow the form to be updated at all without a valid date in the form.

Please advise

Regards

Vijay