Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
Welcome to the skill header Forums. You can ask questions, get help, or help other members out. Join our Forum for free.
Notifications
Clear all
Nov 18, 2024 5:12 am
Okay, I am having a lot of trouble figuring out how code the count bar at the top of the listed forms. Currently I am 2/3 the way of getting the Orders form to work right, whereas I can count Hold and Pending, but the NotInvoicedOrders line is killing me. I have checked all my instructional information on DCount and using the instructions you gave3 in lesson 7 (Stock & Inventory) video. I keep getting Error Message 3464, data type mismatch. (see code that is causing this below)
Private Sub Form_Current()
Me.txt_OnholdOrders = DCount("OrderStatus", "tbl_Orders", "OrderStatus = 'Hold'")
Me.txt_NotInvoicedOrders = DCount("OrderIsInvoiced", "tbl_Orders", "OrderIsInvoiced = '1'")
Me.txt_PendingOrders = DCount("OrderStatus", "tbl_Orders", "OrderStatus = 'Pending'")
End Sub
On Hold and Pending works fine. PLEASE HELP! I do not need the code, although it would help. but a more in depth understanding of how you are using DCOUNT in this application would be wonderful. I think there must be away to count the IsINV field, but I am lost as you see.
BTW, I fixed a small glitch on the OK Stock Items counting your source file. It was counting all records, small change and now it is counting just the Ok Stock Items.
Private Sub Form_Current()
Me.txt_OkStockItems = DCount("StockStatus", "q_Stock", "StockStatus='Stock OK'")
Me.txt_LowStockItems = DCount("StockStatus", "q_Stock", "StockStatus='Low Stock'")
Me.txt_ZeroStockItems = DCount("StockStatus", "q_Stock", "StockStatus='Zero Stock'")
End Sub
Thanks as always, hope this helps everyone else!
4 Replies
Nov 18, 2024 5:23 am
I have to laugh, otherwise I will cry. I did create a q_Orders using the SQL that you used as the record source for the form and it worked. Can I just refer the form record source to the q_Orders? Should not cause any problems as they have identical results. Customers and Suppliers will be really difficult...WILL LET YOU KNOW IF CREATING A NEW QUERY FOR EACH WITH SOME CALCULATING FIELD (like IsInv) WILL HELP.
Private Sub Form_Current()
Me.txt_OnholdOrders = DCount("OrderStatus", "tbl_Orders", "OrderStatus = 'Hold'")
Me.txt_NotInvoicedOrders = DCount("IsInv", "q_Orders", "IsInv = 'No'")
Me.txt_PendingOrders = DCount("OrderStatus", "tbl_Orders", "OrderStatus = 'Pending'")
End Sub
Nov 18, 2024 5:33 am
Yep, Customers is a copy from Suppliers, so I do not know what is intended to be shown on that form. Suppliers looks like we have to wait for invoicing before we can figure some of ciunts. like Overdue and total payables. Up against the brick wall, again!
Nov 20, 2024 1:41 am
First of all, there is no need to create a separate query to get the count of OrderIsInvoiced. It is a numeric(Integer) type of field and you are using commas(String). Us this:
txt_NotInvoicedOrders = DCount("OrderIsInvoiced", "tbl_orders", "OrderIsInvoiced=1")
Nov 20, 2024 3:32 am
Had to change the 1 to a 0 to count "Not Invoiced" worked great. I actually went to a level in my sandbox project of creating a separate query for each of my functionalities Orders, Customers, and Suppliers. In all fairness, I am modifying them all to suit my personal needs. As you can see by the attached screenshot, I am allowing for variations to Customer contact name for a different person in Billing and in warehouse. Would love to share more with you guys as we go along.
