if date exist in datagrid conncted to database..ned help plz

[vonnoy]

im having problm with my date function.
i want to record date in database only one
but my code do not function.
this checks attendance once in a day. date must not double
plz help me.

Sub search()
'search if id registered
With ado
    .ConnectionString = connectdb
    .RecordSource = "Select * from info where id = '" & Text2 & "'"
    .Refresh


    If .Recordset.RecordCount > 0 Then
    Text3 = .Recordset!id
    Text4 = .Recordset!fname
    Text5 = .Recordset!lname
    Text6 = Date

'goto save record
    Call add


Public Sub add()
'save record
adoadd.Refresh

    If Text2.Text = adoadd.Recordset.Fields("date") Then
     MsgBox "duplicate date", vbInformation, "invalid"
     Text2 = ""
     Text3 = ""
     Text4 = ""
     Text5 = ""
     Text6 = ""
     Else
    With adoadd.Recordset
    .AddNew
    !id = Text3
    !First_Name = Text4
    !Last_Name = Text5
    !Date = Text6
    .Update
    MsgBox " save", vbInformation, "Done"

Text2 = ""
Text3 = ""
Text4 = ""
Text5 = ""
Text6 = ""
End With
End If
DataGrid1.Refresh

End Sub

[tinstaafl]

Have you checked the format of the relevant date strings? If the formats don't agree the search function won't recognize the same date. You might be better off casting each one to a date type to compare them.

[vonnoy]

ok, then how am i supposed to do it?i dont have any idea.

[tinstaafl]

One way, maybe the easiest, to check the format is to put a break on line 23 and examine the relative strings in the immediate window. Another way is to load those strings into a messagebox just before line 23 and display them that way. To use date type variables:

            Dim Date1, Date2 As Date
            Date1 = Date.Parse(Text2.Text)
            Date2 = Date.Parse(adoadd.Recordset.Fields("date"))
            If Date1 = Date2 then

[vonnoy]

i cant figure out.
can you just use my code above?

[tinstaafl]

Try putting the code block I sibmitted above in to replace line 23 in your code and see if that clears it up.

[vonnoy]

do you mean like this?
cause it says compile error: end of statement.
thats what it says
where should i put it?

Public Sub add()
Dim Date1, Date2 As Date
'save to attendance record
adoadd.Refresh


    Date1 = Date.Parse(Text6.Text)
    Date2 = Date.Parse(adoadd.Recordset.Fields("date"))
    If Date1 = Date2 Then
     MsgBox "duplicate date", vbInformation, "invalid"
     Text2 = ""
     Text3 = ""
     Text4 = ""
     Text5 = ""
     Text6 = ""
     Else
     Exit Sub
    With adoadd.Recordset
    .AddNew
    !Barcode = Text3
    !First_Name = Text4
    !Last_Name = Text5
    !Date = Text6
    .update
    MsgBox "Attendance Checked", vbInformation, "Done"

Text2 = ""
Text3 = ""
Text4 = ""
Text5 = ""
Text6 = ""

    End With

DataGrid1.Refresh

[tinstaafl]

You have it in the right place. But your code is missing a bunch of statements. There's no End Sub and no End If. If your code is incomplete it's not going to run.

[vonnoy]

i just forgot to copy the end statements.
these two codes got color red, like its already error from the start.
this is what it looks. pls help with this.

Date1 = Date.Parse(Text6.Text)
Date2 = Date.Parse(adoadd.Recordset.Fields("date"))

[tinstaafl]

The picture you're showing is from the program running and entered a break. Did an error cause the break? If so the details from the error window at the bottom can help to narrow it down.

[AndreRet]

Use the format function to first add a date and check the date accordingly....

Sub search()
'search if id registered
With ado
    .ConnectionString = connectdb
    .RecordSource = "Select * from info where id = '" & Text2 & "'"
    .Refresh
    If .Recordset.RecordCount > 0 Then
    Text3 = .Recordset!id
    Text4 = .Recordset!fname
    Text5 = .Recordset!lname
    Text6 = Now ''Changed to now, this is normally in a proper date format...
'goto save record
    Call add
Public Sub add()
'save record
adoadd.Refresh
    Dim chkDate As Date
    chkDate = Format(Text2.Text, "yyyy/mm/dd")

    If chkDate = adoadd.Recordset.Fields("date") Then
     MsgBox "duplicate date", vbInformation, "invalid"
     Text2 = ""
     Text3 = ""
     Text4 = ""
     Text5 = ""
     Text6 = ""
     Else
    With adoadd.Recordset
    .AddNew
    !id = Text3
    !First_Name = Text4
    !Last_Name = Text5
    !Date = Format(Text6.Text, "yyyy/mm/dd")
    .Update
    MsgBox " save", vbInformation, "Done"
Text2 = ""
Text3 = ""
Text4 = ""
Text5 = ""
Text6 = ""
End With
End If
DataGrid1.Refresh
End Sub

[vonnoy]

it was almost on it, but if the database has no current record of date,
then it keeps on reading the BOF and EOF, the result is error.

[AndreRet]

Try the following...

Sub search()
'search if id registered
With ado
    .ConnectionString = connectdb
    .RecordSource = "Select * from info where id = '" & Text2 & "'"
    .Refresh
    If .Recordset.RecordCount > 0 Then
    Text3 = .Recordset!id
    Text4 = .Recordset!fname
    Text5 = .Recordset!lname
    Text6 = Now ''Changed to now, this is normally in a proper date format...
'goto save record
    Call add
Public Sub add()
'save record
adoadd.Refresh

If adoadd..Recordset.RecordCount > 0 Then
    Dim chkDate As Date
    chkDate = Format(Text2.Text, "yyyy/mm/dd")
    If chkDate = adoadd.Recordset.Fields("date") Then
     MsgBox "duplicate date", vbInformation, "invalid"
     Text2 = ""
     Text3 = ""
     Text4 = ""
     Text5 = ""
     Text6 = ""
     Else
    With adoadd.Recordset
    .AddNew
    !id = Text3
    !First_Name = Text4
    !Last_Name = Text5
    !Date = Format(Text6.Text, "yyyy/mm/dd")
    .Update
    MsgBox " save", vbInformation, "Done"
Text2 = ""
Text3 = ""
Text4 = ""
Text5 = ""
Text6 = ""
End With
End If
DataGrid1.Refresh
    Else
   Msgbox "No records found" 

   Exit sub
  End If
End Sub