Date Validation in Datagrid

You need to use the _BeforeUpdate event, this allows you to set the Cancel parameter which is passed ByRef to True to cancel the update before it is committed to the database.

Private Sub DataGrid1_BeforeUpdate(Cancel As Integer)
  'Here is your validation code:
  If Not ValidationOK Then
    Cancel = True
  End If
End Sub

If you want to check whether the new StartDate is between x and y use

If (DateDiff("d", x, NewStartDate) > 0) And (DateDiff("d", NewStartDate, y) > 0) Then
   'Update
      Else
   'Cancel
End If

P.S.   x and y are dates...

Dear TimCottee,

The code-piece you have given, is not working properly, b'coz the event is firing after a row change. But I need the event to be fired after the Cell Change of 'Start Date'.i.e. When i add a new Start Date it should comapre with the Start Date and End Date of the previous row. Could u pls help me in that?

The Validation i've given in the Code is here below:



Private Function ValidationOK()

On Error Resume Next

Dim rsSTDate As New ADODB.Recordset

Set rsSTDate = New ADODB.Recordset

If rsSTDate.State = adStateOpen Then rsSTDate.Close
rsSTDate.Open "Select * from MilkPrice", gadoconn, adOpenKeyset, adLockOptimistic
If Not (rsSTDate.BOF And rsSTDate.EOF) Then
   
    rsSTDate.MoveLast
    rsSTDate.MovePrevious
   
    Do While Not rsSTDate.EOF
        If grdDataGrid.Columns(1).Caption = "Start Date" Then
            If Not grdDataGrid.Columns(1).Text = "" Then
                If (CDate(grdDataGrid.Columns(1).Value)) >= rsSTDate.Fields("Stdt") Then
                    If Not IsNull(rsSTDate.Fields("Enddt")) Then
                        If Not (CDate((grdDataGrid.Columns(1).Value)) > rsSTDate.Fields("Enddt")) Then
                            MsgBox "Invalid Date Format. Please split the Previous Date and then continue", vbInformation, App.Title
                           
                        End If
                    End If

                End If
            End If
        End If
        rsSTDate.MoveNext
    Loop
End If

End Function



Regards
Shajahan

Dear slavikn,

Yaeh it worked a little, but its not working when I'm giving the NewStartDate as equal to  1 and less than that. Also need to validate the Start Date and End Date of the same row. That means the End Date should be greater than the Start Date. I also would like to know, in which event should I validate it? Could u pls help me in that?

regards
Shajahan

> but its not working when I'm giving the NewStartDate as equal to 1 and less than that...
What do you mean by giving 1 - is it a date??

> I also would like to know, in which event should I validate it...
You should use the BeforeUpdate event. If the data is incorrect, you have to set the Cancel parameter. This will not update the recordset. Ok?

PLEASE ACCEPT IF THIS HELPED!

Dear slavikn,

I'm sorry, it didn't worked. The NewStartDate equal to 1 means - yes it is a date.

Do u have any other options?

Regards
Shajahan

Dear Shajahan,

Sorry. I don't understand. How can "1" be a date? It should be "01/08/2002" or something like this. Right?

Best regards,
slavikn.

Dear Slavikn,
     Yes its just like that. I meant "1" for "1/1/2002" or something like this. And Could u check this and tell me??

With Regards
Shajahan

Oh, I am sorry. I forgot two words...
It should be:

If (DateDiff("d", CDate(CStr(x)), CDate(CStr(NewStartDate))) > 0) And (DateDiff("d", CDate(CStr(NewStartDate)), CDate(CStr(y))) > 0) Then
  'Update
     Else
  'Cancel
End If

CDate(CStr(something)) convert this "something" to a String value and then to a Date value. Now everything much be alright.

PLEASE ACCEPT IF THIS HELPED!

Best regards,
slavikn.

Dear slavikn,

    I'm very sorry....
It didn't worked. If we give a date greater than that of the Previous End Date , then also it is showing error msgbox that I've given Like "Invalid Date Entry".

Now What I'll do.
Pls Help me out...

Regards
Shajahan

Try to play with: >, <, >=, <= etc...
I will connect to the Internet in 2 days
and will check out for your comments.

Please give me the full sub's code. I want to check if it is correct.

Hi ShajahanPS,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept slavikn's comment(s) as an answer.

ShajahanPS, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer

Hi,

I've n't got any answers for my query. Wht is the problem?Could anyone here find an answer for this,Pls?

Regards
Shajahan

I think I gave the correct answer, didn't I?

SpideyMod,

I think that I gave a correct answer.
I expected my answer to be accepted
and to be given the points.

When I asked ShajahanPS what problems does he
still have with his question, he didn't answer.
It means that the problem was solved...

SpideyMod, will I be given these points, please?

Best wishes,
  Slavik.

SpideyMod,

I think that I gave a correct answer.
I expected my answer to be accepted
and to be given the points.

When I asked ShajahanPS what problems does he
still have with his question, he didn't answer.
It means that the problem was solved...

SpideyMod, will I be given these points, please?

Best wishes,
  Slavik.

Slavik,
My apologies, I normally post reasons why, if I disagree with a recommendation, but I missed this one.  My reasoning for PAQing the question with a refund is:
1) Last communication from ShajahanPS said it wasn't working.  
2) Your next response was to play around with different variable tests(<,>,<=,>=, etc), which in my opinion would not have satisfied me if I were asking the question.  

However, considering only those two above, I would probably have still awarded you the points.  But then after the recommendation was made...

3) ShajahanPS came back and verified that nothing has worked still to date.
4) You came back and didn't sound too sure that your response did help.

However, you will notice that I have put the original point value in my statement.  I usually do this whenever I disagree with the recommendation so that if the operation needs to be backed out, I have a record of the original point value.  I am still convinced that I made the correct decision to PAQ with a refund.  However, the points are still available if you are willing to continue to assist ShajahanPS to a successful conclusion.  It may be that your answer is indeed correct, but ShajahanPS doesn't understand, or you may need to do something else altogether to resolve it.  Either way, until ShajahanPS receives an answer that works, I can't see awarding the points.

I hope you understand.  If you are still unsatisfied with this answer, then I am happy to have my actions as a moderator reviewed by the site administrators, just say the word and I will have them review and post here.

Have a great day.

SpideyMod
Community Support Moderator @Experts Exchange

I would like to add that my recommendation is always based upon incomplete information and it assumes that the question is totally abandoned.  I agree with SpideyMod's decision.

When the Asker returns and selects an answer, or requests delete, or requests PAQ and refund, then my recommendation is meaningless.  ShajahanPS says that no comment helped him -- and that is the ultimate authority.

-- Dan

Thanks Dan.  IMO, your original recommendation was definitely the best alternative for an abandoned question.

I know I say thanks for your cleanup work, but sometimes I don't feel I say it enough.  You provide an extremely valuable service to the entire community here and I am very grateful.  You've done a fantastic job and without your volunteer work, this VB area in particular would be in serious hurt.

Slavik,
The administrative review is still available as both Dan and I are merely trying to do what we feel is best for all parties involved and I'm always happy to have another set of eyes look at it.

SpideyMod and Dan,

I agree with what you say.
Thanks for explaining.

Best wishes,
 Slavik.