Blogger BlogNet61187

In this example I show how to create a table of similar data that may only have a date in difference. For example, you want to evaluate how your cash will flow and you have a table of expenditures. You know that you have regular payments for payroll, loans, taxes etc. that you want to put into your transaction data table without having to key in every record when maybe only the date is different. An example would be, you have twelve tax payments, all the same but in different months.

This example demonstrates using an array and SQL statements to create the similar records. It also has a function to replace records if you want to change the amount value and update the data table. The replace function assumes that the description and date are the same and replaces every occurrence of the existing record with the latest amount value. For safekeeping the replace function makes a copy of the data table before executing.

This example requires a form, a module and a three tables to support it.

Create the following Module and name it.

Make sure you set the references to include the MS DAO 3.6 Library. Cut and paste the following code.

Option Compare Database

Option Explicit

Public misql As String, Midb As Database, MiRec As DAO.Recordset, MiRec2 As DAO.Recordset

Public Function GetMiSQL()

Set Midb = CurrentDb()

Set MiRec = Midb.OpenRecordset(misql, dbOpenDynaset)

End Function

Public Function GetMiSQL2()

Set Midb = CurrentDb()

Set MiRec2 = Midb.OpenRecordset(misql, dbOpenDynaset)

End Function

Public Function PutMiSQL()

Dim Midb As Database ', MiRec As DAO.Recordset

Set Midb = CurrentDb()

Midb.Execute misql

End Function

Table 1. Cash

Amount currency

Cdate date/time

Desc text

Once you have created this table close and save it as Cash. Make a copy of it and name it CashBac. It will be used to make a copy of your data before you do a replace.

Table 2. Repeats

Item text

Amount currency

Occr number

DOM number

Set both of the number items properties Integer. Close and save the table as Repeats.

Create Form "Transactions"

Use table "Repeats" as the datasource

Put a Form header and footer on the form

Place all the fields from the table on the form.

Place all the field labels in the form header above their respective field placed in a single row in the detail section of the form. Format the form as a Continuous Form.

Place two buttons in the form footer and name them BT1 and BT2.

Change the caption for BT1 to Create and for BT2 to Replace.

Place the following code in the form. Open the form in design view, then click view code from the menu tool bar. This opens the VBA editor. You can just cut and paste from this file to avoid typing.

Cut Here.

'************************

Public Function DoRept()

Dim ItemVal(12, 4) As Variant

Dim Recnt As Integer, RDate As Date, xtimes As Integer

misql = "SELECT Repeats.Item, Repeats.Amount, Repeats.Occr, repeats.dom FROM Repeats;"

GetMiSQL

MiRec.MoveLast

Recnt = MiRec.RecordCount

MiRec.MoveFirst

For X = 1 To Recnt

ItemVal(X, 1) = MiRec(0)

ItemVal(X, 2) = MiRec(1)

ItemVal(X, 3) = MiRec(2)

ItemVal(X, 4) = MiRec(3)

xtimes = MiRec(2)

'Debug.Print ItemVal(x, 1) & " " & ItemVal(x, 2) & " " & ItemVal(x, 3) & " " & ItemVal(x, 4)

For z = 1 To xtimes

RDate = z & "/" & ItemVal(X, 4) & "/08"

misql = "INSERT INTO Cash ( [Desc], Amount, [Cdate]) SELECT '" & ItemVal(X, 1) & "' AS x1, " & ItemVal(X, 2) & " AS x2, #" & RDate & "# AS x3;"

PutMiSQL

'Debug.Print misql

Next

MiRec.MoveNext

Next

getout:

MiRec.Close

End Function

'*********************

Private Sub BT1_Click()

DoRept

End Sub

'***********************

Public Function DoReplace()

Dim ItemVal(12, 4) As Variant

Dim Recnt As Integer, RDate As Date, xtimes As Integer

misql = "SELECT Repeats.Item, Repeats.Amount, Repeats.Occr, repeats.dom FROM Repeats;"

GetMiSQL

misql = " DELETE CashBac.* FROM CashBac;"

PutMiSQL

misql = "INSERT INTO CashBac SELECT cash.* FROM cash;"

PutMiSQL

MiRec.MoveLast

Recnt = MiRec.RecordCount

MiRec.MoveFirst

For X = 1 To Recnt

ItemVal(X, 1) = MiRec(0)

ItemVal(X, 2) = MiRec(1)

ItemVal(X, 3) = MiRec(2)

ItemVal(X, 4) = MiRec(3)

xtimes = MiRec(2)

'Debug.Print ItemVal(x, 1) & " " & ItemVal(x, 2) & " " & ItemVal(x, 3) & " " & ItemVal(x, 4)

For z = 1 To xtimes

'change the 08 value in the next line to the current year or set a value on the form and change it with a 'variable.

RDate = z & "/" & ItemVal(X, 4) & "/08"

misql = "UPDATE Cash SET Cash.Amount =" & ItemVal(X, 2) & " WHERE Cash.Desc= '" & ItemVal(X, 1) & "' AND Cash.Cdate= #" & RDate & "#;"

PutMiSQL

'Debug.Print misql

Next

MiRec.MoveNext

Next

MiRec.Close

End Function

'***********************

Private Sub BT2_Click()

DoReplace

End Sub

'***********************

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 a lot of time when I want to run several what if's with my cash flow analysis. This example was created with Access 2002 and runs as listed on my system. This is a free example and I can't support it or make changes with out compensation so please don't ask.

This example and many others can be found on our website at www.biomationsystems.com. All of the examples found there are accompanied by a functioning Access database file.

Contact Jon Watson at jonw@biomationsystems.com For more help with Access visit our Web sites atwww.biomationsystems.com | http://www.accessdatabasehelp.com

Are you sick and tired of working the usual round? Waking early in the morning and experiencing the morning traffic jam. After reaching office be pressured by your terrorizing boss. What can be worse? Good news is there's an alternative you can choose from that will eliminate all of these hassles mentioned awhile ago. Working at home is the answer. If you work at home you are virtually the boss of your own office, which happens to be your own home. No more pressures no more hassles. All you need to do is to equip yourself with know knowledge how to handle a home based business. So the main question is: What home based business is there for you? Here are some options you can choose from.

Freelance Writing: This kind of home based job is suitable for those people who have passion when it comes to writing and creating their own composition or articles. Freelance writing is a home based job that gives someone an artistic freedom to express hi or her opinion through writing. Other perks of such home based job is that it allows you to gain improvement and the command of your writing prowess, at the same time increase your know how's about a specific subject while you are conducting the research about the subject you are writing about. Most of the time, communication from the freelance writer to the client takes place via emails and the freelance writer are paid based on the project they have finished.

Freelance Designing: This will be the job for you if you are keen eye for details and somehow artistically inclined. To plunge into a career in freelance designing you should have an impressive portfolio that is already presentable to many. Most of the websites will offer you work in various fields. There will be web designs, graphic designs and even some animated presentations or creations. Thus, working as a freelance designer depends on where your strength and interest takes you. These factors will decide which path you have to take.

Home Tutorial: If you are fond of teaching, this opportunity might be the one for you. Only, in this kind of teaching or tutorial, you don't have to be personally present in a classroom, all you need to have is a compute ad a decent speed f internet connection and he class already begins. Most of the home tutorials are online though sometimes students actually visit the teacher and that's how it goes. For instance, there are Korean kids that seek English tutorial and so they go online to look for an English tutorial. They could find online English teachers or sometimes, depending on their location, an English teacher they could just visit.

Customer Service: Some of the most popular types of customer service include directory assistance and technical assistance. In directory assistance, customers will call you to ask for directions how to get to a certain location. You need to be fact and very familiar with directions. You must equip yourself with knowledge on maps and other travel related information. In technical assistance, people will call you to ask for troubleshooting questions. Most of the time, you will be asked how to operate complicated or just simple gadgets via phone or online. You should be familiar with technical terms and of course operational knowledge how to fix things and other technological gadgets.

Would you like to have a real web site business? Terez Dunn invites you to visit his profitable online business website for everything you need to start and run your own online business. His services include advertising, mentoring, and a full service training and support package to help guarantee your success. Learn more here: http://www.milliondollarblueprints.net

Considering that the great Studio Traffic had been running for nearly two years when I made my debut, I was a latecomer to the get paid to auto surf scene. When I first started to take notice of the get paid to auto surf sites, I looked at the possibilities, did a double-take, looked again and wondered where the catch might be. Surely, I thought, there must be a catch, or can it really be that simple? I'll spare you the suspense and tell your right from the start that the answer was yes, it really is as simple as it appears. Before you go rushing off to spend money with auto surf for cash programmes, let me just stress that it is simple but that is not the same as saying that every get paid to auto surf site is safe. Crossing the road is pretty simple but, if you fail to look out for approaching traffic, it certainly isn't safe. Jumping out of a plane is simple but, if you don't get a parachute and a course of instructions, it's far from safe. So don't try auto surfing for cash at home until you have read the rest of this.

Of course, at the start, I didn't know the answer and I toddled off into the realms of get paid to auto surf, taking baby step investments and breathing sighs of relief every day when the get paid to auto surf sites appeared and I knew my tiny investments were still safe. Considering the idiotic abandon with which I embraced the dreadful money doubler phenomenon, it might seem strange that I was so wary when it came to the auto surf for cash experience. Maybe I learned my lesson from the amount of money I wasted on the money doublers. (By the way, if you happen to know my husband, there is absolutely no need for you to mention to him anything about me and money doubler programmes. Really, no need at all.)

Don't get the wrong impression through me mentioning money doublers and get paid to auto surf programmes in the same article. The auto surf for cash sites are in no way related to the money doublers or run by the same people. The one thing the two types of programme have in common is that they are very easy to do but, whereas money doublers were pure scams, get paid to auto surf sites are real online businesses where you can make money at home. The webmasters and webmistresses who own get paid to auto surf sites are, in the main, honest hard-working people, who want to have a thriving business which will bring them an ongoing income for the future. I'm not saying all auto surf for cash sites are run by saints, the owners are just ordinary people who want to make a living on the Internet, and the industry (like any other) has its share of rogues and thieves who will take your money and run, plus a few inept webmasters who just have no idea how to run a business and lose everyone's money.

So, you might ask, how do I take sensible precautions against getting stung? The answer, like most things, is simple! Investigation is the answer. If you like the look of a get paid to auto surf site, join as a free member and check out the forum (if there is one). Surf to test the quality of surfing and don't invest if the surfbar is unpredictable. Email the owner to ask about the site. If you get no reply, drop the site there and then: any website owner who takes a pride in his business will respond to a member's enquiry. If you get a terse reply, pointing out that the answer to your dumb question is in the site faq's, this is still good: an abrupt reply probably means the owner is busy or has got sick of answering the same stupid questions. The point is that he is human and bothered to respond. Don't even think of giving your money to someone who ignores you from day one.

Do an Internet search for forums concerned with making money online, make money at home, auto surfing, get paid to programmes etc. Read what the members are saying about the sites you are thinking of joining as this is usually a good indicator of whether the site is growing or declining. Don't, however, totally rely on the opinion of forum members because the posts often tend to be a bit hysterical and they are, of course, the voicing of personal opinions and not a handing down of the wisdom of gurus.

Check out get paid to auto surf review sites. I don't mean the one page free websites that have been built simply to advertise one person's choice of programmes and build a downline. Look for serious reviews which are regularly updated, these are more likely to be written by dedicated surfers. There are some very good ones around now: I came across one just this week, where you can sign up for a course of get paid to auto surf lessons and a weekly newsletter. This sort of service takes real energy and commitment. If you read a few of these get paid to auto surf review sites, you will get a feeling for which of them belong to dedicated surfers and which are the work of sloppy amateurs who half the time don't even notice when their recommended programmes have closed down.

Another thing you can do to make life as an auto surfer for cash safer is to tread the path which has already been smoothed out, tiptoe in the footsteps of the people who are already making money from auto surfing. If it is working for them, copy what they do and listen to their advice. The foremost piece of advice any decent auto surfer for cash will give you is "don't ever use cash you could not afford to lose", the second most valuable piece of advice is probably "spread your investments". If you are prepared to be patient, you can build up a decent income from small investments spread over several get paid to auto surf programmes. This way you can enjoy the get paid to auto surf experience and make some money without living in fear of your life savings being carried out to sea on the virtual surf.

Copyright 2005 Elaine Currie

Elaine Currie has a Work From Home Directory and Auto Surf Review at her Plug In Profit Site: http://www.Huntingvenus.com for people who want to make money at home.