If Statement For Checks and Balances Before Post as Paid Invoice

[burrina]

Ok, I am down to where I am doing checks and balances to make sure the user cannot do something unless certain criteria has been met.



Main form named OrderDetails with 2 subform on it, one for Payments and one for Purchasing Items.
Subform names are OrderDetailsSubform and OrdersPaymentSub


Here is my criteria; NOT sure how to put this together!
'DoCmd.RunCommand acCmdSelectAllRecords
(1) If [Order Details Subform].[Form]![OrderStatus] = "Posted" 'Subform Have To Check Multilpe Records Here, it is a Continuous Form.
(2) If [Order Details Subform].Form!txtOrderDetailsTotal = 0 'Mainform
(3) If [Orders Payment Sub].Form![Balance] = 0 'Subform
(4) CreateNewOrder.Enabled = True 'Main Form used to Post Order as Paid

Ok, Here is latest attempt; Am I missing something? Don't I need a Select Object or something for the select all records?

DoCmd.RunCommand acCmdSelectAllRecords
If [Order Details Subform].[Form]![OrderStatus] = "Posted" Then
CreateNewOrder.Enabled = True
ElseIf [Order Details Subform].Form!txtOrderDetailsTotal = 0 Then
CreateNewOrder.Enabled = True
ElseIf [Orders Payment Sub].Form![Balance] = 0 Then
CreateNewOrder.Enabled = True
Else
CreateNewOrder.Enabled = True
End If
________________________________________________
Realized Code Was Incorrect; Still don't know how to address selecting all records for but the one form.
DoCmd.RunCommand acCmdSelectAllRecords
If [Order Details Subform].[Form]![OrderStatus] = "Posted" Then
Me.CreateNewOrder.Enabled = True
ElseIf [Order Details Subform].Form!txtOrderDetailsTotal > 0 Then
Me.CreateNewOrder.Enabled = False
ElseIf [Orders Payment Sub].Form![Balance] > 0 Then
Me.CreateNewOrder.Enabled = False
Else
Me.CreateNewOrder.Enabled = True
End If
End If

[Rod]

Let's break it down into its component parts. First you want to check that every line in the Order Details Subform is Posted?

OK, there are a couple of things to think about.

1. What if the subform contains no rows?
2. What if the subform contains two or more rows? Your code only checks the current row!

OK, it's a little complex. I would write a small VBA procedure to iterate through all the subform rows and check that each and every one was posted. In order not to disturb the subform I would use a clone of the subform recordset to do this. My first thought is that the procedure needs to be within the subform itself but then - and I've never done this personally - I can see no reason why the VBA procedure cannot be in the main form.

Here, off the top of my head, is the likely code for a main form procedure. (You've been at this long enough now to be able to take my suggestion and adapt it to your application. )


Public Function IsPosted As Boolean

Dim rst as DAO.Recordset

IsPosted = False
Set rst = [Order Details Subform].Form.RecordsetClone
If rst.RecordCount = 0 then Exit Function
rst.MoveFirst
Do Until rst.EOF
If rst!Posted <> True Then Exit Function
rst.MoveNext
Loop
IsPosted = True

End Function

P.S. Sorry, just realised the test should be something like Status <> "Posted"

[June7]

Confused by your item (2) - subform is referenced but comment says 'Mainform'.

You need to check if each of the records in subform is 'Posted' status? Just selecting the records doesn't accomplish anything. Options I know of are:

1. actually move to each record on form and check values (I've never done this)

2. use RecordsetClone of the subform records and search the recordset (I have used RecordsetClone many times). Advise you name subform container control different from the object it holds, like ctrDetails. Then code something like:

With Me.ctrDetails.Form.RecordsetClone
If .RecordCount >0 Then .FindFirst "OrderStatus<>'Posted'"
If .NoMatch = True Or Me.ctrDetails!txtOrderDetailsTotal = 0 Or Me.ctrPayment!Balance = 0 Then
CreateNewOrder.Enabled = True
Else
CreateNewOrder.Enabled = False
End If

EDIT: Rod got there first but we have same basic idea.

[Rod]

Neat! Hadn't thought of using FindFirst!

[burrina]

Compile error:
Method or data member not found

Put code on OnCurrentEvent of Main form. Renamed Subform to ctrDetails ???

[June7]

Which line causes the error?

My suggestion was to rename the subform container control, not the form that is held by the control. So did you rename the control?

[burrina]

I renamed the control but this is getting too complicated for me right now. Can't think of how to implement code. I did rename the Label, not the control itself! I have to think about this because if I rename the control then other things are affected. I will have to give this some thought as it changes my Posted Status and I will have to adjust other code as well and probably queries and maybe forms.

Thanks,
I will share something with you that is personal, and I will only do this once! I am 61 yrs old and have NO Access Training as you have no doubt figured out by now.
I also have Multiple Sclerosis and this affects me mentally amongst a lot of other things. Hence the Albert and the Forest moments of can and cant think.
Thanks for all of your help and just keep that in mind when you see me post on here. I need things to be explained in baby steps a lot of times.
"Sometimes I sits and thinks and Sometimes I just sits"

[burrina]

For those of you who have my database, here is the first draft of the manual for it, just in case you have decided to not delete it! For future references, it may prove helpful.
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]

The code will probably work if the container control is not renamed but it is a good practice as I have found situations where Access is confused if the control is named same as the object it contains.

I also routinely make sure control (textbox, combobox, etc) names are different from fieldnames. As you may know, fields dragged from field list to form/report will be named same as the fieldname. If I change the ControlSource to an expression that uses the fieldname, will result in circular reference error in the same-named control.

I also experience that use of the .Form qualifier is not needed if reference is to a field name of the subform RecordSource but reference to a control or property does need .Form.

My practice is to reference fieldnames with bang (!) as in !Posted but reference controls and properties with dot (.) as in .Form.txtOrderDetailsTotal. The dot will provoke intellisense popup tips.