Subform Caption To Show OrderStatus

[burrina]

I am trying to make my Caption, i.e. Label show if ALL orders have been Posted (Order Status) or not. This is my subform and fired on the OnCurrentEvent.


Of course since the subform is a continuous form when the user clicks on another record the code does not work, it only works for the first time.

lblstatus used as a Caption. Is on subform
OrderDetailsSubform subform on main form OrderDetails



DoCmd.RunCommand acCmdSelectAllRecords
If [OrderStatus] = "Not Posted" Then
lblstatus.Caption = "Some Orders Not Posted"
Else
lblstatus.Caption = "All Orders Are Posted"
End If

[June7]

Does coding method described in https://www.accessforums.net/forms/i...aid-30709.html apply to this situation?

However, as you discovered, setting that property will not work in continuous or datasheet view.

An alternative might be a textbox with an IIf expression that displays appropriate text dependent on values in current record.

[burrina]

A spin off the other post but wanting a different result. That suggestion wont work. I have searched the net over with people wanting a similar result and dont believe they got it. What I really need is a way when the focus shifts to another record, then the code runs again. Or else run a query or something, has to be a way.How do you check to see if a user has navigated to another record? The other similar situation did not give me the result I wanted. Here once again is the old code that I tried to adapt to this situation. Point being to make the txtstatus Label show the status of either Posted or Not Posted for ALL records. I will give that If statement a try and see what happens.
With Me Code errors out on New Record, cant use it, back to square one.


With Me.[OrderDetailsSubform].Form.RecordsetClone 'Checks For Records Not Posted,No Balance.
If .RecordCount > 0 Then .FindFirst "OrderStatus<>'Posted'"
If .NoMatch = True Or Me.OrderStatus!txtOrderDetailsTotal = 0 Or [OrderDetails].Form!Me.Payment!Balance = 0 Then
Me.CreateNewOrder.Enabled = True
lblstatus.Caption = "Posted"
Else
Me.CreateNewOrder.Enabled = False
lblstatus.Caption = "Not Posted"
End If
End With

[burrina]

I have concluded that this cant be done and wont inquire again. Instead I will address this at Post as Paid time to finish up the Invoice.
I will run a query and display a message box and then exit code as the case may be to Post the Order as Paid or Not.

Thanks for everyone's help.

[Rod]

I have concluded that this cant be done and wont inquire again.

Oh, but it can be done - and quite easily - and in more than one way.

How do you check to see if a user has navigated to another record?

The form's Current event fires every time the user moves to a new record (including the first time).

Is it too much to ask you to post your db again? (Or remind me what it is called? I don't think I have a copy but maybe.)

[burrina]

The db is called, sheesh, I have slept since then, but I believe I named it Forum something. I am getting myself so confused here! Sorry! The db actually works fine now except for this Checks Balances thing. Of course I may be overlooking something, it has been know to happen. Ha!
Let me know if I need to upload another copy!

[Rod]

No, I don't have a copy. Could you save it as v2007 before zipping otherwise I shall not be able to open it.

[burrina]

Sure, I save it in Access 2000 format by default.

[burrina]

Before I upload, here is the Manual, as far as I have got with it anyway.
Also, I will be deleting this ASAP after the upload, so dont wait!

Purpose of Database:

To track inventory and customer or company purchased or sold items.

1. File Menu
About Opens About Database Form
Exit Program

2. Menu
Choose between Customer, Employee, Products, Suppliers or Tax-rate forms.

3. Purchase Inventory Items
Main Menu Opens Main Menu

4. Reports
ReportList Opens List of Reports with Date criteria options

5. EU Software Info

“Purchase Inventory Items”

A. Select Company and or Customer
B. Select Employee for Order
C. Select Shipping Method
D. Select Order Date
E. Select a Ship Date if Ready
F. Enter a Purchase Order Number
G. Enter any Comments You Wish in memo.

You have now created an Order!

“Adding items to purchase or sell list”

A. Select a product, it fills in the price and quantity on hand.
B. It shows whether it is in stock or needs to be re-stocked.
C. Enter a new price or use existing price.
D. Enter a discount if you wish.
E. Select a different supplier if you wish.
F. You may cancel the order by clicking Cancel.
G. For your protection, you will be asked to confirm Yes/No before committing.
H. Displayed are the Order Number and Status of each line item.

You have now created a product Order!

Next is to either Post the item to inventory or you can wait until you are finished and Batch Post ALL items at once. “This POST is NOT reversible” You cannot double Post either!


You may now leave order as is or Post as Paid.

“Payments”

A. Payments can be made a number of ways
1. By check
2. By Credit Card
3. Money Order
4, Cashiers Check

B. Partial payments can be made.
1. You have a running total of amount due as payments are made.

“Balance Due Explained”

A.Order Sub-Total

1. Order Total from items sold or purchased.

B. Taxes
1. Taxes can be set 2 ways
a. Use customers tax rate and check apply tax rate box.
b. Or simply enter your own tax rate.
c. It's either one or the other.

C. Shipping and Handling
a. Use customers ship rate and check apply ship rate
b. Or simply enter your own ship rate.

D. Order Total
a. Displays order total before applied payments

E. Grand Total
a. Displays Order Total with applied payments

Menu Items:

Add or Edit
a. use these to add or change data as needed.

Reports and Forms:

1. Reports

a. Post as Paid
Use when order is complete to mark as finished.
b.Customer Listing
Displays your customer list
c. Preview Invoice
Allows you to preview invoice.

2. Forms

a. Displays customer information form.
b. Displays Payment Method information.
c. Displays product information.
d. Displays shipping information
e. Displays supplier information.
f. Displays company information.

“Information”

a. At top of form your Order Status is displayed
b. For specific Date Criteria you may filter the form using the Filter by Order Date Combo Box.

“Customer Form”
a. Where you enter customers data.
b. Convenient Add links are provided to edit or add Terms or ShipVia items.
1. Orders Details Summary Tab
a. Displays the current orders(s) details.


“Categories and Products”

a. Each category has its own list of products.
b. A 4 character limit is used to identify Category
c. Category name, Description, Tax Status and Tax Rate.
d. Add link provided for convenient changes if needed.

“Add or Delete Products”

a. Product entry
b. Price
c. Is item is Stock?
d. Has it been discontinued?
e. How many items on hand, i.e. in inventory
f. How many items sold?
g. Shrinkage, inventory discrepancy.
h. Last price paid for item.
I. How many items received?
j. How many items are below set inventory limit?
k. Set Level at which to NOT fall below.
Highlights and Color Explanations:
1. Blue Color in Last$Paid signifies you paid a greater price than initially paid.
2. Red Color in OnHand signifies you have a lesser quantity on hand than your set level.
3. Red Color is Sold means you have Sold more than you Received.
4. Red Color in BelowLevel shows the difference between OnHand Quantity and SetLevel.

“Suppliers”

a. Supplier form for supplier information.



“Hints and Rules”

Convenient mouse over control tips appear when you hover over a field or click in them.
Every effort has been made to prevent user error and check and balances have been put in place.

[June7]

Think I found a couple of your dbs. One called Forum and one called forumDB. Think it is Forum that has the forms of this issue. Unfortunately, it is from before this label and code design modification.

On what form should the label be and behind what form should the code be? What behavior should trigger code to check for the posted status of records in the subform - when the form first opens, when a new record is added to the subform, both?

[burrina]

I think I should wait a bit before trying to confuse anyone with my db, although for me it is straightforward as I am sure yours would be for you. I am at the point where I need to make sure a user cant Post as Paid the Invoice without the proper criteria. This would be greatly appreciated. Criteria for this is of course a Balance of Zero and NO un-Posted Inventory items.also when a NEW Order is created, NO Product entry or Payment entry should be allowed. Of course also the usual requirements, such as a Customer,Employee,etc before a Order can be saved. I may be missing other stuff, but I a not aware of it.

[burrina]

Okay, maybe you can help me with this. I need 3 criteria as far as I can think right now for an Order to be allowed to be Posted as Paid.
1. No unpaid Balance
2. No Un-Posted Inventory items.
3. Recheck criteria to create order in the first place.
(1) Explained; If a balance is owed,then user cant Post as Paid
(2) Explained; If a Inventory item has NOT been Posted to Inventory, then Post as Paid, Not allowed.
(3) Explained; A Customer,Employee,Shipping Method,Ordered By,Order Date,Purchase Order Number are Required.
(If IsNull([CustomerID]) Then 'Customer is Required.Used on OnCurrentEvent
MsgBox "A Customer is Required"
[CustomerID].SetFocus)

I have a query that I use to check for the OrderStatus of Inventory items to see if they are Posted or not. Here is the code for that.
If DCount("*", "qryAllRecordsStatus") > 0 Then
MsgBox "You Have Items Not Yet Posted"

I also have 2 textboxes on my mainform that show the value of 1. Payment Amount (Balance) and 2. Order Amount (Inventory Items Purchased)
These fields are named txtbalance txtamountdue

My final code should look something like this;
1.DoCmd.SetWarnings False
2.If txtamountdue = 0 And txtbalance = 0 Then
End If
3.If DCount("*", "qryAllRecordsStatus") > 0 Then
4.MsgBox "You Have Items Not Yet Posted"
Exit Sub
5.Otherwise if all is Good, then set OrderStatus as Posted
Print Invoice

Now then, more questions, what if the order needs to be edited afterwards?
What if they want to Print a Partial Invoice?

Does this make things as clear as mud?

[June7]

Are you building this db according to specifications of a single client?

Why does customer order need to be posted as 'paid'? Also, why even have a status field in Orders? The order status should be able to be calculated when needed by just checking the status of related OrderDetails records.

I've worked in accounting department and we never posted customer order/invoice as 'paid'. We used a very expensive and sophisticated product from Great Plains Software (now Microsoft Dynamics GP). Invoices and payments were posted to customer account and a monthly statement issued to show this activity with net balance calculated. Order/invoices could not be edited. Adjusting transactions (returns, refunds, discounts) were entered. I have also used Quickbooks which does allow edit of transactions but not really best accounting practice.

The same can be said about 'posting' product to adjust Inventory. Ideally, enter transactions for inventory received and sold then calculate net balance when needed.

You are probably tired of me saying 'calculate when needed' but think deserves repeating.

Right now the query to update Products is triggered with a button click. So user must remember to click button for each OrderDetail entry? And if they forget, what then? Personally, I wouldn't build this to depend on user. People are too fallible.

I suppose what you want can be done but looks very complicated. What event should trigger the order status update - a button Click, form Close, when adding a new detail, all of the above?

[burrina]

I dont have any real accounting experience hence the design flaws I suppose.Status is definitely needed in order to track the order as to whether it is complete or not.
It has to be tracked somehow. I slipped up with my terminology since I have been building a Sell Inventory db as well. Simply Post the Order and your done if all criteria has been met. You only have 1/2 of the db anyway. Good suggestions though, food for thought. I suppose I could fire the code automatically on after update or when user leaves a specific field? I am no accountant but leaving unfinished orders seems to me to be bad practice. The order can be saved and items posted to inventory later though as it stands. I probably am making this too complicated but I can only think of so many scenarios. I have free rein as far as the client is concerned. I will give this some thought.
Thanks,

[burrina]

Okay, I eliminated the user from having to click on anything.This still brings me to the what if scenario! I have in place the must have criteria to save the record.
As long as the user tabs thru the form all is well, ah but what If? I have the update Inventory fired on the exit event of the cancel button which is the last control on the form.
For those who choose to bypass this then I am in a quandry! As long as the criteria is met and the user moves to another record then the code should fire, but from where and when? On lose focus? The complete Order Status update on the main form is triggered when No Balance is owing and All items have their status as Posted. I think this will come in hand for tracking orders is why! I have not even addressed yet Returns,Credits or Discount Modifications, well kinda sorta i have. Any way to make the code fire if criteria is met and user moves to another record? or what if they leave the record without saving and the form closes?