Solved

Date Validation in Datagrid

Posted on 2002-07-11
22
288 Views
Last Modified: 2008-01-09
I've placed a Datagrid and an ADODC controls in a form and connected to the Database the ADODC and connected the ADODC to the Datagrid. In the Datagrid there are three Columns like Price, Startdate and Enddate.

Problem: Now the problem is that I need to validate the StartDate and Enddate. i.e in the first  Row I've entered a Start Date and an Enddate( Say the Start Date may "1/1/2002" and the End Date may "30/1/2002". Now , In the Next Row , if I'm entering a Startdate which lies in between Start Date and End Date in the above row(s)(Say the Start Date may be "15/1/2002", I need to validate the 'Current Start Date' with the Start Date and End Date of the above row(s).How do I validate it and in which condition or which event i've to vallidate the Start Date with the Start Date and End Date of the previous Row(s).

Urs
Shajahan
0
Comment
Question by:ShajahanPS
  • 10
  • 6
  • 3
  • +2
22 Comments
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
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

0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
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

0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
P.S.   x and y are dates...
0
 

Author Comment

by:ShajahanPS
Comment Utility
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
0
 

Author Comment

by:ShajahanPS
Comment Utility
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


0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
> 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!
0
 

Author Comment

by:ShajahanPS
Comment Utility
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
0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
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.
0
 

Author Comment

by:ShajahanPS
Comment Utility
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
0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
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.
0
 

Author Comment

by:ShajahanPS
Comment Utility
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
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Expert Comment

by:slavikn
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
0
 

Author Comment

by:ShajahanPS
Comment Utility
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
0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
I think I gave the correct answer, didn't I?
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
Comment Utility
PAQ'd and all 50 points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
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.
0
 

Expert Comment

by:SpideyMod
Comment Utility
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
0
 
LVL 49

Expert Comment

by:DanRollins
Comment Utility
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
0
 

Expert Comment

by:SpideyMod
Comment Utility
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.
0
 
LVL 1

Expert Comment

by:slavikn
Comment Utility
SpideyMod and Dan,

I agree with what you say.
Thanks for explaining.

Best wishes,
 Slavik.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now