Section 17.4. Using VB to Run a Better Business

17.4. Using VB to Run a Better Business

Over the last 16 chapters, you've come to know and love the Boutique Fudge data-base, which demonstrates a practical sales database that tracks customers, products, and orders. However, although the Boutique Fudge database stores all the information you need, it still doesn't integrate seamlessly into company life. And before you can fix it, you need to understand why it comes up short.

Most people who work in a business like Boutique Fudge aren't thinking about tables and data operations (like inserting, updating, and deleting records). Instead, they're thinking about tasks , like placing an order, shipping an order, and handling a customer complaint.

Many tasks match quite closely with a data operation, in which case you really don't have a problem. The "register a new customer" task's clearly just a matter of opening the Customers table, and then inserting a new record. You can take care of it with a simple form. However, the "place an order" task's a little trickier. This task involves inserting records in more than one table (the Orders and Order-Details tables), and using data from related tables (the Products and Customers tables) to complete the order. You can create an ordinary form to do the job, but the form doesn't quite work the way salespeople want (see Figure 17-10).

The same's true when it comes to the "ship an order" task. This task requires several stepschanging the status of the order, logging the shipment, and updating the units-in-stock numbers . You could deal with this task as several separate data operations, but it's a lot nicer if you create a single form that takes care of the whole process.

Now VB's truly useful. With the right code, you can design an intelligent form that fits the way people work. An intelligent form isn't just a way to add, edit, and insert records in a tableit's a tool that helps run your business.

Figure 17-10. This form lets you insert records into the Orders and OrderDetails tables. However, it lacks a few frills people expect in an order formlike a way to automatically fill in the price of each product you're ordering, the ability to calculate totals as you go, and an option to add a new product on the fly.

In the following sections, you'll consider how to build better forms with some code- powered features. The following forms are covered:

  • PlaceOrder lets you create a new order. It works in conjunction with the PlaceOrder_Subform, which lets you add individual items to the order.

  • AddProduct lets you create a new product. You can use it directly from Place-Order form to add a product in mid-order.

  • ShipOrders lets you update an order with shipment information. It also works with a form named ReviewOrderDetails to show the items in the order.

You can check out the final result with the downloadable databases for this chapter (Section

Tip: It's always a good idea to name your form according to the task it performs (placing an order, shipping a task, and so on), rather than the table it uses. This design helps you remember who's using each form, so you can tailor it to the right audience.

17.4.1. Keeping a Running Total

Few souls are brave enough to place an order without knowing exactly how much it costs. A typical order form shows how much each line item costs (by multiplying the price and quantity information) and the ever-important grand total (Figure 17-11).

Figure 17-11. The PlaceOrder form, with subtotals and a grand total.

Note: The PlaceOrder form also introduces a few refinements you already know about, like putting customer address information on a separate tab, moving the automatically generated fields (the order ID and the order date) to the bottom of the window where they won't distract anyone , and setting their Locked property (Section 13.2.2) to Yes to prevent changes. The form also has its Data Entry property (Section 12.3.6) set to Yes, so you start creating a new order as soon as you open the form.

The line total's the only detail that doesn't require code. In fact, you can solve this problem by adding a text box that uses the following expression to the PlaceOrder_Subform:

 =Quantity * Price 

This expression works because the information you need (Price and Quantity) is located on the same form as the calculate field. However, the grand total isn't as straightforward.

To be even a bit nicer, you can combine this with the Format function (Section 7.2.5) to make sure you get the right number of decimal places and a currency symbol ($):

 =Format(Quantity * Price, "Currency") 

In order to calculate the grand total, you need to use the Quantity and Price information in the OrderDetails table. Unfortunately , the PlaceOrder form doesn't have any easy way to get this information. Not only is this information shown somewhere else (in a subform), but it also involves several separate records. Even if you retrieve the Quantity and Price information from the subform, you can get only the values for the current record, not for the whole list of ordered items.

To solve this problem, you need to use a specialized Access function called a domain function . A domain function can process an entire table and return a single piece of information. (To learn more, see the box below.)

Become a Master of Your Domain (Functions)

The domain functions are like the grouping functions you use in a totals query (Section 7.3). These functions take a range of records, and then perform a calculation or lookup to arrive at a single value.

Access includes eight domain functions:

  • DSum calculates the sum of multiple values. You can use it to calculate an order's total price.

  • DAvg calculates the average of multiple values. You can use it to calculate a product's average price.

  • DCount counts the number of matching records. You can use it to count the number of items in an order, or the number of orders a customer made.

  • DMin and DMax find the smallest or largest value in a series. You can use this function to find bargain- basement or top-priced products.

  • DFirst and DLast extract the first or last value in a series. If you sort a list of orders by date, then you can get the oldest or most recent order.

  • DLookup finds a value that meets specific criteria. You can use it to hunt through a table and find thr product name for a given product ID.

All the domain functions take the same three parameters. The first parameter's the field (or calculated expression) you want to retrieve or use in your calculation. The second parameter's the table or query you're using. The third parameter contains any filter conditions you want to use to narrow down the number of rows. If you're trying to find the average price of all the beverages sold by Boutique Fudge, then you use the Price field (as the first parameter), Products table (the second parameter), and filter it to include just those products in the Beverages category (the third parameter).

To calculate the total of all the items in an order, you use the DSum function. The information you need's in the OrderDetails table, but you want to select only those records where the OrderID field matches the current order. Finally, you need to add together the cost of each item. And as you know from before, you calculate the cost of a line by multiplying together the Price and Quantity fields.

With this information in mind, you can create the following calculated field:

 =DSum("Price*Quantity","OrderDetails","OrderID=" & [ID]) 

The first argument's the calculated field that you're taking from each record. The second argument's the name of the table you're using. The third argument filters out just those records that match the current order. If the current order has an ID of 455, then the final parameter matches all OrderDetails records where OrderID=455 . Once again, you can wrap the whole thing with the Format function if you want the final number to look like a currency value.

This calculated field does the trick, but you need one more enhancement. Ordinarily, Access computes calculated fields the first time a record's shown. However, you need to make sure that the grand total's recalculated every time you make a change in the list of ordered items. To accomplish this, you need to call the Form. Recalc method when an OrderDetails record's inserted, updated, or deleted. Here's the code that does the trick:

 Private Sub Form_AfterInsert( )     Forms("PlaceOrder").Recalc End Sub Private Sub Form_AfterUpdate( )     Forms("PlaceOrder").Recalc End Sub Private Sub Form_AfterDelConfirm(Status As Integer)     Forms("PlaceOrder").Recalc End Sub 

Now you can create and fill out an order, without wondering how much it's going to cost.

17.4.2. Getting Price Information

As you learned in Chapter 5, sometimes a table needs to store point-in-time datainformation that's copied from one table to another because it might change over time. A good example is product prices, which evolve over time. (" Evolve " is a polite way to say, "increase relentlessly.") So a product's current isn't necessarily the price at which you ordered it last week. In order to keep track of how much you owe the company, the selling price of a product needs to be stored in the OrderDetails table.

However, this system creates a headache when you fill out an order. Choosing an order item's easy enoughyou just need to select the product from a lookup list. However, the lookup list sets the ProductID field only for the OrderDetails record. It's up to you to figure out the correct price, and then copy it from the Products table to your new record.

Fortunately, you can make this much easier. You can react to the On Change event in the ProductID list, which is triggered every time a product's selected. Then, you can use the DLookup domain function to find the corresponding price, and insert it in the Price field automatically. Here's the code that does it:

 Private Sub ProductID_Change( ) Price = DLookup("Price", "Products", "ID=" & ProductID) Quantity = 1 End Sub 

This code also sets the Quantity field to 1, which is a reasonable starting point. If necessary, you can edit the Price and Quantity fields after you pick your product. Or, to create a more stringent form, you can set the Locked property of the Price control to Yes, so that no price changes are allowed (as in the Boutique Fudge database). This way, when you create an order, you're forced to use the price that's currently in affect, with no discounting allowed.

Note: You can use the same technique to fill in other point-in-time data. You can grab the address information for the current customer, and use that as a starting point for the shipping address. And you can even use the DLookup function to create more sophisticated validation routines. You could use this technique with the Cacophon Music School database, to look up prerequisites and maximum class sizes before letting a student enroll in a class.

17.4.3. Adding a New Product During an Order

Boutique Fudge is a customer-driven company. If someone wants an innovative product that's not yet in the product catalog (like fudge-dunked potatoes), the company's willing to create it on demand.

Ordinarily, the ProductID lookup list doesn't allow this sort of on-the-fly product creation. If you try to type in a product that doesn't exist, then you get a stern reprimand from Access. However, adding new list items on the go is a common Access programming technique, and dedicated event's designed to help you out: the On Not In List event.

If you type in a product that doesn't exist and you're using the On Not In List event, then Access starts by running your event handling code. You can create the item if you want, show a different message, or correct the problem before Access complains.

The On Not In List event has two parameters: NewData and Response. NewData is the information that was typed into the list box, which isn't found in the list. Response is a value you supply to tell Access how to deal with the problem.

Here's the basic skeleton of the subroutine that Access creates if you choose to handle the On Not In List event for the field named ProductID:

 Private Sub ProductID_NotInList(NewData As String, Response As Integer) End Sub 

When the On Not In List event occurs, you should first ask the person using the form if they meant to enter a product that doesn't exist. You can take this step using the familiar MsgBox function in a slightly different way. First, you need to add a second parameter that tells Access to create a Message box with Yes and No buttons . Then, you need to get hold of the return value from the MsgBox function to find out which button was clicked:

 Dim ButtonClicked ButtonClicked = MsgBox("Do you want to add a new product?", vbYesNo) 

This code creates a variable named ButtonClicked, and then shows the message. When the person closes the Message box (by clicking Yes or No), Visual Basic puts a number into the ButtonClicked variable that tells you what happened . The number's 6 if Yes was clicked, or 7 if No was clicked. But rather than deal directly with these numbers and risk making a mistake, you can use the helpful constants vbYes (which equals 6) and vbNo (which equals 7).

Here's the partially completed code for the On Not In List event handler. It shows the message asking if a new item should be added (Figure 17-12), and then cancels the edit if the person using the form chooses No:

 Private Sub ProductID_NotInList(NewData As String, Response As Integer)     ' Show a Yes/No message and get the result.     Dim ButtonClicked     ButtonClicked = MsgBox("Do you want to add a new product for " & _       NewData & "?", vbYesNo)     ' Visual Basic gives you hand vbYes and vbNo constants     ' that you can use to find out what button was clicked.     If ButtonClicked = vbNo Then         ' Cancel the edit.         ProductID.Undo         ' Tell Access not to show the error message.         ' You've already dealt with it.         Response = acDataErrContinue     Else        ' (Put some code here to add a new product.)     End If End Sub 

Then you supply the code that adds the new product. In this example, it doesn't make sense for your code to add the product completely on its ownafter all, a product needs other information (like price and category details) before it's considered valid. Instead, you need to show another form for adding products. The DoCmd.OpenForm method's the key:

 ' Tell Access not to worry, because you're adding the missing item. Response = acDataErrAdded ' Open the AddProduct form, with three additional arguments. DoCmd.OpenForm "AddProduct", , , , , acDialog, NewData 

Figure 17-12. Fudge-Dunked Potatoes is not a currently offered product. When you type it in, and then hit Enter, your code asks whether you really intend to add this product.

The two additional arguments you use with the OpenForm method are quite important:

  • acDialog opens the form in dialog mode, which means Access puts the code in the ProductID_NotInList on hold until the AddProduct form's closed. That step's important because once the adding process is finished, you'll need to run more code to update the PlaceOrder form.

  • NewData takes the newly typed-in information, and sets it in the AddProduct. OpenArgs property. That way, the AddProduct form can retrieve this information when it starts up, and then adjust itself accordingly .

Here's the code you need in the AddProduct form to copy the newly entered product name (the value you passed using the NewData variable in the previous code snippet) into the ProductName field when AddProduct first loads:

 Private Sub Form_Open(Cancel As Integer)     ProductName = Form.OpenArgs End Sub 

Figure 17-13 shows what this form looks like.

Once you finish entering all the product information, you can close the Add-Product form. At that point, a little more code runs in the ProductID_NotInList subroutine. This code's placed immediately after the DoCmd.OpenForm statement. Its job is to update the new order item to use the product you've just entered:

Figure 17-13. The AddProduct form lets you supply the rest of the information for the new product you want to create. Notice how the form opens as a pop-up form, and Access automatically assumes you're inserting a new record (not reviewing existing products). Access acts this way because the Pop Up and Data Entry properties of the form are both set to Yes.

 ' Cancel the edit. That's because you need to refresh the list ' before you can select the new product. ProductID.Undo ' Refresh the list. ProductID.Requery ' Now find the ProductID for the newly added item using DLookup. ProductID = DLookup("ID", "Products", "ProductName='" & NewData & "'") 

Note: This code works even if you cancel the new product by hitting the AddProduct form's Esc key. In this case, the DLookup function can't find anything, so it returns a null (empty value) to the ProductID field. As a result, you get the familiar Access warning message telling you the product you picked isn't in the list.

There's one more detail. By the time the On Not In List event occurs, the On Change event's already taken place. So you just missed your chance to run the code you used earlier to insert the corresponding price into the Price field in the list of order items.

Fortunately, you can solve this problem quite easily. You just need to add one more line of code that tells Access to go ahead and run the event handler (the ProductID_Change subroutine) again:


To see the complete code for this example in one place, refer to the sample Boutique Fudge database for this chapter.

17.4.4. Managing Order Fulfillment

Now that you've perfected the ordering process, you can turn your attention to what happens next .

In the Boutique Fudge database, every record in the Orders table has an Order-Status field that keeps track of the, well, status. Newly created order records have a New status. In the stock room, the warehouse workers look for orders with the New status, and pick one to start working on. At that point, they change the status of this order to In Progress, so nobody else tries to ship it at the same time. Finally, when the order's complete, they change it to Shipped, and then record the exact time in the ShipDate field.

Logically, this model makes sense. However, it's a bit tricky using ordinary tables and forms. In order to follow this workflow, the warehouse staff needs to modify the status of an order record several times, remember to record the ship date, and avoid changing other details. If they miss a stepsay they never put the order into In Progress statusit's possible that more than one employee could try to complete the same order.

The solution's to create a ShipOrders form that guides the warehouse workers through the right steps. Initially, this form shows a list of orders with minimal information (Figure 17-14).

Figure 17-14. The list of orders is sorted so that the oldest orders (which should be processed first) appear at the top of the list. Each field's Locked property's set to Yes, so nobody can edit any data. However, next to each order is a Process button that starts the order fulfillment process. (You could also add filtering to this form, so that you see only orders with certain statuses.)

When someone clicks the Process button, several steps need to take place. Here's a step-by-step walk-through of the code, one chunk at a time.

First, your code needs to refresh the record. That step catches whether someone else has started processing the order on another computer:

 Private Sub ProcessOrder_Click()     Form.Refresh 

Next, your code needs to check the record's status. If it's anything other than New, that order isn't available for processing:

 ' The StatusID for New is 2. If StatusID <> 2 Then     MsgBox "This order is not available." 

Otherwise, you need to switch the status to In Progress and save the record right away, to make sure no else tries to get it:

 Else     ' The StatusID for In Progress is 3.     StatusID = 3     ' Save the change.     DoCmd.RunCommand acCmdSaveRecord 

Note: It's extremely important to save the record (using the DoCmd.RunCommand method, as shown here) in this sort of situation. Otherwise, the order record remains in edit mode, and the new status isn't saved in the database. Other people might start processing it, because they have no way of knowing that you've changed the status.

Now it's time to launch the ReviewOrderDetails form, which displays a read-only view of all the items in the order (Figure 17-15). The form's opened in dialog mode, which locks up the ShipOrders form until the order fulfillment process is complete:

 DoCmd.OpenForm "ReviewOrderDetails", , , _         "OrderID =" & ID, , acDialog     End If End Function 

The ReviewOrderDetails form gives the warehouse staff two choices. If they click Ship, then Access changes the order status to Shipped, and the process is complete:

 Private Sub Ship_Click( )     ' Close this form.     DoCmd.Close     ' Switch back to the ShipOrders form.     DoCmd.OpenForm "ShipOrders"     ' Update the order.     ' The StatusID for Shipped is 4.     Forms("ShipOrders").StatusID = 4     DoCmd.RunCommand acCmdSaveRecord End Sub 

Figure 17-15. You don't need to include pricing details in the ReviewOrderDetails form. It's simply designed to give the warehouse people the information they need as efficiently as possible. The ReviewOrderDetails form also uses a query join to get some related data, like the PartNumber, from the Products table.

In the ReviewOrderDetails form, the properties Control Box and Close Button are both set to No. That way, the window doesn't give the warehouse staff any way to close it except to click the Ship or Cancel buttons. (If you don't use this approach, then you need to write extra code that resets the order's status if someone clicks the X icon in the top-right corner to close the ReviewOrderDetails form.)

Tip: This spot's also a good place to use DoCmd.OpenReport to print out a report that creates a shipping insert with a list of all the products in the order.

But if they click Cancel (perhaps because they've discovered they don't have the right items in stock), similar code's used to return the order to New status:

 Private Sub Cancel_Click( )     ' Close this form.     DoCmd.Close     ' Switch back to the ShipOrders form.     DoCmd.OpenForm "ShipOrders"     ' Update the order.     Forms("ShipOrders").StatusID = 2     DoCmd.RunCommand acCmdSaveRecord End Sub 

This part completes the code you need to coordinate order processing. Like the forms you learned about in Part Four of this book, the forms in this example draw all their information from your database's tables. But unlike those Part Four examples, these use code to perform some of the work automatically. This difference changes your forms from mere data-entry tools into supercharged workflow tools.

Tip: You could also create a special status value to denote orders that have been attempted but couldn't be completed (like On Hold or Waiting For Stock). That way, the warehouse employees would know not to keep trying the same orders. If you take this step, then make sure you modify the code in the ProcessOrder_Click subroutine, so people can process orders with this status.

17.4.5. Updating Stock Numbers

Thanks to the ever-so-smart ShipOrders form you saw in the previous section, business is running smoothly at Boutique Fudge. However, one day the warehouse employees come to senior management with a complaint. Although orders are sailing through without a hiccup, the product inventory information isn't keeping up. No one remembers to adjust the UnitsInStock information, so it's becoming increasingly useless.

A truly automated solution would automatically update the UnitsInStock information whenever an order ships. And after all, isn't that what Access is designed to do best?

This challenge is entirely unlike the other problems you've solved so far, because it forces you to make a change in a completely different set of recordsrecords that aren't being displayed in any form.

You already know that you can use the domain functions (Section 17.4.1) to retrieve information from other tables. But unfortunately Access doesn't have a similar set of functions that lets you make changes. Instead, you need to turn to a completely new set of objects, called the data access objects (or just DAO for short).

DAO lets you perform any data task you want, independent of your forms. However, DAO is a bit complex:

  • You need to use DAO in a very specific way . If you use the methods in the wrong order or leave out a step, then you run into an error. Often, it's easiest to start with an example that works (like the sample code included with this chapter), copy it, and then alter it as needed.

  • DAO doesn't use query objects . Instead, it forces you to write SQL statements. You learned how to write a basic SQL command in Chapter 6 (Section 6.2.3).

DAO involves two essential techniques. First, there's the CurrentDb.Excecute method, which lets you run a direct SQL command by supplying it in a string:

 CurrentDb.Execute MyUpdateCommand 

This method's a quick and dirty way to make database changes, like sweeping update, delete, or insert operations.

The second essential technique's to retrieve records using a specialized object called the Recordset . To use a Recordset, you must begin by using the CurrentDb. OpenRecordset method, and supplying a string with an SQL select command:

 Dim Recordset Set Recordset = CurrentDb.OpenRecordset(MySelectCommand) 

The Recordset represents a group of records, but it lets you access only one at a time. To move from one record to the next, you use the Recordset.MoveNext method. To check if you've reached the end, you examine the Recordset.EOF property, which stands for end-of-file. When this property's True, you've passed the last record.

You most often use a Recordset in a loop. You can use Recordset.EOF as the loop condition, so that the loop ends as soon as Access reaches the end of the Record-set. Inside the loop, you can retrieve field values for the current record. At the end of each pass, you must call MoveNext to move on:

 Do While Recordset.EOF = False     ' Display the value of the ProductName field.     MsgBox Recordset("ProductName")     ' Move to the next record.     Recordset.MoveNext Loop 

With these bare essentials in mind, you can make your way through the following code, which adjust the product stock values based on a recently shipped order. (A line-by-line analysis follows the code.)

 1 Sub UpdateStock( )       ' If an error occurs, jump down to the DataAccessError section. 2     On Error GoTo DataAccessError       ' Create a SELECT command. 3     Dim Query 4 Query = "SELECT ProductID, Quantity FROM OrderDetails WHERE OrderID=" & ID       ' Get a recordset using this command. 5     Dim Recordset 6     Set Recordset = CurrentDb.OpenRecordset(Query)       ' Move through the recordset, looking at each record.       ' Each record is a separate item in the order. 7     Do Until Recordset.EOF           ' For each item, get the product ID and quantity details. 8         Dim ProductID, Quantity 9         ProductID = Recordset("ProductID") 10        Quantity = Recordset("Quantity")           ' Using this information, create an UPDATE command that           ' changes the stock levels. 11        Dim UpdateCommand 12     UpdateCommand = "UPDATE Products SET UnitsInStock = UnitsInStock-" & _ 13          Quantity & " WHERE ID=" & ProductID           ' Run the command. 14        CurrentDb.Execute UpdateCommand           ' Move to the next order item (if there is one). 15        Recordset.MoveNext 16     Loop        ' Time to clean up. 17     Recordset.Close 18     CurrentDb.Close 19     Exit Sub 20 DataAccessError:        ' You only get here if an error occured.        ' Show the error. 21     MsgBox Err.Description 22 End Sub 

Here's what takes place:

  • Line 1 declares a new subroutine. Because this code's fairly complex, it makes sense to put it in a separate subroutine, which you can call when the Ship button's clicked and the order's shipped.

  • Line 2 tells Access to head down to the end of the subroutine if an error takes place. Errors are always possible with data access code, so it's good to be on guard.

  • Lines 34 create the SQL command you need to select the OrderDetails records for the current order. (See Section for more about SQL SELECT commands.)

  • Lines 56 execute that command, and get all the matching records in a Recordset.

  • Line 7 begins a loop that moves through the entire Recordset.

  • Lines 810 get the ProductID and Quantity fields for the current OrderDetails record (the first one in the Recordset).

  • Lines 1113 use this information to build a SQL UPDATE command. The command subtracts the number of items ordered from the total number in stock. A sample completed command looks like this: UPDATE Products SET UnitsInStock = UnitsInStock-4 WHERE ID=14 . This subtracts 4 units from product 14.

  • Line 14 performs the update.

  • Lines 1516 move to the next record and repeat the update process (until no more order items remain in the Recordset).

  • Lines 1718 perform cleanup.

  • Line 19 exits the procedure. If you made if here, congratulationseverything worked without a hitch!

  • Lines 2022 are only processed if an error occurs somewhere. In this case, the error description's shown in a Message box.

This code's a fair bit more ambitious than anything you've seen so far. However, it builds on all the skills you've honed over the last three chapters. Once again, the best way to get comfortable with this code is to download the sample database, see it in action, and try changing it. Happy experimenting!


In an effort to make life more confusing, Microsoft introduced a second data access technology named ADO many moons ago. Both DAO and ADO let you perform data tasks using handy objects. The key difference between them is that Microsoft designed ADO to be an all-purpose data access technology that works with other database products, like SQL Server, while DAO's a strictly Access-only affair.

Some Access programmers think (incorrectly) ADO's the successor to DAO, and a better choice when writing code for an Access database. (In fact, Microsoft may even have said that at one point, but they've destroyed all the evidence.) Today, the official story's that DAO's the better way to go, because it's fine- tuned for Access. That means DAO's easier to use, and offers better performance in most cases. The only people who use ADO are seasoned VB programmers who already know how it works, and don't want to bother learning DAO, or people who need an exotic feature that ADO provides but DAO doesn't.

Access 2007[c] The Missing Manual
Access 2007[c] The Missing Manual
ISBN: 596527608
Year: 2007
Pages: 153 © 2008-2017.
If you may any questions please contact us: