Dear
arnelgp,
after click button "dayso" with your public sub dayso(), all records as the same from tablea.
ex. after click button "dayso" =>ouput records 1 10 21 25 32 39 or 1 3 22 27 38 40 exist from tablea already etc.
*I want to insert new records no duplicate and different from tablea, but records mixed order based on C1 to C6 from tablea.
* the sub below insert still missing records
Sub dayso(tablea As String, fromngay As Date, tongay As Date, loai As String, tableb As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsCheckA As DAO.Recordset
Dim rsCheckB As DAO.Recordset
Dim strSQL As String
Dim arr(1 To 6) As Variant
Dim i As Integer
Dim STT As Integer
Set db = CurrentDb()
' Initialize STT
STT = 1
' Select records from tablea based on the criteria
strSQL = "SELECT * FROM " & tablea & " WHERE Ngay >= #" & fromngay & "# AND Ngay <= #" & tongay & "# AND Type = '" & loai & "'"
Set rs = db.OpenRecordset(strSQL)
' Loop through the recordset
Do Until rs.EOF
' Store the values of the current record
For i = 1 To 6
arr(i) = rs.Fields("C" & i).Value
Next i
' Move to the next record
rs.MoveNext
' If there is a next record, mix the values and insert a new record into tableb
If Not rs.EOF Then
' Check if the record already exists in tablea
strSQL = "SELECT * FROM " & tablea & " WHERE C1 = " & arr(1) & " AND C2 = " & arr(2) & " AND C3 = " & rs!C3 & " AND C4 = " & rs!C4 & " AND C5 = " & rs!C5 & " AND C6 = " & rs!C6
Set rsCheckA = db.OpenRecordset(strSQL)
' Check if the record already exists in tableb
strSQL = "SELECT * FROM " & tableb & " WHERE h1 = " & arr(1) & " AND h2 = " & arr(2) & " AND h3 = " & rs!C3 & " AND h4 = " & rs!C4 & " AND h5 = " & rs!C5 & " AND h6 = " & rs!C6
Set rsCheckB = db.OpenRecordset(strSQL)
If rsCheckA.EOF And rsCheckB.EOF Then
' If the record does not exist in both tables, insert it into tableb
' But first, check if the values are unique
Dim uniqueValues As New Collection
On Error Resume Next
uniqueValues.Add arr(1), CStr(arr(1))
uniqueValues.Add arr(2), CStr(arr(2))
uniqueValues.Add rs!C3, CStr(rs!C3)
uniqueValues.Add rs!C4, CStr(rs!C4)
uniqueValues.Add rs!C5, CStr(rs!C5)
uniqueValues.Add rs!C6, CStr(rs!C6)
On Error GoTo 0
If uniqueValues.Count = 6 Then
strSQL = "INSERT INTO " & tableb & " (h1, h2, h3, h4, h5, h6, type, STT) VALUES (" & arr(1) & ", " & arr(2) & ", " & rs!C3 & ", " & rs!C4 & ", " & rs!C5 & ", " & rs!C6 & ", '" & loai & "', " & STT & ")"
db.Execute strSQL
STT = STT + 1
End If
End If
rsCheckA.Close
rsCheckB.Close
End If
Loop
' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
