1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2002 - Auto Create New Records

Discussion in 'Business Applications' started by juandeaux, Feb 9, 2005.

Thread Status:
Not open for further replies.
  1. juandeaux

    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 ???
     
  2. a_klein

    a_klein

    Joined:
    Feb 14, 2005
    Messages:
    3
    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.
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/328639

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice