Database Comparison

[darrel]

Hello vb masters, am having difficulty in comparing database through my visual basic program, i'am using access database and sql statement, i'am doing a time scheduling system, so one of the thing that my system must do is to trap the error of same schedule being save by the user .
heres my code for it:

Private Sub cmdVerify_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim x As Integer
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
If Combo1(x).Text = "Select" Or Combo2(x).Text = "Select" Or Combo3(x).Text = "Select" Or Combo4(x).Text = "Select" Then
        MsgBox ("Cannot Save blank entries"), vbCritical
        Exit Sub
End If
 
    Set rs = New ADODB.Recordset
    Set rs.ActiveConnection = cnn
 
    For x = 0 To 15
On Error Resume Next
rs.Source = "Select * from RECORDS where [SUBJECT CODES] = ' " & txtCode(x).Text & " ' "
rs.Open , cnn, adOpenStatic, adLockOptimistic
 
    If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(x).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(x).Text And rs.Fields("DAYS").Value = Combo3(x).Text Then
        MsgBox ("An error occured"), vbCritical
        rs.Close
        cnn.Close
        Exit Sub
    End If
    Next
 
        For z = 0 To 15
        If Not rs.Fields("TimeStart") = Format(Combo1(z).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(z).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(z).Text And rs.Fields("DAYS").Value = Combo3(z).Text Then
 
        MsgBox ("Perfect"), vbOKOnly
        cmdSave.Enabled = True
 
        End If
        Next
 
 
 
 
 
End Sub
 

now having difficulty in comparing the previous records in my table records that consists of TimeStart, TimeEnd, ROOM and DAYS field name.Both TimeStart and TimeEnd are in Date/Time format ("Medium Time") in Access...

i think i have some problems in this area:

  If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(x).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(x).Text And rs.Fields("DAYS").Value = Combo3(x).Text Then
        MsgBox ("An error occured"), vbCritical
        rs.Close
        cnn.Close
        Exit Sub
    End If
    Next
 

coz though my RECORD TABLE has some data on it, and the user inputed data to save in my RECORD TABLE, and its very equivalent to the records that are saved in my RECORD TABLE, so if the schedule is already in the RECORD table, my system must prompt the user that ("An error Occured") to prevent conflict or duplication of schedule.. Please can someone help me out with my problem, it been 1 week and still i cannot moveforward because of this..

Thank you very much...

[Killer42]

Access doesn't store date/time fields in any particular fomat. It just stores the actual date/time information (as a number of some sort). Formatting is done when it displays the fields. In this case, you will be simply retrieving a date/time value, so you should be comparing it to another date/time value, not a formatted string.

In other words, the comparison which you have written as

If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time")

is likely to be more like...

If rs.Fields("TimeStart").Value = CDate(Combo1(x).Text)

[darrel]

Thank you for that information. Now may question, is my code in testing if there an equal records in my the database are correct. Becuase it seems that it doesnt do anything at all, it like it doesnt check if there equal records in my table (RECORDS). PLease do check if my comparison statement are correct or if i'am missing some sort of code or details.

Thank you very much....
Here's my comparison statement code:

If rs.Fields("TimeStart").Value = Format(Combo1(x).Text, "Medium Time") And rs.Fields("TimeEnd").Value = Format(Combo4(x).Text, "Medium Time") And rs.Fields("ROOM").Value = Combo2(x).Text And rs.Fields("DAYS").Value = Combo3(x).Text Then
        MsgBox ("An error occured"), vbCritical
        rs.Close
        cnn.Close
        Exit Sub
    End If
    Next
 

[Killer42]

I just answered that, in my last post. Check the bold parts toward the end.

[darrel]

Ive already tried it, but same thing... it always prompt that "An error occured" indicating that the schedule i have been trying to save it al ready in my database records, though the schedule that am trying to save is actually not in my database records. i hope u understand what am try to say, its like this is i have inputted in my TimeStart 7:00am and TimeEnd 8:00, Room 302 and Days Monday,,, if the schedule am trying to save is already in my database records my system must prompt me, "An error occured" if not "There no error", hope u you can help me with this.

I think my code is mess up and wrong thats why am getting the output or msg which "An error occured' though theres no error at all.

[Killer42]

If you have a comparison which isn't working, stop the execution at that point (with a breakpoint) and examine the values on either side of the comparison. That will tell you what's going on.

For instance, check the values of rs.Fields("TimeStart").Value and Combo1(x).Text to see whether they really do match.

Also, could you post the version that you say you tried? The one you've posted looks unlikely to work due to the date-format thing I mentioned.

[darrel]

How can i check the values of my field in my database and combo box to if they are really match...
I'am using VB 6 and Microsoft access 2000, and my .mdb i'am using in my program is also in 2000 format? thank you! lookinf forward for your reply.

[Killer42]

You really should read the documentation or look up a tutorial on the web. The debugging facilities built into VB6 are excellent. You can interrupt the code at any point, examine the values of variables and properties, change them, "step through" the code one statement at a time, and I forget what else.

To begin with, just move the cursor to the line which does the big IF test, and press the F9 key to put a breakpoint there. (You can also toggle breakpointts on and off by clicking in the grey area to the left of the code).

Run the program. When it hits that line, it should stop and display it. If you simply hover your mousepointer over a variable or property, a popup tooltip should show the current value. You can also do a number of things such as typing Print variable in the immediate window (press Ctrl-G to get there).