Solved

Date Validation in Datagrid

Posted on 2002-07-11
22
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
  • +2
22 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 7148489
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
ID: 7149949
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
ID: 7149951
P.S.   x and y are dates...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ShajahanPS
ID: 7153396
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
ID: 7153758
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
ID: 7154030
> 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
ID: 7156736
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
ID: 7156872
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
ID: 7158580
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
ID: 7159034
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
ID: 7164475
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
 
LVL 1

Expert Comment

by:slavikn
ID: 7166883
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
ID: 8002352
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
ID: 8005836
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
ID: 8011333
I think I gave the correct answer, didn't I?
0
 

Accepted Solution

by:
SpideyMod earned 0 total points
ID: 8095350
PAQ'd and all 50 points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0
 
LVL 1

Expert Comment

by:slavikn
ID: 8098718
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
ID: 8098725
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
ID: 8098960
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
ID: 8099089
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
ID: 8099254
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
ID: 8105833
SpideyMod and Dan,

I agree with what you say.
Thanks for explaining.

Best wishes,
 Slavik.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

726 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