Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19

    Create Multiple Records from Single Form

    Hi All



    First time here hope you're all well and someone can help with my problem.

    I've created a pricing db that allows our sales staff to price up products and service. This works fine but now the powers that be want to produce customer quotes from this data. Currently the pricing form shows all products in table format as below

    Desc Qty Min Sell Correct Sell Margin
    Product1 1 £2 £3 10%
    Product2 2 £3 £4 15%
    Product3 0 £1 £2 8%
    Product4 0 £2 £4 10%

    When this info is saved I would want Product1 created as a quote line, Product2 as a sepearte quote line but Product3 and Product4 would not be created since they are zero qty. I know i need to write vba to loop through each of the fields to check if qty > 0 but I'm not sure if it is possible to do this for multiple fields on 1 record and then split into multiple records and not sure of the vba.

    Would really appreciate any help given.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear on what you would want to create based on the sample data, but the most efficient method would typically be a query, with a criteria of >0 on the qty field. Perhaps if you can help us visualize the result, we can recommend the best solution.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What I think you're asking for is a method to store and display quotes.

    IF that's the case you wouldn't have an entry for every product you have what you'd likely want is something like

    tblQuotes
    QuoteID CustomerID QuoteDate ---> other quote specific fields

    where CUSTOMERID would be the unique identifier from your Customer Table

    tblQuoteDetail
    QuoteID DetailID ItemID ItemQty ItemCost ItemMarkup

    Where quoteID would be the unique identifier from tblQuotes, DetailID would be an autonumber (unique identifier for this table) ItemID would be the unique key from your Items table, then obviously the base cost and the markup percentage. This is a more normalized structure and will allow you to produce quotes storing a minimum of data.

  4. #4
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19
    Hi

    Thanks for your replies. Sorry if I didn't explain this properly will try to give a bit more detail.

    I have tblcustomers tblpricing, tblproducts. tblpricing has PricingID as its index and fields inc Product1Sell, Product2Sell, Product3Sell etc. The form frmpricing is bound to tblpricing and displays all products so staff simply enter a price in order to calculate the margin. They may not price a particular product so within tblpricing Product1Sell may have a price but Product2Sell and Product3Sell could be 0.

    When the db was first designed it was meant for working out sales margins to ensure staff sold at the correct price. The pricing info was then entered into a different system which was used to produce the customer quote. Now we want to use this pricing info to create the customers quote. I know the tables don't follow proper normalisation rules but the structure is already in place.

    What I now need to do is take one record from tblpricing (Product1Sell, Product2Sell etc) and create a header record in tblquote and multiple quote lines in tblquotelines for only those products with a price against them in FrmPricing

    I hope this makes sense and I really appreciate any help. I realise I'm trying to put a square peg into a round hole.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Because you are designing a new module to your database there is no reason you can't build it to follow normalization rules even though your current table does not follow normalization rules. For instance if your Product1Sell always refers to the same product you can create data entry for a specific product if the sell value is non zero. There's really no reason for you to continue to use a bad table design that you inherited.

    As long as when you build a quote EVERY field that has a non zero value must be filled out for a 'valid' quote to be built it shouldn't be that hard to accomplish. In your 'items' table if you add another field that corresponds to the order of the pricing table then if the value in your pricing table is 0 look up the item from your items table based on it's position in your order table (that may not be terribly clear).

    If you can provide a sample of your database it may be easier to give you specifics.

  6. #6
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19
    Hi

    Thanks for your reply. I can see how your suggestion would work but not to sure on how to code this. My pricing screen is laid out as follows

    Description Min Sell Sell Price Margin
    Product 1 £10 £15 10%
    Product 2 £15 £20 15%
    Product 3 £ 8 £0 0%

    This creates one record in tblpricing and updates fields Product1Sell = £15, Product2Sell = £20 and Product3Sell = 0. Margin is a calculation so is not important. Each product has a unique product id in tblproducts. This is not updated in tblpricing it's simply a lookup to show min sell price on screen i.e there is no relationship between tblproducts and tblpricing. The ProductID is hidden on the form and is used purely used for DLookups.

    Once this pricing screen is saved I need to automatically create 1 record in tblquoteheader and records in tblQuoteLines. In the above example 2 quote lines would be created for only those items with a sell price > 0

    In plain english my logic would be for each SellPrice > 0 Add ProductID and Product1Sell into TblQuoteLines ProductID and ProductSell then repeat for Product2Sell.

    I know what I want to do but can't figure out how to code it. Any help is very much appreciated.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you provide a sample of your database with some fake information in it, it would be far easier to help you with code if I had something to start with rather than building something from scratch.

    Just make a copy of your database and delete any sensitive information and put in some garbage data so at least I have a handle on your current structure.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does this work (correcting names as appropriate)?

    INSERT INTO TblQuoteLines(ProductID, ProductSell)
    SELECT ProductID, SellPrice
    FROM tblpricing
    WHERE SellPrice > 0
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19
    Hi

    Thanks for your reply. I've attached a very cut down version of the db. Some areas might fall over as a lot of code and related screens have been removed to reduce size.

    From the frmcustomer select quote > create > tippers > standard. I need to get all the entries in the section Aggregate Margins (right hand side of screen), create a single entry in tblquoteheader and multiple entries into tblquotelines for each aggregate Sell > 0. You see that tblquote contains all this info in one record but to produce a customer quote this data needs to be created in multiple lines in tblquotelines.

    I hope this makes sense. Again thanks for your help.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok, so you want any item that has a non zero value in the SELL column of the AGGREGATE MARGIN (lower right hand corner) of your data entry screen to have a record in tblQuoteLines and you want to create a record in the tblQuoteHeader table. In the tblQuoteHeader you have QuoteID set to autonumber to you intend that field to inherit the quote number on the form frm68aQuote? Or is this to be a brand new, unrelated number? If it's to be a brand new number I assume you want that ID carried over to the tblQuoteLines table in the QUOTEID field. I'm just checking because you're using QUOTEID in three tables and I'm not sure you intend them to all be the same number.

  11. #11
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19
    Hi

    The table tblquotes was originally called this but we'll call it tblpricing to avoid confusion. I will rename it before making the changes.

    However it would probably be better (and easier) for tblquoteheader to take the same quoteid as tblpricing since they are linked albeit not in access. A quote would not actually be created unless the job / product was priced first and the sales staff would not price a job / product unless they were going to produce a customer quote. They are effectively the same thing but the data structure of tblpricing is the issue. The quoteid in tblquoteheaders doesn't need to be an autonumber, it could just take the Quoteid from tblpricing.

    My thinking was to create a Quote Header form linked only to tblquoteheader. This would contain Quote ID, CustomerID, QuoteDate etc. Then pricing the frm68Aquote is launched as normal from this form. Tblpricing is completed as normal and when saved generates the records in tblquotelines.

    Hope this makes sense.

  12. #12
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19
    Sorry just realised I got that the wrong way round. Tblpricing should take the same quoteid as tblquoteheader since the quote header would be created before the pricing record.

    Thanks

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Here is your database back. These are the changes I made:

    1. In your tblPriceList table I added a field FormName. This field is the identical name to the appropriate field on frm68AQuote.
    2. tblQuoteHeader, changed QuoteID from autonumber to number, added a field PKID (autonumber) because every table should have a unique identifier.
    3. frm68AQuote added a TAG property to all the product fields reading ED (Estimate Detail)
    4. frm68AQuote added a button labeled Prepare Tables with come code in the ON CLICK event of the button to add records to both tblQuoteHeader and tblQuoteLines.

    The code only adds new data. It doesn't update anything. It takes the QUOTEID, Looks up the ProductID based on the name of the field in your products section, NOTE the NAME of the product field must be identalc to the field FORMNAME I added to tblPRICELIST or this won't work, and the price in the FIRST column of the product section and appends that to tblQUOTELINES, at the moment it only adds the QUOTEID to tblQuoteHeader because I don't know where the rest of that information is supposed come from but you can likely look it up from your original quote or you can modify the process to include those.

  14. #14
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19
    Hi

    Thanks so much for doing this very much appreciated. I can modify as necessary to get all the other data as required and I understand the process much better.

    The code updates tblquotelines however the code

    If InStr(ctl.Tag, "ED") > 0 Then

    is not excluding the fields with a Sell Price of 0. If you run the prepare tables code 9 records are always entered into tblquotelines instead of only those with a Sell Price > 0. Any idea how this could be modified?

    Sorry to be a pain but never new the Tag option exisited.

    Thanks again

  15. #15
    mcktigger is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    19

    Fixed

    Hi

    Managed to fix the problem. i changed your code slightly as follows

    If ctl.Tag = "ED" Then
    If ctl.Value > 0 Then
    iProductID = DLookup("[ProductID]", "tblpricelist", "[FormName] = '" & ctl.name & "'")
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO tblQuoteLines (QuoteID, ProductID, SellPrice) VALUES (" & Me.QuoteId & "," & iProductID & "," & ctl.Value & ")")
    DoCmd.SetWarnings True
    End If
    End If
    Next

    This worked perfectly. Thanks again for all your help would never have got there without it. Don't know why we put ourselves through this torture

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating multiple records from a single form
    By secretary in forum Forms
    Replies: 8
    Last Post: 07-18-2011, 04:03 PM
  2. Create Multiple Charts from Single Table
    By Catch Wrestler in forum Reports
    Replies: 0
    Last Post: 06-17-2010, 08:33 AM
  3. Create multiple records with 1 form?
    By bergjes in forum Forms
    Replies: 4
    Last Post: 04-14-2010, 06:16 AM
  4. Trying to create multiple records from a form
    By ed_hollywood in forum Forms
    Replies: 4
    Last Post: 04-02-2010, 10:57 PM
  5. Replies: 3
    Last Post: 06-01-2009, 01:41 PM

Tags for this Thread

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