Problem in update database
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
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
Next
Data2.Recordset.MoveNext
Loop
End Sub
Re: Problem in update database
The code posted will not run - there's an 'End IF' missing - which could be seen easily if you'd indented your code.
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?
Re: Problem in update database
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!
Re: Problem in update database
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:
Code:
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.
Re: Problem in update database
Hi:
Ok...i go test!
Thanks
Re: Problem in update database
no hay problema
buena suerte
Re: Problem in update database
Hi:
The code sort an "type mismatch error"
If i just put a single line work like this:
Quote:
If LCase((Data2.Recordset.Fields(s1).Value) = LCase(Text1(2).Text)) Then
Re: Problem in update database
Quote:
Originally Posted by
sacramento
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
Re: Problem in update database
Re: Problem in update database
Re: Problem in update database
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