Fix error sub (1 Viewer)

btamsgn

Member
Local time
Today, 21:23
Joined
Nov 8, 2010
Messages
51
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
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:23
Joined
Sep 21, 2011
Messages
14,633
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

New member
Local time
Today, 10:23
Joined
Mar 22, 2024
Messages
9
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

You've got your good things, and you've got mine.
Local time
Today, 10:23
Joined
May 21, 2018
Messages
8,702
Code:
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

Immoderate Moderator
Staff member
Local time
Today, 09:23
Joined
Feb 28, 2001
Messages
27,529
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.
 

tvanstiphout

Active member
Local time
Today, 07:23
Joined
Jan 22, 2016
Messages
289
"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

AWF VIP
Local time
Today, 15:23
Joined
Jul 21, 2014
Messages
2,387
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.
 

Users who are viewing this thread

Top Bottom