Solved How to make vba check all records in continuous form not only top one record

[TMK]

i have a continuous subform called order details linked to a single form called order, in that, now i have the order status like processing, shipped, completed and returned in the main form order, in that I am able to put multiple processing orders, but after customer pays i can set to shipped status but i want to do that only if the items all in the subform have quantity on stock, i got all of it to work but, since the subform is continuous it only checks if he first item is in stock not the rest, what do i do

 

[arnelgp]

you need to Loop through each record of the subform's recordset"

With Me.subformName.Form.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveFirst
        Do Until .EOF
            'check if item has

            .MoveNext
        Loop
    End If
End With

 

[TMK]

where should this be pasted, im quite new to vba, here is my code so far:

 

[Gasman]

If you are going to Recalc every time, no need to put it everywhere. Just put it at the bottom after all the ElseIf, which you already have done?
I myself would prefer a Select Case.

 

[GPGeorge]

i have a continuous subform called order details linked to a single form called order, in that, now i have the order status like processing, shipped, completed and returned in the main form order, in that I am able to put multiple processing orders, but after customer pays i can set to shipped status but i want to do that only if the items all in the subform have quantity on stock, i got all of it to work but, since the subform is continuous it only checks if he first item is in stock not the rest, what do i do

Check out the Northwind Developer Edition template. We demonstrate how to do this.

 

[MarkK]

I would not embed this kind of business logic in the UI. Consider writing a service class, like cOrderService, and expose this logic as a public method. Then, in your UI, just do...

Private svcOrder As New cOrderService

This serves to...
• Simplify the code in the UI, making it easier to update.
• Make business logic portable, and available to a more diverse set of consumers.
• Provide an authoritative location such that if business rules change--and they will--you know exactly where in your code to find them.
• Reduce the chance of duplicate/conflicting business rules.
• Expose/consume your own business rule methods via intellisense.
hth

 

[arnelgp]

what is the code you are using to check if an item has available stock?

 

[Mark_]

want to do that only if the items all in the subform have quantity on stock

Do you have a query that returns all sub items AND their current quantity in stock? I am guessing this is what your Subform works off of?

If so, a one time call to DMin() can be a quick "Check". If DMin() returns "0" you know you are out of stock on SOMETHING. This would be on your parent form.
------------------------------
As this is for shipping, does your business allow partial fulfillment? If yes, you should have "QtyOrdered" and "QtyShipped" in each line item, at minimum. More robust would be to have a child file that links shipped items to the shipment that was sent and would contain the QtyShipped for each item. Query would then calculate "QtyShipped" by summing the shipped items. You would then calculate "QtyOutstanding" for each item. Once all items are shipped (Sum on QtyOutstanding = 0) you'd then know your entire order has shipped.

Same logic works for accepting partial payments if you account by shipment rather than just "Customer balance". This gets in to a "Check with your accountant / Fiscal reporting" area, but something to keep in mind. Calculating deferred revenue can be very tricky.

 

[TMK]

Do you have a query that returns all sub items AND their current quantity in stock? I am guessing this is what your Subform works off of?

If so, a one time call to DMin() can be a quick "Check". If DMin() returns "0" you know you are out of stock on SOMETHING. This would be on your parent form.
------------------------------
As this is for shipping, does your business allow partial fulfillment? If yes, you should have "QtyOrdered" and "QtyShipped" in each line item, at minimum. More robust would be to have a child file that links shipped items to the shipment that was sent and would contain the QtyShipped for each item. Query would then calculate "QtyShipped" by summing the shipped items. You would then calculate "QtyOutstanding" for each item. Once all items are shipped (Sum on QtyOutstanding = 0) you'd then know your entire order has shipped.

Same logic works for accepting partial payments if you account by shipment rather than just "Customer balance". This gets in to a "Check with your accountant / Fiscal reporting" area, but something to keep in mind. Calculating deferred revenue can be very tricky.

yes, it does but it gives a vba error runtime error 3, as of my guess there is a query inside and other query as my stock dtl is a query and orderdtl is also query

 

[Mark_]

Error with the query would be a separate issue. Are you currently getting help with that?

Back to your original question though, once you get your query corrected you should be able to use DMin() as a check.
More important, does your business allow partial shipments? If yes, are you already dealing with them?

 

[TMK]

Error with the query would be a separate issue. Are you currently getting help with that?

Back to your original question though, once you get your query corrected you should be able to use DMin() as a check.
More important, does your business allow partial shipments? If yes, are you already dealing with them?

nope no partial shpping

 

[TMK]

Error with the query would be a separate issue. Are you currently getting help with that?

Back to your original question though, once you get your query corrected you should be able to use DMin() as a check.
More important, does your business allow partial shipments? If yes, are you already dealing with them?

no help with the query too

 

[arnelgp]

upload your db to quickly resolve this issue.

 

[Gasman]

yes, it does but it gives a vba error runtime error 3, as of my guess there is a query inside and other query as my stock dtl is a query and orderdtl is also query

I hate it when people expect others to know the error messages by heart.

Anyway, error 3 is nothing to do with a query that I can see?

? error(3)
Return without GoSub

 

[TMK]

I solved it!

Since is get the error when i add the query to the query been used by the form, I added an empty field to the table instead, and in the form i did a dlookup to get if the stock is available form the query and get the value for each record automatically and recalc data when i login, and then and a text box to store the value in the table, and then i can get the count of all records and count of no stock records and carry on forward