Access 2002 - Auto Create New Records

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

juandeaux

Thread Starter
Joined
Feb 9, 2005
Messages
1
I am in the process of developing an Access 2002-based system while simultaneously learning Access. I have been able to sort things out pretty well with one notable exception. I would like to be able to invoke an action that will automatically create a new record in a Transaction Table for each record that exists in a Customer Table. My logic is that at the beginning of each billing cycle, a transaction record is created which can subsequently be manually processed when payments are received. Is this doable from within Access, or will it require VB or ???
 
Joined
Feb 14, 2005
Messages
3
juandeaux said:
I am in the process of developing an Access 2002-based system while simultaneously learning Access. I have been able to sort things out pretty well with one notable exception. I would like to be able to invoke an action that will automatically create a new record in a Transaction Table for each record that exists in a Customer Table. My logic is that at the beginning of each billing cycle, a transaction record is created which can subsequently be manually processed when payments are received. Is this doable from within Access, or will it require VB or ???
From what I understand, you want to periodically take a "peek" at the Customer Table, and then create a new record for each customer, in a Transaction Table.

Yes, it can be done using Access - but the simplest way to do it uses VBA (within Access).

I am not sure what kind of interface you have (forms, menues, switchboard or what) - but basically you can create a snippet of code that would say something like (this is just a sample... so it won't 100% work if you cut and paste it):

dim db as dao.database
dim rst as dao.recordset
dim sSQL as string

set db=currentdb
set rst = db.openrecordset ("Select * from CustomerTable;")

docmd.setwarngings false 'so you don't see warnings for each insert
while not rst.eof
tempCustomer = rst!customer_id
'.... etc... get the rest of the customer data you want to grab for the transation table
sSQL = "INSERT INTO TransactionTable VALUES ('" & tempCustomer
sSQL = sSQL & "');" 'build the rest of the insert string
docmd.executeSQL(sSQL)
wend

docmd.setwarnings true


This is the easiest (in my opinion) to understand way of doing what you want to do.... and you can add whatever additional fields you want to "tweak" in the INSERT statement.

Good Luck.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top