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