| In this example I show how to create a table of similar | | | | GetMiSQL |
| data that may only have a date in difference. For | | | | MiRec.MoveLast |
| example, you want to evaluate how your cash will | | | | Recnt = MiRec.RecordCount |
| flow and you have a table of expenditures. You know | | | | MiRec.MoveFirst |
| that you have regular payments for payroll, loans, | | | | For X = 1 To Recnt |
| taxes etc. that you want to put into your transaction | | | | ItemVal(X, 1) = MiRec(0) |
| data table without having to key in every record when | | | | ItemVal(X, 2) = MiRec(1) |
| maybe only the date is different. An example would | | | | ItemVal(X, 3) = MiRec(2) |
| be, you have twelve tax payments, all the same but in | | | | ItemVal(X, 4) = MiRec(3)xtimes = MiRec(2) |
| different months. | | | | 'Debug.Print ItemVal(x, 1) & " " & ItemVal(x, 2) & " " & |
| This example demonstrates using an array and SQL | | | | ItemVal(x, 3) & " " & ItemVal(x, 4) |
| statements to create the similar records. It also has a | | | | For z = 1 To xtimes |
| function to replace records if you want to change the | | | | RDate = 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 the | | | | SELECT '" & 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 safekeeping | | | | PutMiSQL |
| 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 three | | | | MiRec.MoveNext |
| tables to support it. | | | | Nextgetout: |
| Create the following Module and name it. | | | | MiRec.Close |
| Make sure you set the references to include the MS | | | | End Function |
| DAO 3.6 Library. Cut and paste the following code. | | | | '********************* |
| Option Compare Database | | | | Private Sub BT1_Click() |
| Option Explicit | | | | DoRept |
| Public misql As String, Midb As Database, MiRec As | | | | End 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 Function | | | | Repeats.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 Function | | | | cash.* FROM cash;" |
| Public Function PutMiSQL() | | | | PutMiSQL |
| Dim Midb As Database ', MiRec As DAO.Recordset | | | | MiRec.MoveLast |
| Set Midb = CurrentDb() | | | | Recnt = MiRec.RecordCount |
| Midb.Execute misql | | | | MiRec.MoveFirst |
| End Function | | | | For X = 1 To Recnt |
| Table 1. Cash | | | | ItemVal(X, 1) = MiRec(0) |
| Amount currency | | | | ItemVal(X, 2) = MiRec(1) |
| Cdate date/time | | | | ItemVal(X, 3) = MiRec(2) |
| Desc text | | | | ItemVal(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 will | | | | ItemVal(x, 3) & " " & ItemVal(x, 4) |
| be used to make a copy of your data before you do | | | | For z = 1 To xtimes |
| a replace. | | | | 'change the 08 value in the next line to the current |
| Table 2. Repeats | | | | year or set a value on the form and change it with a |
| Item text | | | | 'variable. |
| Amount currency | | | | RDate = 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. Close | | | | Cash.Cdate= #" & RDate & "#;" |
| and save the table as Repeats. | | | | PutMiSQL |
| Create Form "Transactions" | | | | 'Debug.Print misql |
| Use table "Repeats" as the datasource | | | | Next |
| Put a Form header and footer on the form | | | | MiRec.MoveNext |
| Place all the fields from the table on the form. | | | | Next |
| Place all the field labels in the form header above their | | | | MiRec.Close |
| respective field placed in a single row in the detail | | | | End 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 them | | | | DoReplace |
| 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 in | | | | This should do it. As always, there are many different |
| design view, then click view code from the menu tool | | | | ways to accomplish something similar to this. This |
| bar. This opens the VBA editor. You can just cut and | | | | works 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 Variant | | | | please don't ask. |
| Dim Recnt As Integer, RDate As Date, xtimes As | | | | This example and many others can be found on our |
| Integermisql = "SELECT Repeats.Item, | | | | website at |
| Repeats.Amount, Repeats.Occr, repeats.dom FROM | | | | All of the examples found there are accompanied by |
| Repeats;" | | | | a functioning Access database file. |