Preventing Duplication

[darrel]

hi vb master good day! i just wanted to ask for some help here.. i'am trying to prevent duplcation of records that will be save in my table...

here my code:

Private Sub cmdVerify_Click()
Dim rsShowRec As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
For x = 0 To 3
 
On Error Resume Next
rsShowRec.Open "Select * from RECORDS , cnn, adOpenStatic, adLockOptimistic"
 
If rsShowRec.EOF <> True Then
If Combo1(x).Text = rsShowRec.Fields("TIME START").Value And Combo4(x).Text = rsShowRec.Fields("TIME END").Value And Combo2(x).Text = rsShowRec.Fields("ROOM").Value And Combo3(x).Text = rsShowRec.Fields("DAYS").Value Then
    rsShowRec.Close
    cnn.Close
    cmdSave.Visible = False
 
Else
    cmdSave.Visible = True
End If
End If
Next
    MsgBox ("Records already existed"), vbCritical
 
 
End Sub
 

the scenario is this eventhough my table doesnt have any records on it, still my system goes popping a mesage that the records i want to verify or save is already existed, i know theres something with my code. I assme that i doesnt actually loking in my database like i want,, kidly take a look at my code and i hope somebody can figure out whats wrong with it!!! Thank you very much!

[Killer42]

rsShowRec.Open "Select * from RECORDS , cnn, adOpenStatic, adLockOptimistic"

You've got the quotes in the wrong place. You're including all the parameters (cnn, etc) in the SQL string.

[darrel]

Thank you for entertaining mga thread, but i dont how to arrange it! can you give me ideas or hint on how my code will function as i want...

[Killer42]

Thank you for entertaining mga thread, but i dont how to arrange it! can you give me ideas or hint on how my code will function as i want...

I haven't looked at the code in much detail as yet, but the line that I mentioned probably should be corrected like this...

rsShowRec.Open "Select * from RECORDS" , cnn, adOpenStatic, adLockOptimistic

[darrel]

Thank very Mr. i will try to debug my program and keep you posted...thank you!

[darrel]

Mr.Killer ive debug my code but still doesnt change, still it doesnt look for records in my fields:
here's my code:

Dim rsShowRec As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
rsShowRec.Open "Select * from RECORDS", cnn, adOpenStatic, adLockOptimistic, adText
Next
 
    If Combo1(0).Text = rsShowRec.Fields("TIME START") And Combo2(0).Text = rsShowRec.Fields("ROOM") And Combo3(0).Text = rsShowRec.Fields("DAYS") And Combo4(0).Text = rsShowRec.Fields("TIME END") Then
        MsgBox ("Record already existed"), vbCritical
        rsShowRec.Close
        cnn.Close
        Exit Sub
 
 
    Else
        MsgBox ("There's no conflict with the previous records, you may save it now!"), vbOKOnly
        cmdSave.Enabled = True
    End If
 

i think there's something with this code of mine:

 
If Combo1(0).Text = rsShowRec.Fields("TIME START") And Combo2(0).Text = rsShowRec.Fields("ROOM") And Combo3(0).Text = rsShowRec.Fields("DAYS") And Combo4(0).Text = rsShowRec.Fields("TIME END") Then
        MsgBox ("Record already existed"), vbCritical
        rsShowRec.Close
        cnn.Close
        Exit Sub
 

I think it doesnt do anything at all, what my code must do is to test all the records in my fields and see if there's going to be a duplication with the data that will be save later on... Mr.Killer i hope you can figure out what ive been doing wrong or missing, am getting frustrated,,, i cant go forward to the other tasks of my system without finishing this test of records.. I hope you can help me.. Thank you very much and God bless you all for helping us newbies in vb programming, hail to you all!!

[Killer42]

I don't understand the purpose of the For loop around the Open. You are trying to open rsShowRec 15 times? Why?

I think what you need to do is either open rsShowRec as now (but only once) then use MoveNext in a loop to go through the records and check them, or use a WHERE clause in your SQL to find any record matching your values.

[darrel]

thank you for that information: can u teach how to use WHERE clause in my code??? Thank you very much.

i have modified again my code, but still nothing happen... it always prompt me that the records i have inputted inputted though in my records table it doesnt match anything i know theres somthing wrong with my comparison method, and i'am not sure with the connection of my database, is it the right way to code it,,

here my code:

Private Sub cmdVerify_Click()
Dim rsShowRec As New ADODB.Recordset
Dim cnn As New ADODB.Connection
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For y = 0 To 15
If Combo1(y).Text = "Select" Or Combo2(y).Text = "Select" Or Combo3(y).Text = "Select" Or Combo4(y).Text = "Select" Then
        On Error Resume Next
        MsgBox ("Cannot Save blank entries"), vbCritical
        On Error Resume Next
        Exit Sub
End If
Next
 
'AVOIDING DUPLICATION OF RECORDS
        rsShowRec.Open "Select * from RECORDS", cnn, adOpenStatic, adLockOptimistic
 
    If rsShowRec.RecordCount > 0 Then
        For x = 0 To 15
            If rsShowRec.Fields("TIME START") = UCase(Combo1(x).Text) And rsShowRec.Fields("TIME END") = UCase(Combo4(x).Text) And rsShowRec.Fields("ROOM") = UCase(Combo2(x).Text) And rsShowRec.Fields("DAYS") = UCase(Combo3(x).Text) Then
            rs.MoveNext
            End If
 
        Next
 
        MsgBox ("Record already existed"), vbCritical
        rsShowRec.Close
        cnn.Close
        End If
 
 
End Sub
 

thank you for patience mr. i hope we can slice whats wrong with this code... i just have to compare the data that i will be saving to the records that are been save in my RECORDS table... please...

[Killer42]

Ok, this is still going to require some work on your part. But the basic syntax to include a WHERE clause in your SQL string would be something like this...

Dim strSQL As String
strSQL = "Select * from RECORDS" _
       & " WHERE [TIME START] = '" & Ucase(Combo1(x).Text) & "'" _
       & " AND [TIME END] = '" & UCase(Combo4(x).Text) & "'" _
       & " AND ROOM = '" & UCase(Combo2(x).Text) & "'" _
       & " AND DAYS = '" & UCase(Combo3(x).Text) & "'"

[darrel]

Thank you very much... i will try to work it out now... ill keep you posted with the development of my program.

[darrel]

Hello, i have a news.. it still doesnt work,, there no error with the conection of the database and to the code it self but it doesnt meet up my needs... now the problem is every time i will verify the schedule that has been made, the program always prompt me the that theirs no error on the schedule though it has errors... i think there something wrong my with code concerning the testing on the records in my table and the schedule that is being created by the user...

heres my code now

 
Private Sub cmdVerify_Click()
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim x As Integer
Dim strSQL As String
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\dbTimeScheduling2.mdb;"
cnn.Open
 
For x = 0 To 15
On Error Resume Next
strSQL = "Select * from RECORDS" _
& "WHERE [TimeStart] = ' " & UCase(Combo1(x).Text) & " ' " _
& " AND [TimeEnd] = ' " & UCase(Combo4(x).Text) & " ' " _
& " AND [ROOM] = ' " & UCase(Combo2(x).Text) & " ' " _
& " AND [DAYS] = ' " & UCase(Combo3(x).Text) & " ' "
 
 
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
        rsShowRec.Close
        cnn.Close
        Exit Sub
End If
Next x
 
       If rs.EOF <> True Then
       Else
    For z = 0 To 15
       If UCase(Combo1(z).Text) = rs.Fields("TimeStart") And UCase(Combo4(z).Text) = rs.Fields("TimeEnd") And UCase(Combo2(z).Text) = rs.Fields("ROOM") And UCase(Combo3(z).Text) = rs.Fields("DAYS") Then
 
        rs.Close
        cnn.Close
       End If
    Next z
        MsgBox ("Theres error on the schedule!"), vbCritical
    End If
 
    For y = 0 To 15
       If Not UCase(Combo1(y).Text) = rs.Fields("TimeStart") And UCase(Combo4(y).Text) = rs.Fields("TimeEnd") And UCase(Combo2(y).Text) = rs.Fields("ROOM") And UCase(Combo3(y).Text) = rs.Fields("DAYS") Then
     End If
    Next y
        MsgBox ("Theres no error on the schedule!"), vbOKOnly
        cmdSave.Enabled = True
End Sub
 
 

I think theres something wrong in this area

 
If rs.EOF <> True Then
       Else
    For z = 0 To 15
       If UCase(Combo1(z).Text) = rs.Fields("TimeStart") And UCase(Combo4(z).Text) = rs.Fields("TimeEnd") And UCase(Combo2(z).Text) = rs.Fields("ROOM") And UCase(Combo3(z).Text) = rs.Fields("DAYS") Then
 
        rs.Close
        cnn.Close
       End If
 

i think this code doesnt do anything at all... I hope you can tell me whats wrong with this,,, I pressume that it doesnt actually looking in my fields.Thank you so much for patience... Hoping to hear from you soon...

[Killer42]

I don't have time right now to go over this in detail. But I have a couple of observations...

• You probably don't actually know whether there are any errors, because once you execute On Error Resume Next any error will simply cause you to continue with the next statement.
• Your indentation is a bit messed up, which makes it much harder to follow the logic.
• Why do you have an If test which does nothing...
  If rs.EOF <> True Then
  Else
• I think your logic is needlessly complex. It probably should be following a fairly simple path something like...
  

  For x = 0 to 15
    If anything not filled in Then
      Cannot Save blank entries
      Exit For
    End If
    Build SQL string to find any match for values(x)
    Execute query
    If anything found (check RecordCount, I suppose) Then
      Theres error on the schedule!
      Exit For
    End If
  Next

[darrel]

thank you for the advise, one more question i hope its ok! after i build a SQL string, how can i execute it, am having a hard to understand what you have posted "Execute query"..?? thank you

[Killer42]

thank you for the advise, one more question i hope its ok! after i build a SQL string, how can i execute it, am having a hard to understand what you have posted "Execute query"..?? thank you

That's the Open that you were using before. It opens, or executes, the query.

[darrel]

Thank you for that information Mr.Killer, but sad to say still my program doesnt work as i want, Mr. can you give me some example on how to compare database records or on how to prevent records using sql statement in visual just like wat i have been doing,even a simple example or code, for able for me to understand the concept and to understand the codes and scenarion needed.. thank you very very much... i hope you can site me some example... am really getting frustrated ive been working it out for about a week now, but still no changes...


Hope you can help me with this... Thank you!

[Dököll]

Thank you for that information Mr.Killer, but sad to say still my program doesnt work as i want, Mr. can you give me some example on how to compare database records or on how to prevent records using sql statement in visual just like wat i have been doing,even a simple example or code, for able for me to understand the concept and to understand the codes and scenarion needed.. thank you very very much... i hope you can site me some example... am really getting frustrated ive been working it out for about a week now, but still no changes...


Hope you can help me with this... Thank you!

Response to this is here:

http://www.thescripts.com/forum/thread654517.html