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:
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:
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...
heres my code for it:
Code:
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
i think i have some problems in this area:
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
Thank you very much...
Comment