Introduction to rowing


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

In this example I show how to create a table
of similar data that may only have a date inMiRec.MoveFirst
difference. For example, you want to
evaluate how your cash will flow and you haveFor  X  =  1  To  Recnt
a table of expenditures. You know that you
have regular payments for payroll, loans,ItemVal(X,  1)  =  MiRec(0)
taxes etc. that you want to put into your
transaction data table without having to keyItemVal(X,  2)  =  MiRec(1)
in every record when maybe only the date is
different. An example would be, you haveItemVal(X,  3)  =  MiRec(2)
twelve tax payments, all the same but in
different  months.ItemVal(X,  4)  =  MiRec(3)xtimes  = MiRec(2)
This example demonstrates using an array and'Debug.Print ItemVal(x, 1) & " " & ItemVal(x,
SQL statements to create the similar records.2) & " " & ItemVal(x, 3) & " " & ItemVal(x,
It also has a function to replace records if4)
you want to change the amount value and
update the data table. The replace functionFor  z  =  1  To  xtimes
assumes that the description and date are the
same and replaces every occurrence of theRDate = z & "/" & ItemVal(X, 4) & "/08"misql
existing record with the latest amount value.= "INSERT INTO Cash ( [Desc], Amount,
For safekeeping the replace function makes a[Cdate]) SELECT '" & ItemVal(X, 1) & "' AS
copy  of  the  data  table  before executing.x1, " & ItemVal(X, 2) & " AS x2, #" & RDate &
"#  AS  x3;"
This example requires a form, a module and a
three  tables  to  support  it.PutMiSQL
Create  the  following  Module  and  name it.'Debug.Print  misql
Make sure you set the references to includeNext
the MS DAO 3.6 Library. Cut and paste the
following  code.MiRec.MoveNext
Option  Compare  DatabaseNextgetout:
Option  ExplicitMiRec.Close
Public misql As String, Midb As Database,End  Function
MiRec As DAO.Recordset, MiRec2 As
DAO.Recordset'*********************
Public  Function  GetMiSQL()Private  Sub  BT1_Click()
Set  Midb  =  CurrentDb()DoRept
Set MiRec = Midb.OpenRecordset(misql,End  Sub
dbOpenDynaset)
'***********************
End  Function
Public  Function  DoReplace()
Public  Function  GetMiSQL2()
Dim  ItemVal(12,  4)  As  Variant
Set  Midb  =  CurrentDb()
Dim Recnt As Integer, RDate As Date, xtimes
Set MiRec2 = Midb.OpenRecordset(misql,As Integermisql = "SELECT Repeats.Item,
dbOpenDynaset)Repeats.Amount, Repeats.Occr, repeats.dom
FROM  Repeats;"
End  Function
GetMiSQLmisql = " DELETE CashBac.* FROM
Public  Function  PutMiSQL()CashBac;"
Dim Midb As Database ', MiRec AsPutMiSQLmisql = "INSERT INTO CashBac SELECT
DAO.Recordsetcash.*  FROM  cash;"
Set  Midb  =  CurrentDb()PutMiSQL
Midb.Execute  misqlMiRec.MoveLast
End  FunctionRecnt  =  MiRec.RecordCount
Table  1. CashMiRec.MoveFirst
Amount  currencyFor  X  =  1  To  Recnt
Cdate  date/timeItemVal(X,  1)  =  MiRec(0)
Desc  textItemVal(X,  2)  =  MiRec(1)
Once you have created this table close andItemVal(X,  3)  =  MiRec(2)
save it as Cash. Make a copy of it and name
it CashBac. It will be used to make a copyItemVal(X,  4)  =  MiRec(3)xtimes  = MiRec(2)
of  your  data  before  you  do  a  replace.
'Debug.Print ItemVal(x, 1) & " " & ItemVal(x,
Table  2. Repeats2) & " " & ItemVal(x, 3) & " " & ItemVal(x,
4)
Item  text
For  z  =  1  To  xtimes
Amount  currency
'change the 08 value in the next line to the
Occr  numbercurrent year or set a value on the form and
change  it  with  a  'variable.
DOM  number
RDate = z & "/" & ItemVal(X, 4) & "/08"misql
Set both of the number items properties= "UPDATE Cash SET Cash.Amount =" &
Integer. Close and save the table asItemVal(X, 2) & " WHERE Cash.Desc= '" &
Repeats.ItemVal(X, 1) & "' AND Cash.Cdate= #" & RDate
&  "#;"
Create  Form  "Transactions"
PutMiSQL
Use  table  "Repeats"  as  the  datasource
'Debug.Print  misql
Put  a  Form  header  and  footer on the form
Next
Place all the fields from the table on the
form.MiRec.MoveNext
Place all the field labels in the form headerNext
above their respective field placed in a
single row in the detail section of the form.MiRec.Close
Format  the  form  as  a  Continuous  Form.
End  Function
Place two buttons in the form footer and name
them  BT1  and  BT2.'***********************
Change the caption for BT1 to Create and forPrivate  Sub  BT2_Click()
BT2  to  Replace.
DoReplace
Place the following code in the form. Open
the form in design view, then click view codeEnd  Sub
from the menu tool bar. This opens the VBA
editor. You can just cut and paste from this'***********************
file  to  avoid  typing.
Cut  Here.
Cut  Here.
This should do it. As always, there are many
'************************different ways to accomplish something
similar to this. This works for me and saves
Public  Function  DoRept()a lot of time when I want to run several what
if's with my cash flow analysis. This
Dim  ItemVal(12,  4)  As  Variantexample was created with Access 2002 and runs
as listed on my system. This is a free
Dim Recnt As Integer, RDate As Date, xtimesexample and I can't support it or make
As Integermisql = "SELECT Repeats.Item,changes with out compensation so please don't
Repeats.Amount, Repeats.Occr, repeats.domask.
FROM  Repeats;"
This example and many others can be found on
GetMiSQLour  website  at
MiRec.MoveLastAll of the examples found there are
accompanied by a functioning Access database
Recnt  =  MiRec.RecordCountfile.



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 88 89 90 91 92 93