| 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 | | | | ItemVal(X, 4) = MiRec(3)xtimes = |
| have twelve tax payments, all the same | | | | MiRec(2) |
| but in different months. | | | | 'Debug.Print ItemVal(x, 1) & " " & |
| This example demonstrates using an array | | | | ItemVal(x, 2) & " " & ItemVal(x, 3) & " |
| and SQL statements to create the similar | | | | " & ItemVal(x, 4) |
| records. It also has a function to | | | | For z = 1 To xtimes |
| replace records if you want to change | | | | RDate = z & "/" & ItemVal(X, 4) & " |
| the amount value and update the data | | | | 08"misql = "INSERT INTO Cash ( [Desc], |
| table. The replace function assumes | | | | Amount, [Cdate]) SELECT '" & ItemVal(X, |
| that the description and date are the | | | | 1) & "' AS x1, " & ItemVal(X, 2) & " AS |
| same and replaces every occurrence of | | | | x2, #" & RDate & "# AS x3;" |
| the existing record with the latest | | | | PutMiSQL |
| amount value. For safekeeping the | | | | 'Debug.Print misql |
| replace function makes a copy of the | | | | Next |
| 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 | | | | Public Function DoReplace() |
| Database, MiRec As DAO.Recordset, MiRec2 | | | | Dim ItemVal(12, 4) As Variant |
| As 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 = |
| Table 1. Cash | | | | MiRec(2) |
| Amount currency | | | | 'Debug.Print ItemVal(x, 1) & " " & |
| Cdate date/time | | | | ItemVal(x, 2) & " " & ItemVal(x, 3) & " |
| Desc text | | | | " & ItemVal(x, 4) |
| Once you have created this table close | | | | For 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 to | | | | the current year or set a value on the |
| make a copy of your data before you do a | | | | form and change it with a 'variable. |
| replace. | | | | RDate = z & "/" & ItemVal(X, 4) & " |
| Table 2. Repeats | | | | 08"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 number | | | | PutMiSQL |
| Set both of the number items properties | | | | 'Debug.Print misql |
| Integer. Close and save the table as | | | | Next |
| Repeats. | | | | MiRec.MoveNext |
| Create Form "Transactions" | | | | Next |
| Use table "Repeats" as the datasource | | | | MiRec.Close |
| Put a Form header and footer on the form | | | | End Function |
| Place all the fields from the table on | | | | '*********************** |
| the form. | | | | Private Sub BT2_Click() |
| Place all the field labels in the form | | | | DoReplace |
| header above their respective field | | | | End Sub |
| placed in a single row in the detail | | | | '*********************** |
| section of the form. Format the form as | | | | Cut Here. |
| a Continuous Form. | | | | This should do it. As always, there are |
| Place two buttons in the form footer and | | | | many different ways to accomplish |
| name them BT1 and BT2. | | | | something similar to this. This works |
| Change the caption for BT1 to Create and | | | | for 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 click | | | | created with Access 2002 and runs as |
| view code from the menu tool bar. This | | | | listed on my system. This is a free |
| opens the VBA editor. You can just cut | | | | example and I can't support it or make |
| and paste from this file to avoid | | | | changes 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 Variant | | | | accompanied by a functioning Access |
| Dim Recnt As Integer, RDate As Date, | | | | database file. |
| xtimes As Integermisql = "SELECT | | | | |