Dear All
Hôm trước có giới thiệu với các bạn một CSDL Sổ quỹ tiền mặt bao gồm các bước thiết kế và file CSDL. Trong file có trình bày cách lập các query để lấy dữ liệu nguồn cho các báo cáo. Việc thiết kế các query sẽ rất dễ dàng thông qua lưới query. Nhưng việc bảo mật các query và các table trong Ac là rất khó. Người ngoài có thể mở bất kỳ một table hoặc một query nào của bạn cho dù bạn đã chuyển sang MDE. Có một cach khắc phục đó là sử dụng các table tạm làm nguồn cho các report. Các table tạm này được cập nhật dữ liệu thông qua các Function được viết trong cácModules. Hôm nay chúng ta cùng xem xét vấn đề này.
Ở đây tôi vẫn sử dụng file Sổ quỹ tiền mặt đã nói ở trên và tập trung vào việc lập các Function để lấy dữ liệu cho việc tính tồn quỹ.
Để làm được việc này, chúng ta cùng bổ sung thêm hai table nữa
1/ Bảng ghi dữ liệu tồn đầu kỳ : tblTonDauKy. Bảng này chỉ có 1 trường duy nhất
TonDauKy – Number – Double – Standar – 0 – 0
2/ Bảng ghi dữ liệu phát sinh trong kỳ : tblPhatSinh. Bảng này gồm các trường sau
NgayCT – Date/Time
LoaiCT – Text – 1
SoCT – Text – 4
TenKhach – Text – 50
DiaChi – Text – 100
LyDo – Text – 100
SoCTGoc – Number – Integer
TKDU – Text – 10
SoTienThu – Number – Standar – 0 – 0
SoTienChi – Number – Standar – 0 – 0
---------- Post added at 01:57 PM ---------- Previous post was at 01:50 PM ----------
Các bạn chọn Modules, kích New để mở một modules mới, lưu lại với tên modSoQuy. Trong màn hình soạn thảo, các bạn lập các Function sau
1/ Hàm xóa bảng : hàm này có tác dụng xóa các dữ liệu ở bảng tạm để cập nhật dữ liệu mới khi người dung thay đổi thời gian báo cáo. Nội dung của hàm này như sau :
Diễn giải nội dung của hảm này như sau :
Tên hàm : XoaTable với một TabName được chỉ định cụ thể.
Đinh nghĩa một biến Rs kiểu Recordset(dữ liệu của bảng đang xét)
Chỉ định biến Rs là TabName của Database đang xét
Nếu TabName có dữ liệu ( Số record > 0 ) thì
Chuyển dữ liệu trong TabName về record đầu tiên
Xét vòng lặp trong TabName từ record đầu tiên cho đến record cuối cùng(TabName bị EOF)
Xóa record hiện hành
Chuyển xuống record tiếp theo
Khi TabName hết record thi ngưng vòng lặp và đóng TabName
Kết thúc hàm
Có thể hiểu nôm na thế này : Xét Table cần xóa, duyệt qua từng dòng của nó và tiến hành xóa lần lượt từng dòng một. Hoặc các bạn cũng có thể dùng câu lệnh SQL để xóa một lần. Các bạn thử theo cách này xem nhé (Cứ coi như đây là bài tập). Bạn nào làm xong thì post lên cho mọi người tham khảo
---------- Post added at 02:27 PM ---------- Previous post was at 01:57 PM ----------
2/ Hàm Tính Tồn Quỹ Đầu Kỳ : Hàm này có tác dụng tính toán ra số tồn quỹ tiền mặt trước ngày đang xét. Nội dung hàm này như sau :
Diễn giải nội dung của hàm này như sau :
Tên hàm : TonDau theo ngày TuNgay được chỉ đinh trước
Đinh nghĩa các biến kiểu Recordset gồm RTD và RCT
Đinh nghĩa một biếnTong kiểu số ( Double )
Định nghĩa một biến Khoa kiểu chuỗi ( String )
Gán giá trị của biến Khoa bằng cách lấy NamThangNgay của giá trị TuNgay va được diễn giải theo dạng yyyymmdd.
Gán giá trị cho biến Tong = 0 (trước khi xét vòng lặp ở đoạn sau)
Gán biến RTD là bảng tblTonDauKy của database đang xét
Gán biến RCT là bảng tblPhieuChiTiet của database đang xét
Nếu bảng tblTonDauKy có số record > 0 thì gọi hàm để xóa dữ liệu của bảng này bằng lệnh Call ( Call XoaTable(“tblTonDauKy”))
Chuyển về dòng dữ liệu đầu tiên của bảng tblPhieuChiTiet
Xét vòng lặp từ dòng dữ liệu đầu tiên đến hết dòng cuối cùng của bảng này
Xét trường RecKey của dòng dữ liệu hiện hành. Lấy giá trị số của 8 vị trí tính từ bên trái của trường này (dùng hàm Val để chuyển đổi từ text sang số) so sanh với giá trị số của biến Khoa đã được định nghĩa ở trên. Nếu là nhỏ hơn ( tức chỉ xét những ngày nhỏ hơn ngày chỉ định TuNgay ) thì
Nếu Chữ số cuối cùng của trường RecKey là T thì lấy giá trị của trường SoTien cộng vào giá trị Tong đã gán ở trên, sau đó lưu giá trị đã tính vào lại biến Tong
Ngược lại thì lấy giá trị của biến Tong đã được lưu trù đi giá trị của trường SoTien sau đó lại lưu vào biến Tong
Chuyển xuống xét record tiếp theo
Khi Val(Left(RCT!RecKey, 8)) >= Val(Khoa) hoặc đã hết dữ liệu trong bảng tblPhieuChiTiet thi ngưng vòng lặp
Add thêm một record mới cho bảng tblTonDauKy
Gán giá trị của biến Tong đã được tính toán ở trên vào trường TonDauKy của bảng tblTonDauKy và update số liệu
Đóng các bảng đã mở
Kết thúc hàm
Ở đây lưu ý các bạn mấy điểm sau :
1/ Muốn xét một bảng nào đó thì phải định nghĩa nó ở một biến recordset và mở nó
2/ Khi đã Add Record mới vào một bảng nào đó thì nhớ phải Update, nếu không Update thì kết quả bằng không, giá trị không được lưu
3/ Đã mở Recordset thì phải đóng nó lại để giải phóng bộ nhớ
---------- Post added at 03
8 PM ---------- Previous post was at 02:27 PM ----------
3/ Hàm ghi các phát sinh của sổ quỹ trong kỳ báo cáo : Hàm này co nhiệm vụ nhặt các chưng từ phát sinh có trong CSDL để update vào bảng tblPhatSinh.
Để phục vụ cho việc tinh toán, ta lập thêm một query qryChiTiet với câu SQL của nó như sau :
Query này nhằm mục đích tách trường SoTien ra thanh hai trường SoTienThu va SoTienChi
Nội dung cụ thể của hàm này như sau :
Các bạn tự diễn giải nhé. Ở đây lưu ý hai điểm : cách dùng Seek và dung biến kiểu QueryDef.
1/ Cách dùng biến kiểu queryDef
Chúng ta theo dõi đoạn hàm sau :
2/ Seek là chỉ đích danh, vì thế muốn dùng Seek, yêu cầu Recordset nhất thiết phải có Index (chỉ mục). Các bạn xem đoạn này sẽ rõ
Trong đoạn hàm trên, chúng ta dùng Seek để lấy Recordset của bảng tblKhach có trường MaKhach trùng với trường MaKhach của bảng tblPhatSinh nhằm lấy trường TenKhach và DiaChi tương ứng trong bảng tblKhach gán vào hai trường tương ứng trong bảng tblPhatSinh
Hôm trước có giới thiệu với các bạn một CSDL Sổ quỹ tiền mặt bao gồm các bước thiết kế và file CSDL. Trong file có trình bày cách lập các query để lấy dữ liệu nguồn cho các báo cáo. Việc thiết kế các query sẽ rất dễ dàng thông qua lưới query. Nhưng việc bảo mật các query và các table trong Ac là rất khó. Người ngoài có thể mở bất kỳ một table hoặc một query nào của bạn cho dù bạn đã chuyển sang MDE. Có một cach khắc phục đó là sử dụng các table tạm làm nguồn cho các report. Các table tạm này được cập nhật dữ liệu thông qua các Function được viết trong cácModules. Hôm nay chúng ta cùng xem xét vấn đề này.
Ở đây tôi vẫn sử dụng file Sổ quỹ tiền mặt đã nói ở trên và tập trung vào việc lập các Function để lấy dữ liệu cho việc tính tồn quỹ.
Để làm được việc này, chúng ta cùng bổ sung thêm hai table nữa
1/ Bảng ghi dữ liệu tồn đầu kỳ : tblTonDauKy. Bảng này chỉ có 1 trường duy nhất
TonDauKy – Number – Double – Standar – 0 – 0
2/ Bảng ghi dữ liệu phát sinh trong kỳ : tblPhatSinh. Bảng này gồm các trường sau
NgayCT – Date/Time
LoaiCT – Text – 1
SoCT – Text – 4
TenKhach – Text – 50
DiaChi – Text – 100
LyDo – Text – 100
SoCTGoc – Number – Integer
TKDU – Text – 10
SoTienThu – Number – Standar – 0 – 0
SoTienChi – Number – Standar – 0 – 0
---------- Post added at 01:57 PM ---------- Previous post was at 01:50 PM ----------
Các bạn chọn Modules, kích New để mở một modules mới, lưu lại với tên modSoQuy. Trong màn hình soạn thảo, các bạn lập các Function sau
1/ Hàm xóa bảng : hàm này có tác dụng xóa các dữ liệu ở bảng tạm để cập nhật dữ liệu mới khi người dung thay đổi thời gian báo cáo. Nội dung của hàm này như sau :
PHP:
Function XoaTable(TabName As String)
Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordset(TabName, dbOpenTable)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Do Until Rs.EOF
Rs.Delete
Rs.MoveNext
Loop
End If
Rs.Close
End FunctionDiễn giải nội dung của hảm này như sau :
Tên hàm : XoaTable với một TabName được chỉ định cụ thể.
Đinh nghĩa một biến Rs kiểu Recordset(dữ liệu của bảng đang xét)
Chỉ định biến Rs là TabName của Database đang xét
Nếu TabName có dữ liệu ( Số record > 0 ) thì
Chuyển dữ liệu trong TabName về record đầu tiên
Xét vòng lặp trong TabName từ record đầu tiên cho đến record cuối cùng(TabName bị EOF)
Xóa record hiện hành
Chuyển xuống record tiếp theo
Khi TabName hết record thi ngưng vòng lặp và đóng TabName
Kết thúc hàm
Có thể hiểu nôm na thế này : Xét Table cần xóa, duyệt qua từng dòng của nó và tiến hành xóa lần lượt từng dòng một. Hoặc các bạn cũng có thể dùng câu lệnh SQL để xóa một lần. Các bạn thử theo cách này xem nhé (Cứ coi như đây là bài tập). Bạn nào làm xong thì post lên cho mọi người tham khảo
---------- Post added at 02:27 PM ---------- Previous post was at 01:57 PM ----------
2/ Hàm Tính Tồn Quỹ Đầu Kỳ : Hàm này có tác dụng tính toán ra số tồn quỹ tiền mặt trước ngày đang xét. Nội dung hàm này như sau :
PHP:
Function TonDau(TuNgay)
Dim RTD As Recordset
Dim RCT As Recordset
Dim Tong As Double
Dim Khoa As String
Khoa = Year(TuNgay) & Right("0" & Month(TuNgay), 2) & Right("0" & Day(TuNgay), 2)
Tong = 0
Set RTD = CurrentDb.OpenRecordset("tblTonDauKy", dbOpenTable)
Set RCT = CurrentDb.OpenRecordset("tblPhieuChiTiet", dbOpenTable)
If RTD.RecordCount > 0 Then Call XoaTable("tblTonDauKy")
RCT.MoveFirst
Do Until RCT.EOF
If Val(Left(RCT!RecKey, 8)) < Val(Khoa) Then
If Right(RCT!RecKey, 1) = "T" Then
Tong = Tong + RCT!SoTien
Else
Tong = Tong - RCT!SoTien
End If
End If
RCT.MoveNext
Loop
RTD.AddNew
RTD!TonDauKy = Tong
RTD.Update
RTD.Close: RCT.Close
End FunctionDiễn giải nội dung của hàm này như sau :
Tên hàm : TonDau theo ngày TuNgay được chỉ đinh trước
Đinh nghĩa các biến kiểu Recordset gồm RTD và RCT
Đinh nghĩa một biếnTong kiểu số ( Double )
Định nghĩa một biến Khoa kiểu chuỗi ( String )
Gán giá trị của biến Khoa bằng cách lấy NamThangNgay của giá trị TuNgay va được diễn giải theo dạng yyyymmdd.
Gán giá trị cho biến Tong = 0 (trước khi xét vòng lặp ở đoạn sau)
Gán biến RTD là bảng tblTonDauKy của database đang xét
Gán biến RCT là bảng tblPhieuChiTiet của database đang xét
Nếu bảng tblTonDauKy có số record > 0 thì gọi hàm để xóa dữ liệu của bảng này bằng lệnh Call ( Call XoaTable(“tblTonDauKy”))
Chuyển về dòng dữ liệu đầu tiên của bảng tblPhieuChiTiet
Xét vòng lặp từ dòng dữ liệu đầu tiên đến hết dòng cuối cùng của bảng này
Xét trường RecKey của dòng dữ liệu hiện hành. Lấy giá trị số của 8 vị trí tính từ bên trái của trường này (dùng hàm Val để chuyển đổi từ text sang số) so sanh với giá trị số của biến Khoa đã được định nghĩa ở trên. Nếu là nhỏ hơn ( tức chỉ xét những ngày nhỏ hơn ngày chỉ định TuNgay ) thì
Nếu Chữ số cuối cùng của trường RecKey là T thì lấy giá trị của trường SoTien cộng vào giá trị Tong đã gán ở trên, sau đó lưu giá trị đã tính vào lại biến Tong
Ngược lại thì lấy giá trị của biến Tong đã được lưu trù đi giá trị của trường SoTien sau đó lại lưu vào biến Tong
Chuyển xuống xét record tiếp theo
Khi Val(Left(RCT!RecKey, 8)) >= Val(Khoa) hoặc đã hết dữ liệu trong bảng tblPhieuChiTiet thi ngưng vòng lặp
Add thêm một record mới cho bảng tblTonDauKy
Gán giá trị của biến Tong đã được tính toán ở trên vào trường TonDauKy của bảng tblTonDauKy và update số liệu
Đóng các bảng đã mở
Kết thúc hàm
Ở đây lưu ý các bạn mấy điểm sau :
1/ Muốn xét một bảng nào đó thì phải định nghĩa nó ở một biến recordset và mở nó
PHP:
Dim RCT As Recordset
….
Set RCT = CurrentDb.OpenRecordset("tblPhieuChiTiet", dbOpenTable)
PHP:
RTD.AddNew
…
RTD.Update
PHP:
Dim RCT As Recordset
….
Set RCT = CurrentDb.OpenRecordset("tblPhieuChiTiet", dbOpenTable)
….
RCT.Close---------- Post added at 03
8 PM ---------- Previous post was at 02:27 PM ----------3/ Hàm ghi các phát sinh của sổ quỹ trong kỳ báo cáo : Hàm này co nhiệm vụ nhặt các chưng từ phát sinh có trong CSDL để update vào bảng tblPhatSinh.
Để phục vụ cho việc tinh toán, ta lập thêm một query qryChiTiet với câu SQL của nó như sau :
PHP:
SELECT tblPhieuThuChi.RecKey, tblPhieuThuChi.NgayCT, tblPhieuThuChi.SoCT, tblPhieuThuChi.LoaiCT, tblPhieuThuChi.MaKhach, tblPhieuThuChi.LyDo, tblPhieuThuChi.SoCTGoc, tblPhieuChiTiet.TKDU, IIf([LoaiCT]="T",[SoTien],0) AS SoTienThu, IIf([LoaiCT]="C",[SoTien],0) AS SoTienChi
FROM tblPhieuThuChi INNER JOIN tblPhieuChiTiet ON tblPhieuThuChi.RecKey = tblPhieuChiTiet.RecKey;Nội dung cụ thể của hàm này như sau :
PHP:
Function PhatSinh(TuNgay, Denngay)
Dim RCT As Recordset
Dim RPS As Recordset
Dim Khach As Recordset
Dim QCT As QueryDef
Set QCT = CurrentDb.QueryDefs("qryChiTiet")
Set RCT = QCT.OpenRecordset()
Set RPS = CurrentDb.OpenRecordset("tblPhatSinh", dbOpenTable)
Set Khach = CurrentDb.OpenRecordset("tblKhach", dbOpenTable)
Khach.Index = "PrimaryKey"
If RPS.RecordCount > 0 Then Call XoaTable("tblPhatSinh")
RCT.MoveFirst
Do Until RCT.EOF
If RCT!NgayCT > Denngay Then Exit Do
If RCT!NgayCT >= TuNgay Then
RPS.AddNew
RPS!NgayCT = RCT!NgayCT
RPS!SoCT = RCT!SoCT
RPS!LoaiCT = RCT!LoaiCT
Khach.Seek "=", RCT!MaKhach
If Not Khach.NoMatch Then
RPS!TenKhach = Khach.Fields(1)
RPS!DiaChi = Khach.Fields(2)
End If
RPS!LyDo = RCT!LyDo
RPS!SoCTGoc = RCT!SoCTGoc
RPS!TKDU = RCT!TKDU
RPS!SoTienThu = RCT!SoTienThu
RPS!SoTienChi = RCT!SoTienChi
RPS.Update
End If
RCT.MoveNext
Loop
RCT.Close: Khach.Close: RPS.Close
End FunctionCác bạn tự diễn giải nhé. Ở đây lưu ý hai điểm : cách dùng Seek và dung biến kiểu QueryDef.
1/ Cách dùng biến kiểu queryDef
Chúng ta theo dõi đoạn hàm sau :
PHP:
Dim RCT As Recordset ‘Định nghĩa biến Recordset
Dim QCT As QueryDef ‘Định nghĩa biến kiểu QueryDef
Set QCT = CurrentDb.QueryDefs("qryChiTiet") ‘Gán biến QueryDef là qryChiTiet
Set RCT = QCT.OpenRecordset() ‘ Gán các dữ liệu của biến QueryDef vào Recordset
PHP:
Dim Khach As Recordset
….
Set Khach = CurrentDb.OpenRecordset("tblKhach", dbOpenTable)
Khach.Index = "PrimaryKey"
…..
Khach.Seek "=", RCT!MaKhach
If Not Khach.NoMatch Then
RPS!TenKhach = Khach.Fields(1)
RPS!DiaChi = Khach.Fields(2)
End If
----Trong đoạn hàm trên, chúng ta dùng Seek để lấy Recordset của bảng tblKhach có trường MaKhach trùng với trường MaKhach của bảng tblPhatSinh nhằm lấy trường TenKhach và DiaChi tương ứng trong bảng tblKhach gán vào hai trường tương ứng trong bảng tblPhatSinh
Sửa lần cuối:
