Help create a button that takes an old record, copy everything, and generate a new Work Order #.

[LonnieCAR]

The gentleman that originally created my database has passed away and as we get more orders in I have been frantically trying to get something to work. Each time we get a new order I would like to be able to take an old order, click on a button, and keep all the same information while simply generating a new Work Order #. The Work Order# automatically generates for new entries too. Later on down the road, once we’ve solved this issue I would love any help/advice on creating something in this database that tracks order history per customer. This may involve a search bar for each customer that pulls-up a customer by name/number, it may show a list of frequently ordered products, who knows. Any help for my main/first issue would be amazing.Thank you kindly,Lonnie

[June7]

Why would you want a new work order that duplicates data of existing order? Do you also want to duplicate the Order Details?

There is more than one way to accomplish. Example of one:

CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) SELECT field1, field2, field3 FROM tablename WHERE ID =" & Me.ID


Should avoid spaces and special characters/punctuation (underscore is exception) in naming convention, such as the ? mark.

[orange]

The table structures look normalized. I agree with June re the naming, but if it was built before your arrival, that just a past fact. I also do not see the rationale for copying existing workOrders, but you know your work environment and processes better than we do.
I'd like to hear more about the increased workload, or issues inputting new work orders etc.

I have often found that starting with a big picture and ensuring pieces fit together at least at the 30000 ft level is a better strategy than building detail now and we'll redesign when the new features (we know are coming) actually arrive. That is to say, resolve the operation issue you are facing; then get to work on modelling the new features that are being/have been proposed.

Good luck with your project. And remember break it down into steps; and 1 step at a time.

[LonnieCAR]

Each time we make a new record, or order, we print the reports and give them to production to run. We also give a copy to customer service and file the other. We just need to keep a running log of all the orders generated for a company. Like I mentioned before, the gentleman that created this database in the beginning has since passed and I only have novice Access experience, so I was wondering if there was a quick/easy way to accomplish this. Thank you for looking into this for me.

[June7]

That doesn't explain to me why you want to duplicate data as opposed to doing data entry for a new record. All you described would ordinarily follow data entry. What is a 'running log' - doesn't the Orders table serve as a 'log'?

Quick/easy is subjective - depends on your level of knowledge/experience.

[LonnieCAR]

Ultimately. if I could click on a customer and view a list of all the products they've ordered that would be what I need. A lot of the customers order the same thing multiple times a year and I would like the form to simply copy an existing order and give me a new order number and I will enter a new date. I don't really want to lose the past data either.

[orange]

I think we are saying a query, perhaps behind a button on a form, could generate a list of all orders by Customer X in Date sequence. That sort of functionality is quite easy to do. The hard part is to define exactly WHAT you need.

It breaks down into:
- what data do I need,
- where is that data stored in the data base.
- when do I need this data (a process)/end of day/week, on request
- what format (form/query/report)

How many users/operators use this database? Are these simultaneous users?

Is there an operations or Operators Manual--- some documentation?

[LonnieCAR]

I will get this information in the morning and send it your way.

[June7]

Code in post 2 is one method. Would have to run two INSERT queries. One for the Order and one for OrderDetails with the new order number. Capturing the new order number is actually the tricky part.

[ssanfu]

I played around with your dB. There are still things that I don't like...

The WO number is "OrderID", which is an Autonumber.
There is a table, "Products" which is linked to the "OrderDetails", but is not being used. The products info is being entered directly into the order details table.
There is not a table for cover colors. I would have a table for consistency. (and ease of entry)

There were a lot of macros...... I converted them to VBA code and also removed unused code.
I fixed the reports... or at least they don't error when opened.
Added a duplicate order button (code courtesy of Allen Browne)

The form no longer opens to a new record.... have to click the new order button.

How many people are in the dB at the same time? If more than 1, you should split the dB into a FE and BE.


Anyway, see how you like it...

[LonnieCAR]

Oh my ssanfu, that is exactly what I need! Only 1 person uses the DB at the same time, there is only 1 terminal. I need the work order to be an auto number, so I know which one is in line next.

[ssanfu]

Glad it helped

I need the work order to be an auto number, so I know which one is in line next.

This is not how an autonumber is meant to be used.
Read this
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

and this
Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

Pay attention to the third to the last paragraph (starts "If you need a meaningful sequencer.....").
I have seen (once) where the autonumber went negative.

Good news is that you can still have a custom number be created automatically for the WO number.

Good luck

[orange]

Good stuff Steve!