Need help creating Link/Button in cmd mode to change info in field of status of order

[Stephanie53]

Ok this is what I need to have done:

New Quote to Order
Order to Accounting
Accounting to Production
Production to Quality Control
Quality Control to Shipping
Shipping to Accounting

I need to have command button on the Order form for each department to advise to where the status of each order when a customer calls it in. It will flow like this:

Customer calls in for a Quote - Quote will be emailed to them for approval
Customer then calls back and approves Quote - it now becomes an Order
Accounting now needs to put this in Peachtree for the financial part of this for billing
after that is completed then Production will produce the product -
after Production is complete they change the status of this order to Quality Control


After Quality Control they change the status to Shipping
After its shipped then Ship Dept changes it to Billing

This way each department can run their daily reports and schedule their day accordingly for each status to be completed.

Now so some of these status must have approval of management first and they will have the privilege of changing the status ONLY in some aspects i.e. Quotes MUST be approved by Management before sending to Customer (I will have the privileges set for each person on what they can do in each stage)

If someone can help me create the code for the first 2 status I can take it from there

Here is the list of Status in Order

Status ID	Status Name
0	Quote
1	Order
2	Peachtree
3	Production
4	QualityControl
5	Shipping
6	Accounting
7	Completed

Any Assitance would be greatly appriciated

Thank you in advance

Stephanie

[June7]

You have a single status field in the Orders table and users will update that field as order is processed? Don't care about history of these status changes: who, date, comment?

Not really understanding what code you need. If user has the textbox available for entry/edit then they do that.

[Stephanie53]

Hi June and thank you for coming to my rescue ONCE again :-)

Yes your right I do need that included in it. remember yesterday they said they wanted to add things to this? Well this is the start!

I already have this created is this will help you:

Public Enum CustomerOrderStatusEnum
    Quote_CustomerOrder = 0
    Order_CustomerOrder = 1
    Peachtree__CustomerOrder = 2
    Production_CustomerOrder = 3
    QualityControl_CustomerOrder = 4
    Shipping_CustomerOrder = 5
    Accounting_CustomerOrder = 6
    Completed_CustomerOrder = 7
End Enum

Please let me know what other information you may need and YOU ARE THR BEST! :-)

Stephanie

[June7]

If you want history of status then need a table for that:

tblStatusHistory
OrderID
DateEnter
WhoEnter
StatusCode

Now on orders form can have a subform for that data entry.

I've never used Enum declarations and that is really a secondary consideration in developing this feature.

[Stephanie53]

Ok maybe not understanding this part *If user has the textbox available for entry/edit then they do that*

We do need record of who updated the status, the date it was done and if there is any comments but we dont want it typed in where it can be edited. We want it to record from when they click on it then it shows in a report incase there is question on it. As for comment yes I can put in a Text box for them to type in

So how would I do the first part to where it will state if a Status report was done, or a Production report was done that it can seperate the 2 and only pull up Production Status only in the report

They want to keep a close tract of product from the time the customer calls it in and until its shipped out and billed.

Again appreciate your help in all this

TIA

Stephanie

[Stephanie53]

ok maybe I am missing something here ... I need the Orders to have the Status on them

Ok here is the form that I need:



up at the top I want the right department to be able to click on it, have the person clicking on it do these things:

When click on the button/link that it changes the status in upper left to the department that is listed in the buttons (except when it opens up that its already in Quote Status so New Quote would open another one) (the one on the bottom that is for the status of product/inventory like instock, no stock, allocated, etc etc)

How would I direct when they click on button (they will be signed on to their name for the privileges) that it would go to a subform that is not really apart of this order form (that this is for management to view the process on the who, when and comments). Can that be sent to another table/query by code without having to create another form?

Also I want it to where that they can not bypass the process until the previous department is completed with their process. And before it reaches Peachtree that they have the customers ship to address filled out on Tab 2.

I know I am probably driving you crazy here and I do appreciate all that you are doing here

TIA

Stephanie

[June7]

Orders will have status info but in a related child table, if you want history. Displaying the latest status for the order on the form is possible but will be tricky.

Are those command buttons for the various status levels at the top of the form? You can control availability based on who is logged in and the current status of the order. Code might be complicated.

Yes, can create record in another table with code behind the Orders form. Example:
CurrentDb.Execute "INSERT INTO StatusHistory(WhoEnter, DateEnter, StatusCode) VALUES(" & Me.UserID & ", #" & Date() & "#, " & strNewStatus)

[Stephanie53]

So I would created tblStatusHistory and link it to OrderDetails as the Master? Is this done in the properties of the form or the table of OrderDetails? or in the properties of tblStatusHistory?

yes those are the command buttons at the top for the various levels - I need that each level not to be bypassed until the previous level is complete. For example:

Quote Status - Customer calls back and has approved this to become an Order - as long no price change override the Salesman can change it to Order - if there is a price change (which would be another code to which we will come back to later) the Supervisor will have to release it to Customer for approval of Quote before placed in Order Status. I have to also make sure just as they Click on the command to go to Order that is where Shipping Info (where its going to and the date its being shipped THIS IS A MUST for Production) is filled out (not who the shipper is as that is done in the Shipping Department when it gets to that level)
Order Status - Once its in Order Status then Peachtree department can get a copy of this by running a report of open orders by date and change it to Peachtree status
Peachtree Satus - Peachtree after getting the open orders into their system and when they are done then change it to Production Status
Production Status - Production department is able see the Orders by Shipping Date (which I can create report for this for Production Status) Once product is complete they then in turn will click on QC (QualityControl) so that the QC Department can inspect the product, get the paperwork necessary together
QualityControl Status - After they finish with everything as stated in previous they in turn will click on Shipping command
Shipping Status - the Shipping department then will contact Who the shipper is and schedule it for pickup and delivery - they will then provide a tracking number (to which I will need to create a Note Text Box in the order form under Shipping so they can put in the tracking information) After they have verified that it has shipped then they will click on the Accounting button
A/R Status - they will in turn be able to finalize the Order thru PeachTree

I have created the following:

tblStatusHistory
ID AutoNumber
OrderID Number Disply Control: List Box Row Source Type: Table/Query SELECT [Order Details Status].[Status ID], [Order Details Status].[Status Name] FROM [Order Details Status];
DateEnter Date/Time Date and Time each status was completed and by who
EmployeeID Number Disply Control: List Box Row Source Type: Table/Query SELECT Employees.[First Name], Employees.[Last Name], Employees.[Job Title] FROM Employees;
StatusID Number Disply Control: List Box Row Source Type: Table/Query SELECT [Order Details Status].[Status ID], [Order Details Status].[Status Name] FROM [Order Details Status];

Now how exactly would I link this table to the order without having to create fields and make each button send this info to this table?

Thank you again :-) and if you need more info let me know

Stephanie

[June7]

I expect there is more than one way to manage this status info. The simplest would be a subform bound to tblStatusHistory. The most difficult probably the multiple status buttons you are now trying. Somewhere in between would be an unbound combobox. All of them are complicated by any requirement to limit user options and validate selection. Also, might want to prevent multiple records for the same status for the same order - more complication. I don't want to do the work effort required to build and then instruct you on how to replicate. This is a very involved exercise in logical reasoning and code application that you will have to tackle.

As a for instance, why have a list or combo box for EmployeeID? If you capture the Employee info from user login this value can automatically be recorded without user interaction - so John can't select Joan from list, John is the logged in user so only John's ID would be recorded.

[Stephanie53]

We just lost our server so when have access to it I will get back to this one. Now on cell phone Lol

[Stephanie53]

Ok I have removed the Selection under EmployeeID as you are correct on that one but how would it refer to Employee on the subform if not able to select recordsource? Do I just select EmployeeID to refer it to there?

ok lets take this idea of the subform - i do know how to add a subform to the order but here are my questions regarding this...

1. how can I just put the StatusID combobox from this subform to only show that on the Order form and not the rest from that subform? From what you said I will not need to add the field EmployeeID field nor would i need to add OrderID field that is created in the subform as that generates from the Order being placed and be double records on the same form and could get confusing to some..even me.
2. in doing the above will it record to the table or should i have it record to a query?
3. How would this relationship work in references to the other tables? would I have it going this way: OrderID to EmployeeID to StatusID?
4. and if able to create this combobox to have selection of the status can I under the OrderStatus table with each status put a restriction on it that refers to the employee privileges?

I know that this is sounding like a lot and I am trying to make this as easy on them and especially on me and when is all said and done I will be taking donations on purchasing a wig for all the hair I am losing by pulling it out :-)

Thank you again for your time here and your patience

Stephanie

[June7]

The simplest answer to all is 'code, code, and more code'. The more 'user friendly' the more code is required.

The user ID can be determined when they open the database and stored somewhere for future reference by code. In my db I pull the user's network login ID, do a lookup on the Users table to retrieve their initials and permission level. I store those values in textboxes on form (MainMenu) that never closes so that info is always available. Then when record is saved that requires the initials, code populates the field.

1. Don't really understand the question.

2. Of course data will record to table, by some means, one option being user selects status in combobox of subform record

3. Don't really understand the question. Each of those fields will store foreign key value. The StatusHistory table is really a child of the Orders table but the other keys are to retrieve related info by joining tables. Although I have suggested saving the actual status text instead of a key so as to eliminate one join requirement.

4. Yes. Have a field in StatusTypes table for the permissions info (maybe department ID) or use the permission level from Users table as the criteria.

[Stephanie53]

Can I see your DB on how you do that with the user ID?

1. I have created the form from tblStatusHistory - it has the fields OrderID, DateEnter, EmployeeID, StatusID - now the question is the Field in StatusHistory named StatusID (which for some reason its bringing over the ID Number and not the status name itself so I think i need to have that recordsourse from the name and not the id number), can that be the only combobox to bring over as a subform on the OrderDetails Form for the employees to select from a drop down of which status it pertains to the need at that time of that order but record the info of who did, date and what order besides the status?

2. I think that kinda answers my question on that but not if i can just bring over to the OrderDetails form as described above.

3. were you talking about this: Yes, can create record in another table with code behind the Orders form. Example:
CurrentDb.Execute "INSERT INTO StatusHistory(WhoEnter, DateEnter, StatusCode) VALUES(" & Me.UserID & ", #" & Date() & "#, " & strNewStatus) (of course I would change the info in this accordingly but my question would be where would i put this executable?

4. I am still getting the information of all the permissions from the upper management here so I can finish my excel spread sheet in the format to bring to access for the privileges table here.

TIA

Stephanie

[June7]

Old version of my db available from link in http://forums.aspfree.com/microsoft-...hs-445065.html

1. Yes, that is possible - just need code

3. That was described as an option if you used the multiple status buttons approach, but not necessary if you have a subform for StatusHistory. Where to put code is always the tricky part. Should it be combobox AfterUpdate or a button Click or form BeforeUpdate? Depends on situation.

[Stephanie53]

June,

I think I got this working!! So far all codes I am creating for each Link/Button is working in accordingly to the way they want it..SO FAR lol

I will mark this one solved and if I have any more issues I will create another Post

Thank you for all your help :-)

Stephanie