Introduction to rowing


MS Access Tip - You have heard of Arrays, Now Learn to Use Them

In this example I show how to create aRepeats.Item, Repeats.Amount,
table of similar data that may only haveRepeats.Occr, repeats.dom FROM Repeats;"
a date in difference. For example, youGetMiSQL
want to evaluate how your cash will flowMiRec.MoveLast
and you have a table of expenditures.Recnt = MiRec.RecordCount
You know that you have regular paymentsMiRec.MoveFirst
for payroll, loans, taxes etc. that youFor X = 1 To Recnt
want to put into your transaction dataItemVal(X, 1) = MiRec(0)
table without having to key in everyItemVal(X, 2) = MiRec(1)
record when maybe only the date isItemVal(X, 3) = MiRec(2)
different. An example would be, youItemVal(X, 4) = MiRec(3)xtimes =
have twelve tax payments, all the sameMiRec(2)
but in different months.'Debug.Print ItemVal(x, 1) & " " &
This example demonstrates using an arrayItemVal(x, 2) & " " & ItemVal(x, 3) & "
and SQL statements to create the similar" & ItemVal(x, 4)
records. It also has a function toFor z = 1 To xtimes
replace records if you want to changeRDate = z & "/" & ItemVal(X, 4) & "
the amount value and update the data08"misql = "INSERT INTO Cash ( [Desc],
table. The replace function assumesAmount, [Cdate]) SELECT '" & ItemVal(X,
that the description and date are the1) & "' AS x1, " & ItemVal(X, 2) & " AS
same and replaces every occurrence ofx2, #" & RDate & "# AS x3;"
the existing record with the latestPutMiSQL
amount value. For safekeeping the'Debug.Print misql
replace function makes a copy of theNext
data table before executing.MiRec.MoveNext
This example requires a form, a moduleNextgetout:
and a three tables to support it.MiRec.Close
Create the following Module and name it.End Function
Make sure you set the references to'*********************
include the MS DAO 3.6 Library. Cut andPrivate Sub BT1_Click()
paste the following code.DoRept
Option Compare DatabaseEnd Sub
Option Explicit'***********************
Public misql As String, Midb AsPublic Function DoReplace()
Database, MiRec As DAO.Recordset, MiRec2Dim ItemVal(12, 4) As Variant
As DAO.RecordsetDim Recnt As Integer, RDate As Date,
Public Function GetMiSQL()xtimes As Integermisql = "SELECT
Set Midb = CurrentDb()Repeats.Item, Repeats.Amount,
Set MiRec = Midb.OpenRecordset(misql,Repeats.Occr, repeats.dom FROM Repeats;"
dbOpenDynaset)GetMiSQLmisql = " DELETE CashBac.* FROM
End FunctionCashBac;"
Public Function GetMiSQL2()PutMiSQLmisql = "INSERT INTO CashBac
Set Midb = CurrentDb()SELECT cash.* FROM cash;"
Set MiRec2 = Midb.OpenRecordset(misql,PutMiSQL
dbOpenDynaset)MiRec.MoveLast
End FunctionRecnt = MiRec.RecordCount
Public Function PutMiSQL()MiRec.MoveFirst
Dim Midb As Database ', MiRec AsFor X = 1 To Recnt
DAO.RecordsetItemVal(X, 1) = MiRec(0)
Set Midb = CurrentDb()ItemVal(X, 2) = MiRec(1)
Midb.Execute misqlItemVal(X, 3) = MiRec(2)
End FunctionItemVal(X, 4) = MiRec(3)xtimes =
Table 1. CashMiRec(2)
Amount currency'Debug.Print ItemVal(x, 1) & " " &
Cdate date/timeItemVal(x, 2) & " " & ItemVal(x, 3) & "
Desc text" & ItemVal(x, 4)
Once you have created this table closeFor z = 1 To xtimes
and save it as Cash. Make a copy of it'change the 08 value in the next line to
and name it CashBac. It will be used tothe current year or set a value on the
make a copy of your data before you do aform and change it with a 'variable.
replace.RDate = z & "/" & ItemVal(X, 4) & "
Table 2. Repeats08"misql = "UPDATE Cash SET Cash.Amount
Item text=" & ItemVal(X, 2) & " WHERE Cash.Desc=
Amount currency'" & ItemVal(X, 1) & "' AND Cash.Cdate=
Occr number#" & RDate & "#;"
DOM numberPutMiSQL
Set both of the number items properties'Debug.Print misql
Integer. Close and save the table asNext
Repeats.MiRec.MoveNext
Create Form "Transactions"Next
Use table "Repeats" as the datasourceMiRec.Close
Put a Form header and footer on the formEnd Function
Place all the fields from the table on'***********************
the form.Private Sub BT2_Click()
Place all the field labels in the formDoReplace
header above their respective fieldEnd Sub
placed in a single row in the detail'***********************
section of the form. Format the form asCut Here.
a Continuous Form.This should do it. As always, there are
Place two buttons in the form footer andmany different ways to accomplish
name them BT1 and BT2.something similar to this. This works
Change the caption for BT1 to Create andfor me and saves a lot of time when I
for BT2 to Replace.want to run several what if's with my
Place the following code in the form.cash flow analysis. This example was
Open the form in design view, then clickcreated with Access 2002 and runs as
view code from the menu tool bar. Thislisted on my system. This is a free
opens the VBA editor. You can just cutexample and I can't support it or make
and paste from this file to avoidchanges with out compensation so please
typing.don't ask.
Cut Here.This example and many others can be
'************************found on our website at
Public Function DoRept()All of the examples found there are
Dim ItemVal(12, 4) As Variantaccompanied by a functioning Access
Dim Recnt As Integer, RDate As Date,database file.
xtimes As Integermisql = "SELECT



1 A B C 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93