Another free database application from Argeus: Stock Kid 2009. Download and see it for yourself if it can help you track your inventory and costs.
Argeus Solutions is now giving away a free database application: VAT Kid 2009. You can download and use it for free! It is useful for small companies to enter all sales and purchase transactions without any details and get VAT return form quickly and easily.
Microsoft Office Access 2003 is required. Please contact me if you are using an earlier version of MS Access. If you do not have Access, you can use Access Runtime 2007.
The problem:
When we use formulas on textboxes in the report footers of MS Access reports, such as “=sum(a_field_in_header)”, we get incorrect results because of duplication of same amounts for multiple items in detail section.
Here is an example to make it clear:
We have two tables one for header and another detail records, for example, invoice header and a item details.
SELECT h.*, d.item, d.price, d.quantity, d.detail_subtotal, d.detail_vat, d.detail_total FROM t_trans AS h INNER JOIN t_trans_detail AS d ON h.trans_id = d.trans_id;
So, that we get:
If we design a report to show the transactions:
We will get a listing of grouped transactions like this:
The report footer shows the problem and solution includes some VBA code below to do the correct calculations.
Option Compare Database Option Explicit Dim myTrans As New Collection Dim mySums(1 To 2) As Double '1 sum of total cost '2 sum of grandtotal 'distinct transactions Private Sub myCalculation(lTransID As Long, myCost As Currency, myTotal As Currency) On Error Resume Next myTrans.Add lTransID, "T" & lTransID 'try to insert transaction ID with collection Key If Err Then 'exists Err.Clear Else 'first time, so consider distinct headers mySums(1) = mySums(1) + myCost mySums(2) = mySums(2) + myTotal End If End Sub Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Debug.Print "Trans ID: " & Nz(Me.trans_id, 0) 'you will see this subroutine is called more than once Call myCalculation(Me.trans_id, Nz(Me.txt_totalcost, 0), Nz(Me.txt_grandtotal, 0)) End Sub Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) Me.txt_sum_totalcost = mySums(1) Me.txt_sum_grandtotal = mySums(2) End Sub
Problem:
Reading records from the database and instead of using DLookup or querying the database all the time, to improve performance we can read them into a VBA collection object and refer to it in memory. Especially, if the data in particular tables are not changing at all or changing very rarely, we can use this facility and hold the data in memory while the application is running. Beware of the number of records in your table and the amount of memory it may consume.
First, a very simple table to hold our data, with an ID (primary key) field to store unique numbers and a simple character field to save alphanumeric data.
Then, based on our table, a VBA class definition to store and manipulate data in memory.
'clsTable1 Option Compare Database Option Explicit 'definition same as Table1 Public id As Long Public data As String 'Flag to be set TRUE when loaded fine Public isOK As Boolean Private Sub Class_Initialize() isOK = False id = 0 data = "" End Sub
Here is the code of module modMain:
'modMain Option Compare Database Option Explicit 'simple collection Private myColl_simple As Collection 'collection to hold records of Table1 by id's Private myColl As Collection Function myColl_init() As Boolean On Error GoTo myErr Dim rs As DAO.Recordset, sKey As String, r As clsTable1 Set myColl = New Collection Set rs = CurrentDb.OpenRecordset("table1") rs.MoveFirst Do While Not rs.BOF And Not rs.EOF Set r = New clsTable1 r.id = rs.Fields("id").Value r.data = rs.Fields("data").Value r.isOK = True sKey = "A" & r.id myColl.Add r, sKey rs.MoveNext Loop rs.Close: Set rs = Nothing myExit: myColl_init = (myColl.Count > 0) Exit Function myErr: Resume myExit End Function Function myColl_get_simple(i As Long) As String On Error GoTo myErr Dim k As String, v As String, r As clsTable1 k = "A" & i 'key Set r = myColl_simple.Item(k) v = r.data myExit: myColl_get_simple = v Exit Function myErr: v = "" 'return an unexpected value Resume myExit End Function Function myColl_get(i As Long) As clsTable1 On Error GoTo myErr Dim k As String, r As clsTable1 k = "A" & i 'key Set r = myColl.Item(k) 'Flag is fine already myExit: Set myColl_get = r Exit Function myErr: Set r = New clsTable1 'return an object, flag False Resume myExit End Function Function myColl_init_simple() As Boolean Dim i As Long, k As String, v As String, t As clsTable1 Set myColl_simple = New Collection Randomize For i = 1 To 26 v = Chr(64 + i) 'A .. Z v = v & v & v ' AAA .. ZZZ Set t = New clsTable1 t.id = i t.data = v t.isOK = True k = "A" & i ' key myColl_simple.Add t, k Next i myColl_init_simple = (myColl_simple.Count > 0) End Function
Code of the form frmTest:
'frmTest Option Compare Database Option Explicit Private Sub cmdGet_Click() Dim id As Long, r As clsTable1 id = CLng(Nz(Me.txtID, 0)) If id > 0 Then Set r = myColl_get(id) If r.isOK Then Me.txtData.Value = r.data End If End If End Sub Private Sub cmdGetSimple_Click() Dim id As Long, v As String id = CLng(Nz(Me.txtID, 0)) If id > 0 Then v = myColl_get_simple(id) If v <> "" Then Me.txtData.Value = v End If End If End Sub Private Sub Form_Load() If Not myColl_init_simple() Then MsgBox "Error loading records!" End If If Not myColl_init() Then MsgBox "Error loading records!" End If End Sub
Feel free to download the whole database.
Microsoft programmers are always very particular on their way of approaching problems. Since they are the “biggest” software developers in the world, they have to set the standards!?
Problem: Updating field(s) of a table using a complex (sub) query.
Solution:
1. In standard SQL, you can this:
1 2 3 4 | UPDATE TABLE1 SET A_FIELD = Q.CALCULATED_FIELD FROM A_COMPLEX_QUERY Q WHERE TABLE1.ID_FIELD = Q.ID_FIELD; |
It makes sense, right?
No, not according to MS Access!
2. You must try something like this:
1 2 3 4 | UPDATE TABLE1 INNER JOIN A_COMPLEX_QUERY Q ON TABLE1.ID_FIELD = Q.ID_FIELD SET A_FIELD = Q.CALCULATED_FIELD; |
However, it says:
“You must use an updatable query!”
3. One of the “good” features of MS Access is its functions.
1 2 3 | UPDATE TABLE1 SET A_FIELD = DLOOKUP("CALCULATED_FIELD", "A_COMPLEX_QUERY", "ID_FIELD = " & ID_FIELD); |
Simple; but it may take ages execute if you have thousands of records.
You can also write your own (public) functions in VBA modules and refer to them in anywhere inside your MS Access database.
