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 in				MiRec.MoveFirst
difference. For example, you want to
evaluate how your cash will flow and you have				For 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 key				ItemVal(X, 2) = MiRec(1)
in every record when maybe only the date is
different. An example would be, you have				ItemVal(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 if				4)
you want to change the amount value and
update the data table. The replace function				For z = 1 To xtimes
assumes that the description and date are the
same and replaces every occurrence of the				RDate = 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 include				Next
the MS DAO 3.6 Library. Cut and paste the
following code.				MiRec.MoveNext

Option Compare Database				Nextgetout:

Option Explicit				MiRec.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 As				PutMiSQLmisql = "INSERT INTO CashBac SELECT
DAO.Recordset				cash.* FROM cash;"

Set Midb = CurrentDb()				PutMiSQL

Midb.Execute misql				MiRec.MoveLast

End Function				Recnt = MiRec.RecordCount

Table 1. Cash				MiRec.MoveFirst

Amount currency				For X = 1 To Recnt

Cdate date/time				ItemVal(X, 1) = MiRec(0)

Desc text				ItemVal(X, 2) = MiRec(1)

Once you have created this table close and				ItemVal(X, 3) = MiRec(2)
save it as Cash. Make a copy of it and name
it CashBac. It will be used to make a copy				ItemVal(X, 4) = MiRec(3)xtimes = MiRec(2)
of your data before you do a replace.
				'Debug.Print ItemVal(x, 1) & " " & ItemVal(x,
Table 2. Repeats				2) & " " & 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 number				current 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 as				ItemVal(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 header				Next
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 for				Private Sub BT2_Click()
BT2 to Replace.
				DoReplace
Place the following code in the form. Open
the form in design view, then click view code				End 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 Variant				example was created with Access 2002 and runs
				as listed on my system. This is a free
Dim Recnt As Integer, RDate As Date, xtimes				example 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.dom				ask.
FROM Repeats;"
				This example and many others can be found on
GetMiSQL				our website at

MiRec.MoveLast				All of the examples found there are
				accompanied by a functioning Access database
Recnt = MiRec.RecordCount				file.

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

Introduction to rowing

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