Hi EniGma,
Here we go! I've written three routines, all contained in a standard module called ItemOrdQtyManager that is attached here as a zip file.
ItemOrdQtyManager.zip
To include this in your database do the following:
- Unzip the file; it then has a .bas extension.
- Go to the VBA coding window (Alt+F11).
- Right-click in the upper left-hand pane.
- Select Import File...
- Browse until you can select the unzipped ItemOrdQtyManager.bas in your directories.
- Click Open.
Here are some comments on the routines. You must however change the code to suit your own names if they differ from my assumptions. Further the module contains no error handling; this is something that should be included as the implementation is 'refined.'
Public Function SQLTest(rlngItemId As Long) As Long
I wrote this as an afterthought to test whether my techniques would work within SQL. They do but the routine is likely to prove slow for large data. However 120 records is hardly 'large' and I suspect this is the solution you will prefer which is why I'm describing it first. Here is a sample of SQL that shows how to use it.
SELECT tblItemOrdQty.*, SQLTest([ItemID]) AS LatestQty FROM tblItemOrdQty;
Public Sub TransposeTable(rstrTableNameIn As String)
This routine creates a new table with a suffix appended to the name of the original table. The sufix is currently 'Ext' but this can be changed in the declarations section of the module. Because I suspect the number of 'lot' columns may vary, the routine first deletes any existing table and recreates the new table definition from the source table adding a field for LatestQty. (The name of the additional column may be changed in the declarations section of the module.)
I have been crude when copying field specifications limiting the copied properties to name and data type only. There may be other properties (and perhaps indices) that should be copied but I have not seen your table design and therefore do not know.
The routine needs to be invoked (called) somehow. The usual method is to do this from the Click event of a command button. The code behind the event will be similar to the following.
Code:
Private Sub Command0_Click()
ItemOrdQtyManager.TransposeTable "tblItemOrdQty"
End Sub
This to my mind is the preferred generalised solution, especially for large numbers of records (i.e. thousands!)
Public Function GetLatestQuantity(rstrTableNameIn As String, _
rlngItemId As Long, _
rstrFieldNameOut As String) As Long
Finally, to complete the set, this function returns the latest lot quantity for a single item and indicates which lot is the latest. It is not intended for mass use but for individual enquiries should the need arise. You must supply a string variable for the column name but it may be empty; the procedure places the column name in this variable.
Here's an example of the 'calling' code. The MsgBox statement is there simply for my testing purpose.
Code:
Private Sub Command1_Click()
Dim lngQty As Long
Dim strFieldName As String
lngQty = ItemOrdQtyManager.GetLatestQuantity("tblItemOrdQty", 101, strFieldName)
MsgBox lngQty & " " & strFieldNam
End Sub
Windows XP 
Access 2007
Reply With Quote
