Problem in update database

[sacramento]

Hi to all:

This part of code, is suppose write in a DB Access, but something is wrong once the code not work very well, hi mean sometimes the values are stored in DB, sometimes no! Strange no? Yes very strange!!!
Can anyone tell me if i have something wrong in the code that could make this happen? i think the problem is on the code marked on bold!

Thanks

Private Sub colocarregisto()

Set db = Workspaces(0).OpenDatabase(caminho)
aSQL = "Select * from feiraaves" & ano
aSQL = aSQL & " ORDER BY registo"
Set rsfeiraaves = db.OpenRecordset(aSQL)
Set Data2.Recordset = rsfeiraaves

Do While Not Data2.Recordset.EOF


For n = 1 To 16

s1 = "gaiolaind" & n
s2 = "ano" & n
s3 = "precovenda" & n
s4 = "anilha" & n
s5 = "talao" & n
s6 = "vendido" & n
table = "feiraaves" & ano

If (Data2.Recordset![nomesocio] = Text1(3).Text) And _
(Data2.Recordset.Fields(s1).Value = Text1(2).Text) And _
(Data2.Recordset.Fields(s3).Value = Text1(5).Text) Then
Value = LCase(Text1(1).Text)
Value1 = LCase(Text1(2).Text)
Value2 = LCase(Text1(9).Text)
Sql = "update " & table & " set " & s5 & " = '" & Value & "' where " & s1 & " = '" & Value1 & "'"
db.Execute Sql
Sql = "update " & table & " set " & s6 & " = '" & Value2 & "' where " & s1 & " = '" & Value1 & "'"
db.Execute Sql

Next

Data2.Recordset.MoveNext
Loop



End Sub

[Doogle]

The code posted will not run - there's an 'End IF' missing - which could be seen easily if you'd indented your code.

Private Sub colocarregisto()
Set db = Workspaces(0).OpenDatabase(caminho)
aSQL = "Select * from feiraaves" & ano
aSQL = aSQL & " ORDER BY registo"
Set rsfeiraaves = db.OpenRecordset(aSQL)
Set Data2.Recordset = rsfeiraaves
Do While Not Data2.Recordset.EOF
    For n = 1 To 16
        s1 = "gaiolaind" & n
        s2 = "ano" & n
        s3 = "precovenda" & n
        s4 = "anilha" & n
        s5 = "talao" & n
        s6 = "vendido" & n
        Table = "feiraaves" & ano
        If (Data2.Recordset![nomesocio] = Text1(3).Text) And _
        (Data2.Recordset.Fields(s1).Value = Text1(2).Text) And _
        (Data2.Recordset.Fields(s3).Value = Text1(5).Text) Then
            Value = LCase(Text1(1).Text)
            Value1 = LCase(Text1(2).Text)
            Value2 = LCase(Text1(9).Text)
            Sql = "update " & Table & " set " & s5 & " = '" & Value & "' where " & s1 & " = '" & Value1 & "'"
            db.Execute Sql
            Sql = "update " & Table & " set " & s6 & " = '" & Value2 & "' where " & s1 & " = '" & Value1 & "'"
            db.Execute Sql
        End If  'This was missing
    Next
    Data2.Recordset.MoveNext
Loop
End Sub

So, what code are you actually running ?
Are you getting any error messages, if so what are they ?
Are there any 'On Error Resume Next' statements in the code, if there is (are) then remove it (them)?
Are the values in Text1(3),Text1(2) and Text1(5) correct ? (e.g. The same Case as the fields in the RecordSet with no extraneous spaces)
Have you put a breakpoint on and stepped through the code to see what is executed and what the values are?

[sacramento]

Hi,

I had try everything so far....i don't have any errors on code, i had put break points to see what 's going on, but i had find nothing!
The strange is sometimes the code Write on DB, and sometimes don't, and i still without understand why this happen....the code work Like desired, with this little (Big) problem!

[SamOscarBrown]

100 to 1, it is in your IF statement. Like Doogle pointed out, you must make sure your case (upper or lower) of your textbox entries and what is in your DB match. Also, you may have to do a TRIM() around each textbox as leading and trailing spaces count as characters (even tho you can't see them).
It would help if you wrote the beginning of your IF statement like this:

If lcase((Data2.Recordset![nomesocio]) = lcase(Text1(3).Text)) And _
        lcase((Data2.Recordset.Fields(s1).Value) = lcase(Text1(2).Text)) And _
        lcase((Data2.Recordset.Fields(s3).Value) = lcase(Text1(5).Text)) Then

if this continues to fail to work, you can debug your lines and look at each value in your textboxes, and compare them with the fields in your table to see if they match before executing the command.

[sacramento]

Hi:

Ok...i go test!

Thanks

[SamOscarBrown]

no hay problema
buena suerte

[sacramento]

Hi:

The code sort an "type mismatch error"

If i just put a single line work like this:

If LCase((Data2.Recordset.Fields(s1).Value) = LCase(Text1(2).Text)) Then

[SuperDre]

Hi:

The code sort an "type mismatch error"

If i just put a single line work like this:

your line should read: If LCase(Data2.Recordset.Fields(s1).Value) = LCase(Text1(2).Text) Then a '(' and ')' at the wrong place,

But try using StrComp(Data2.Recordset.Fields(s1).Value, Text1(2).Text, vbTextCompare) = 0

[sacramento]

OK...thanks

[SamOscarBrown]

Did it work?

[sacramento]

Hi...

So far Yes, but is unpredicable....i had make a few tests, but from one moment to other could happen!
Let's see if work 100%...just the time could tell!

Thanks