con1.Execute "INSERT INTO Lop(MaLop,TenLop) VALUES ('" & ws.Range("A" & i).Value & "', '" & ws.Range("B" & i).Value & "')"
Loop Until ws.Range("A" & (i + 1)) = Empty
Set ex = Nothing
Can you check the UPDATE Statement ? Why is the UPDATE Statement is error ?
As I have mentioned before if your date field is an actual date field then access requires you to use # signs around the date value rather than ' characters.
You have a comma in your update statement right after SET which should not be there.
That is just what I see at a quick glance. You should:
1: Dim a String variable to hold your SQL statement
2: Assign your SQL Statement to the string
3: Use debug.Print or Msgbox to display the content of that SQL Statement once it is built
4: Use the string variable holding the sql statement for your execute statement
This allows you to see the actual string rather than trying to dechiper it from your code, also if you use debug.print you can copy the actual update statement and paste it into access query designer then execute it there to get a better idea of what is wrong and how to fix it.
Now I Stuck in FIND Statement because my homework is required if MSSV is update in the first time, the ID Number = 1. Then if MSSV is update the second time, the ID Number = 2 and the Status from MSSV is convert -1(Yes) to 0(No)
cmd.CommandText = "SELECT MAX(ID Number) FROM SV where MSSV ='" & ws.Range("C" & i) & "'", con, adOpenDynamic, adLockBatchOptimistic
Set rs = cmd.Execute()
If rs.RecordCount > 0 Then
con.Execute "UPDATE SV SET Status = 0 WHERE Status = -1"
I think it is wrong. Can you fix it for me, please?
I have 2 problem in my homework. Firstly, I have convert ID field from AutoNumber to Number. I want to start ID = 1.Because I try to fix it but ID is starting 0. When I insert repeatly the second time, ID = 2.
Secondly, I try to write INSERT Statement and Update Statement but the program is running the Update Statement.