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

In this example I show how to create a table of similarGetMiSQL
data that may only have a date in difference. ForMiRec.MoveLast
example, you want to evaluate how your cash willRecnt = MiRec.RecordCount
flow and you have a table of expenditures. You knowMiRec.MoveFirst
that you have regular payments for payroll, loans,For X = 1 To Recnt
taxes etc. that you want to put into your transactionItemVal(X, 1) = MiRec(0)
data table without having to key in every record whenItemVal(X, 2) = MiRec(1)
maybe only the date is different. An example wouldItemVal(X, 3) = MiRec(2)
be, you have twelve tax payments, all the same but inItemVal(X, 4) = MiRec(3)xtimes = MiRec(2)
different months.'Debug.Print ItemVal(x, 1) & " " & ItemVal(x, 2) & " " &
This example demonstrates using an array and SQLItemVal(x, 3) & " " & ItemVal(x, 4)
statements to create the similar records. It also has aFor z = 1 To xtimes
function to replace records if you want to change theRDate = z & "/" & ItemVal(X, 4) & "/08"misql =
amount value and update the data table. The replace"INSERT INTO Cash ( [Desc], Amount, [Cdate])
function assumes that the description and date are theSELECT '" & ItemVal(X, 1) & "' AS x1, " & ItemVal(X, 2)
same and replaces every occurrence of the existing& " AS x2, #" & RDate & "# AS x3;"
record with the latest amount value. For safekeepingPutMiSQL
the replace function makes a copy of the data table'Debug.Print misql
before executing.Next
This example requires a form, a module and a threeMiRec.MoveNext
tables to support it.Nextgetout:
Create the following Module and name it.MiRec.Close
Make sure you set the references to include the MSEnd Function
DAO 3.6 Library. Cut and paste the following code.'*********************
Option Compare DatabasePrivate Sub BT1_Click()
Option ExplicitDoRept
Public misql As String, Midb As Database, MiRec AsEnd Sub
DAO.Recordset, MiRec2 As DAO.Recordset'***********************
Public Function GetMiSQL()Public Function DoReplace()
Set Midb = CurrentDb()Dim ItemVal(12, 4) As Variant
Set MiRec = Midb.OpenRecordset(misql,Dim Recnt As Integer, RDate As Date, xtimes As
dbOpenDynaset)Integermisql = "SELECT Repeats.Item,
End FunctionRepeats.Amount, Repeats.Occr, repeats.dom FROM
Public Function GetMiSQL2()Repeats;"
Set Midb = CurrentDb()GetMiSQLmisql = " DELETE CashBac.* FROM
Set MiRec2 = Midb.OpenRecordset(misql,CashBac;"
dbOpenDynaset)PutMiSQLmisql = "INSERT INTO CashBac SELECT
End Functioncash.* FROM cash;"
Public Function PutMiSQL()PutMiSQL
Dim Midb As Database ', MiRec As DAO.RecordsetMiRec.MoveLast
Set Midb = CurrentDb()Recnt = MiRec.RecordCount
Midb.Execute misqlMiRec.MoveFirst
End FunctionFor X = 1 To Recnt
Table 1. CashItemVal(X, 1) = MiRec(0)
Amount currencyItemVal(X, 2) = MiRec(1)
Cdate date/timeItemVal(X, 3) = MiRec(2)
Desc textItemVal(X, 4) = MiRec(3)xtimes = MiRec(2)
Once you have created this table close and save it as'Debug.Print ItemVal(x, 1) & " " & ItemVal(x, 2) & " " &
Cash. Make a copy of it and name it CashBac. It willItemVal(x, 3) & " " & ItemVal(x, 4)
be used to make a copy of your data before you doFor z = 1 To xtimes
a replace.'change the 08 value in the next line to the current
Table 2. Repeatsyear or set a value on the form and change it with a
Item text'variable.
Amount currencyRDate = z & "/" & ItemVal(X, 4) & "/08"misql =
Occr number"UPDATE Cash SET Cash.Amount =" & ItemVal(X, 2)
DOM number& " WHERE Cash.Desc= '" & ItemVal(X, 1) & "' AND
Set both of the number items properties Integer. CloseCash.Cdate= #" & RDate & "#;"
and save the table as Repeats.PutMiSQL
Create Form "Transactions"'Debug.Print misql
Use table "Repeats" as the datasourceNext
Put a Form header and footer on the formMiRec.MoveNext
Place all the fields from the table on the form.Next
Place all the field labels in the form header above theirMiRec.Close
respective field placed in a single row in the detailEnd Function
section of the form. Format the form as a Continuous'***********************
Form.Private Sub BT2_Click()
Place two buttons in the form footer and name themDoReplace
BT1 and BT2.End Sub
Change the caption for BT1 to Create and for BT2 to'***********************
Replace.Cut Here.
Place the following code in the form. Open the form inThis should do it. As always, there are many different
design view, then click view code from the menu toolways to accomplish something similar to this. This
bar. This opens the VBA editor. You can just cut andworks for me and saves a lot of time when I want to
paste from this file to avoid typing.run several what if's with my cash flow analysis. This
Cut Here.example was created with Access 2002 and runs as
'************************listed on my system. This is a free example and I can't
Public Function DoRept()support it or make changes with out compensation so
Dim ItemVal(12, 4) As Variantplease don't ask.
Dim Recnt As Integer, RDate As Date, xtimes AsThis example and many others can be found on our
Integermisql = "SELECT Repeats.Item,website at
Repeats.Amount, Repeats.Occr, repeats.dom FROMAll of the examples found there are accompanied by
Repeats;"a functioning Access database file.