Duplicate Orders

[jsanders]

It seems I continue to run into this problem.

A company sells products in packages.

Example: Let’s say you build PCs

All of your PCs are sold as a complete product with 10 or so components

Your choices are to reenter every item each time you make a new quote, or automate the process.


This is the current method

Step1:
Create multiple tables identical with the orders detail table, and create all of the records needed to satisfy the order packages. Each table is one order package.

Step 2:
Create an order as you normally would.

Step 3:
Change the linking number in the package table (to the appropriate order number)

Step 4:
Paste the contents if the table into the Order Detail Table.

This works well but is labor intensive, and is not error proof.

So finely the question. Is there a way to automate this process with the selection being made form either a drop down list or a query .

My gut tells me it a macro thing but I don’t use macros for much and my VBA skills are limited to adapting code that I pick up here and there.


Any guidance would be greatly appreciated.

Sincerely,
Joe

 

[jsanders]

Hmm maybe I got it we'll see

I was just thinking that an append query might satisfy this condition.

You could make it get its filter data from a dropdown on a form and it could get the order number from a form.

I just saw a problem

Can you make an append query change a field value as it appends data to a table.

I’m going to go find out.

 

[KeithIT]

Wow. Well to start with change your structure!

You should have a table with customer information, another with order information, a third with package information, and a fourth linking the customer with the order numbers (so that a customer may have more than one order). Set a unique ID for each field in each of the tables (AutoNumber works best for this, but a formula based on the customer information, order number, etc might also work... gets tricky though. Remember, this unique ID does not have to be the order number, just a way for the db to keep track of everything).

You'll have to set up a field in the orders table that links to the package ID in the packages table so that you can keep track of what items went into which packages.

Now you can create forms which show you the customer information, order information and packing information (sub-forms works well here if you know how to use them). You can then put code behind some of the form events to create exactly what you are looking for.

Of course this only helps you with the basics. You probably want to have items in another table, etc etc.