Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    10

    Unanswered: simplify order process, to many reports

    Hi I have a form with 30 rows each consisting of combobox and 4 text boxes. The user selects a product from combo then enters number required in the quantity textbox and other criteria in the other textboxes. At the end of the row is an order button that passes the values to a report which is then saved as a .txt file which is then emailed as attachment to the suppliers. All this is done in the button click event with a call to a mail module. The user will work through the 30 rows selecting and ordering. Each row could be a product made by a different supplier therefore a seperate report has to be created for each row with values based on the controls of each individual row. This is something I would rather avoid, is there a way of simplifying this? so I dont have to create all those reports.

    TIA

    B

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by bobnz
    Hi I have a form with 30 rows each consisting of combobox and 4 text boxes. The user selects a product from combo then enters number required in the quantity textbox and other criteria in the other textboxes. At the end of the row is an order button that passes the values to a report which is then saved as a .txt file which is then emailed as attachment to the suppliers. All this is done in the button click event with a call to a mail module. The user will work through the 30 rows selecting and ordering. Each row could be a product made by a different supplier therefore a seperate report has to be created for each row with values based on the controls of each individual row. This is something I would rather avoid, is there a way of simplifying this? so I dont have to create all those reports.

    TIA

    B
    Hi bobnz and "Welcome to the Forum"
    I would think you should have a MainForm/SubForm with the MainForm being based on the Vendor that carries or makes the products. Then in the SubForm you have a list of all the Products that the vendor carries or makes.
    That way you just have one Vendor report that shows and pulls in the data that you selected in the Products SubForm. Only ONE report per Vendor. Make any sense? Just a matter of having table relationships set up...One-To-Many.

    hope this gives you an idea to look into,
    have a nice one,
    Bud

  3. #3
    Join Date
    Aug 2002
    Posts
    10
    Thanks for the reply Bud, Unfortunately the form has to look like the word document the client currently uses ^%$%$ annoying to say the least.

    B

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by bobnz
    Thanks for the reply Bud, Unfortunately the form has to look like the word document the client currently uses ^%$%$ annoying to say the least.

    B
    HI again bobnz,

    Tried to send this from the link in email and it trashed it all. Anyway, what I was saying is, my suggestion had to do with how you have your database setup behind the scenes. In my Inventory database, I select a Vendor at the top and all the products for That Vendor populate the lower form in Datasheet view..(spreadsheet look). Then I select the item(s), input the Quantity, then Click the CheckBox to set it up to Print later. Then it is sent to the Report to print or email or whatever.
    Now if I were to select another Vendor and repeat the process, there will be yet another report for THAT Vendor and it's products ONLY. One Vendor per Report. You can design your Forms/Reports however suits you. For the user inputting the data, the form can look pretty much any kind of way. But for the output to the recipient, you can duplicate the design of whatever form they are using as your Report. No need to create many Reports, just the one will do as it dumps the data into it per Vendor.
    IF you have a copy of what your recipient on the other end is using even better. Create a WordMerge document with the wizard in Access. It's pretty easy. It will be based on your Query that your Form/Report is based on. Only the data will be sent directly to Word to fill in the blanks in the PreDesigned form. The Merge it with MS Word Wizard on the toolbar will take you through the process and will only have to do it once. Otherwise, the Report as described above will do the trick. See copies (screenshots) of my database from my Inventory database.

    (1 of 3) First I select the Vendor at the top of the form. Then the products for that Vendor only populate the form below in DatasheetView (spreadsheet look).

    (2 of 3) Next, I find the item I want, Input the Quantity, Click the Print check box which sets it up to print all items chosen when I am done. At this point I can go select yet another Vendor and repeat the process.

    (3 of 3) Finally the end result. The report that reflects all that was chosen in the form. IF another Vendor was chosen, that report will come on page 2 and so on. I can at any time Print as I go along OR select them all and hit the PrintPO button which prints all that have checkboxes selected to print.
    This should give you what you need IF your database is set up in the proper manner. ONE report for various data.


    hope you have a great day,
    Bud
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2002
    Posts
    10
    Hey Bud thanks for the replies and the effort put into them.
    My client is adamant that the order form has to look like the word document that they where using. Basically this document lists all the materials needed to build a kitchen, when they get an order for a kitchen they use this form to order the preferred materials.
    So the oven combobox lists all the different ovens and the one the client wants is selected and an order sent off to the supplier. It can take upto 3-4 weeks for this form to be completed, once it is, a report that looks just like it is printed off. This then becomes the worksheet for the guys who build the kitchen and is sent out back along with plans. It would be easier to create an order report that concatenated orders for the same supplier, but then orders wouldn't be placed until the form was completed so delivry could be delayed for 3-4 weeks which is not good.

    thanks again for your efforts

    Bob

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •