paulsteigel > 10-05-16, 10:49 AM
(10-05-16, 09:30 AM)Minh Tiên Đã viết: ....
Sub UpdateHH()
Dim SqlTxt As String
' Tat canh bao tu CSDL
DoCmd.SetWarnings False
' Cap nhap nhung ma hang da co trong tblDanhsach_Hanghoa
SqlTxt = "UPDATE tblDanhsach_Hanghoa AS d INNER JOIN ( " & _
"SELECT b.Mahang, a.Dongianhap, a.Dongiabanle, a.Dongiabansy, [a].[Soluongton]+[b].[Soluongnhap] AS slTon " & _
"FROM strTableNameTam AS b INNER JOIN tblDanhsach_Hanghoa AS a ON b.Mahang = a.Mahang) AS c " & _
"ON d.Mahang = c.Mahang " & _
"SET d.Soluongton = [c].[slTon], d.Dongianhap = [c].[Dongianhap], d.Dongiabanle = [c].[Dongiabanle], d.Dongiabansy = [c].[Dongiabansy];"
' O day co 2 Query, Query1_1 de lay danh sach nhung ban ghi co Mahang giong nhau giua tblDanhsach_Hanghoa strTableNameTam
' "SELECT b.Mahang, a.Dongianhap, a.Dongiabanle, a.Dongiabansy, [a].[Soluongton]+[b].[Soluongnhap] AS slTon " & _
' "FROM strTableNameTam AS b INNER JOIN tblDanhsach_Hanghoa AS a ON b.Mahang = a.Mahang
' Va Query 2 thuc hien viec UPDATE
' "UPDATE tblDanhsach_Hanghoa AS d INNER JOIN Query1_1 AS c " & _
' "ON d.Mahang = c.Mahang " & _
' "SET d.Soluongton = [c].[slTon], d.Dongianhap = [c].[Dongianhap], d.Dongiabanle = [c].[Dongiabanle], d.Dongiabansy = [c].[Dongiabansy];"
' Thuc thi lenh
CurrentDb.Execute SqlTxt
' Them vao tblDanhsach_Hanghoa nhung cai khong co trong bang nay
SqlTxt = "INSERT INTO tblDanhsach_Hanghoa ( Mahang, Tenhang, Donvitinh, Nhomhang, Nganhhang, Soluongton, Dongianhap, Dongiabanle, Dongiabansy ) " & _
"SELECT a.Mahang, a.Tenhang, a.Donvitinh, a.Nhomhang, a.Nganhhang, a.Soluongnhap, a.Dongianhap, a.Dongiabanle, a.Dongiabansy " & _
"FROM (SELECT c.Mahang, c.Tenhang, c.Donvitinh, c.Nhomhang, c.Nganhhang, c.Soluongnhap, c.Dongianhap, c.Dongiabanle, c.Dongiabansy " & _
"FROM strTableNameTam AS c LEFT JOIN tblDanhsach_Hanghoa AS b ON c.Mahang = b.Mahang " & _
"WHERE (((b.Mahang) Is Null))) AS a;"
' O day co 2 Query tuong tu nhu tren
' Querry1_2 xac dinh danh sach ban ghi trong strTableNameTam ma khong co trong tblDanhsach_Hanghoa
' "SELECT c.Mahang, c.Tenhang, c.Donvitinh, c.Nhomhang, c.Nganhhang, c.Soluongnhap, c.Dongianhap, c.Dongiabanle, c.Dongiabansy " & _
' "FROM strTableNameTam AS c LEFT JOIN tblDanhsach_Hanghoa AS b ON c.Mahang = b.Mahang " & _
' "WHERE (((b.Mahang) Is Null))
' Query thuc hien lenh chen ban ghi INSERT INTO (dat ten Query truoc la Querry1_2
' "INSERT INTO tblDanhsach_Hanghoa ( Mahang, Tenhang, Donvitinh, Nhomhang, Nganhhang, Soluongton, Dongianhap, Dongiabanle, Dongiabansy ) " & _
' "SELECT a.Mahang, a.Tenhang, a.Donvitinh, a.Nhomhang, a.Nganhhang, a.Soluongnhap, a.Dongianhap, a.Dongiabanle, a.Dongiabansy " & _
' "FROM Querry1_2 AS a;"
CurrentDb.Execute SqlTxt
DoCmd.SetWarnings True
End Sub