[RESOLVED] Impossible to avoid duplication !!!

[Mustaphi]

Hi
This code is supposed to avoid duplication of the same data even you click the Cmd_Add button twenty times.
But each time I click the button, the same data is ceated in database.

RS.Open "select Id , plan, doc, unit , order from MyTable" & _
        " where Id like '" & text1.Text & "' " & _
        " and plan= '" & text2.Text & "' " & _
        " and doc= '" & text3.Text & "' " & _
        " and order = '" & text4.Text & "' " & _
        "   and unit = '" & text5.Text & "'", _
        DB, adOpenDynamic, adLockOptimistic      
         If not RS.BOF Then
       rs!Id = text1.Text 
    rs!plan= text2.Text 
    rs!doc= text3.Text 
    rs!order = text4.Text 
    rs!unit = text5.Text 
RS.update
else
if text4.text <> "" and text5.text <> "" then
Rs.Addnew
 rs!Id = text1.Text 
    rs!plan= text2.Text 
    rs!doc= text3.Text 
    rs!order = text4.Text 
    rs!unit = text5.Text 
Rs.update
end if 
end if
RS.Close: Set RS = Nothing
    DB.Close: Set DB = Nothing

[Bobbles]

I am a bit pressed for time, so I cannot quickly find example code for you.
If I were you I would evolve away from using a rs to do your update.
I use ACTION queries instead.
Using those, you can INSERT records, or UPDATE records, without using a rs.
I also dislike maintaining a connection to the DB
So my scenario is use a rs to retrieve records, which i store into my controls (Grid, textbox, etc).
I immediately close the rs, and disconnect from the DB
If a user wishes to change a record, I do an ACTION UPDATE, which updates the DB (no rs involved)

Here is an example of INSERT INTO (inserting new record) -
http://www.vb-helper.com/howto_ado_i...ct_quotes.html

I did a quick look in my code for an ACTION UPDATE, to give you a taste for it -
sSQL = "UPDATE JobLists SET DateDue = " & IN_DateDue
sSQL = sSQL & " WHERE ParentTS = '" & IN_ParentTS & "' AND JobNu = '" & IN_JobNuCough & "'"

[DataMiser]

Well your query looks a bit odd. For one thing you are using Like but no wildcard so you probably should be using = instead.

ID should be the key of the record so there should be no need to have all of those fields in the where clause.
ID should be defined as unique so that it is not possible to have a duplicate even if you code fails.

The update code makes no sense at all since it is not actually changing anything


That said the code does look like it should only add the data if it is not already in the system.
I personally have never used

If not RS.BOF Then

to test for records instead I use

If Not RS.EOF Then

but I would expect BOF to have the same result. You may want to try EOF just to see if it gives a different result but I doubt that it would.

In this case however I since the actual If portion of that does not need to do anything then I would simply use

If  RS.EOF Then
    ' add new record
End If

Still that should not really be any different in the long run other than being simpler.

I would suspect that perhaps there is some other code somewhere which we are not seeing that is causing your problem

[IkkeEnGij]

well, maybe OP is using the MoonShine database system, wich is notorious for its lack of unique constrains
and i suspect that is so because:

if text4.text <> "" and text5.text <> "" then

the MoonShine database system is also notorious for its lack of the NULL concept

[SamOscarBrown]

Tend to agree with Ikke....usually beginners on this Forum are using MS ACCESS, but if OP is using ACCESS (Or a JET Database to be more precise), then the word 'order' is a Reserved Word and would had thrown an error in his query.

So, not sure how to assist, other than to reiterate what DM said about using EOF, and also the great Bobbles' comment about using ACTION updates.

[Mustaphi]

I chaged the operator (like ) to (=)

I used Eof instead of Bof

I have concised the code

If rs.eof then
addnew

I changed the field name (order)
and I got what I was expecting is OK
so thankful to you

[DataMiser]

I missed the field named order for some reason but yes since order is a reserved word that could be an issue. I would have expected it to give an error though.

Perhaps there was an On Error Resume Next there that is not shown?