Results 1 to 5 of 5
  1. #1
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51

    Auto Create Standard records when a new customer id is created

    Hi everyone,

    I am in the middle of creating a new fee database that will track our customer's fee schedule and ultimate use it to export and import the data into our fee system.

    First of all, sales people usually needs to create a fee schedule for each customer, the fee schedule consist of company level fees, and account level fees. Ideally the Sales people will maintain the fee in the database, and then once operations group complete the setup they will go in and activate the fee. What I am trying to do is streamline the process for Operations group so that they don't have to re-key the fees into the fee system.

    I started creating multiple tables but stuck at how I can "auto-create" all standard fee records when a new company is created. Another word, I want the database to create 150 fee records from the "standard fee table" every time a new customer or account number is created in the customer or account tables. I have tried doing the Append query, but unable to add the customer id or account number during the "run query" process. It will append the record to the table, but without the Customer ID or Account Number, which means I will have to run another "update query" to update. I am not sure if this is the best way to achieve this task. I feel like if I have to do 2 separate queries, there will be room for mistake especially if multiple user is in the database and access won't know what account number or customer id to update.

    I have also tried creating a "listbox" where users will select all record from the standard fee table, and then select an customer id from the combobox and then click on "Create". it works, but not sure if it is the most user friendly way to do it.

    Can someone advise what will be the best approach to handle the "auto-creation" of records automatically?

    Thank you in advance!!
    Attached Thumbnails Attached Thumbnails relationship.png  

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    have a macro to run the append queries to add the fees.
    (im guessing a table of fees will have Add2NEW field for new clients)

    when a form is opened to add new record, there is no # for the autonum field.
    private bIsNew as boolean

    'form ON LOAD
    bIsNew = true


    so on the form AFTERUPDATE and is a new record, then run the macro.
    the queries in the macro use the ID (autonum) field to post to the fees table.

    Code:
    sub form_afterupdate()
    if bIsNew and not null(me.ID) then docmd.runMacro "mAddFees2NewClient"
    end sub

  3. #3
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51
    Can you break it down for me? I am not sure if I am following.

    Is this correct:
    1) create an unbound form with 'form ON LOAD' code
    2) Run "afterupdate" command button to do the append query?


    how do get to add the "company id" or "account number" to the new records? I want the fee codes in each of the company id or account number to be unique, as they should only have one unique fee record per customer.

    Thanks,

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you don't want to use macros and queries, here's a little DB that uses SQL to draw records from one table (fees) and inserts them into another table (tblWhat). It included the customerID (Cust_PK from the form) inserting into tblWhat along with the fees.
    Maybe you can find what you need to adapt to your situation.
    Last edited by davegri; 06-12-2018 at 02:57 PM.

  5. #5
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51
    I don't mind using the append query, but I don't understand the steps of how to create the extra field in the append query. Can you help detailing out the steps?

    Thanks,

    Quote Originally Posted by ranman256 View Post
    have a macro to run the append queries to add the fees.
    (im guessing a table of fees will have Add2NEW field for new clients)

    when a form is opened to add new record, there is no # for the autonum field.
    private bIsNew as boolean

    'form ON LOAD
    bIsNew = true


    so on the form AFTERUPDATE and is a new record, then run the macro.
    the queries in the macro use the ID (autonum) field to post to the fees table.

    Code:
    sub form_afterupdate()
    if bIsNew and not null(me.ID) then docmd.runMacro "mAddFees2NewClient"
    end sub
    Quote Originally Posted by davegri View Post
    If you don't want to use macros and queries, here's a little DB that uses SQL to draw records from one table (fees) and inserts them into another table (tblWhat). It included the customerID (Cust_FK) inserting into tblWhat along with the fees.
    Maybe you can find what you need to adapt to your situation.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-16-2017, 05:50 AM
  2. Replies: 2
    Last Post: 12-29-2016, 11:42 AM
  3. auto create records
    By lockstock in forum Programming
    Replies: 16
    Last Post: 09-22-2015, 02:50 PM
  4. Handling inequalities on records. Is there a standard way to do it?
    By Javier Hernández in forum Database Design
    Replies: 2
    Last Post: 07-11-2014, 07:55 AM
  5. Auto create records
    By RamiMohaisen in forum Programming
    Replies: 4
    Last Post: 05-16-2012, 07:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums