Fix error sub (1 Viewer)

[btamsgn]

Dear Everyone,

Pls see and how to fix error sub c1tim() on command 4 of form as follows:
Sub c1tim(tablea As String, loai As String, x1 As Integer, tableb As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer

' Bu?c 1: Tìm giá tr? x1 trong các c?t C1 d?n C6 c?a tablea
Set db = CurrentDb()
strSQL = "SELECT * FROM " & tablea & " WHERE Type = '" & loai & "'"
Set rs = db.OpenRecordset(strSQL)

' Bu?c 2: Xóa d? li?u tableb tru?c khi dùng SQL vi?t các giá tr? tìm du?c ? bc1 vào tableb
strSQL = "DELETE * FROM " & tableb
db.Execute strSQL, dbFailOnError

' Bu?c 3: Sao chép toàn b? dòng tìm th?y t? tablea vào tableb

While Not rs.EOF
For i = 1 To 7
If rs.Fields("C" & i) = x1 Then
strSQL = "INSERT INTO " & tableb & " SELECT * FROM " & tablea & " WHERE C" & i & " = " & x1 & " AND Type = '" & loai & "'"
db.Execute strSQL, dbFailOnError

Exit For
End If
Next i
rs.MoveNext
Wend

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
The sub to update data into table "daso455"
Although query1 is ok.
Pls find the attachment for your ref., thanks.

Thanks& Regards,
Btamsgn

 

[Gasman]

Why not show the code?
Why do we need to download the db first, when it can be could easily be spotted with a display of code within code tags?

 

[jdelano]

Guessing because you haven't included what the error message is or on which line.

Place a breakpoint on the lines where you're creating the query and check the values of the variables that you're concatenating to make sure the values are what you are expecting.

 

[MajP]

Sub c1tim(tablea As String, loai As String, x1 As Integer, tableb As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer

' Bu?c 1: Tìm giá tr? x1 trong các c?t C1 d?n C6 c?a tablea
Set db = CurrentDb()
strSQL = "SELECT * FROM " & tablea & " WHERE Type = '" & loai & "'"
Debug.Print "RS Sql: " & strSQL
Set rs = db.OpenRecordset(strSQL)

' Bu?c 2: Xóa d? li?u tableb tru?c khi dùng SQL vi?t các giá tr? tìm du?c ? bc1 vào tableb
strSQL = "DELETE * FROM " & tableb
Debug.Print "Delete SQL: " & strSQL
db.Execute strSQL, dbFailOnError

' Bu?c 3: Sao chép toàn b? dòng tìm th?y t? tablea vào tableb

While Not rs.EOF
    For i = 1 To 7
        If rs.Fields("C" & i) = x1 Then
            strSQL = "INSERT INTO " & tableb & " SELECT * FROM " & tablea & " WHERE C" & i & " = " & x1 & " AND Type = '" & loai & "'"
            Debug.Print "Insert SQL: " & strSQL
            db.Execute strSQL, dbFailOnError
            Exit For
        End If
    Next i
    rs.MoveNext
Wend

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[The_Doc_Man]

While you DID post some code (and MajP cleaned up its format for you), you didn't tell us what you WANTED to do (as an overview) or what it actually DID do, and you even left out any error messages. The DB attachment didn't make it, by the way.

 

[Gasman]

Now crossposted https://www.accessforums.net/showthread.php?t=89700

 

[tvanstiphout]

"Type" is a Reserved Word and it should not be used for a field name or any other object name. Search online for "Access reserved words" to get the full list.

 

[cheekybuddha]

Really you probably ought to fix the design of table daso455 and whatever is tableb

Having seven fields named c1 ... c7 is a really poor design (search 'normalisation') that will require you to perform these kinds of code gymnastics.

With a better table structure the result could probably be easily achieved just using a query.