Create A Menu Planner

It’s turkey time! Design a menu planner that lets you practice user input and data access.

By Chris Barlow

Reprinted with permission from Visual Basic Programmer's Journal, November 1999, Volume 9, Issue 11, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange.

Learning a new programming language can be difficult, and it can be even tougher to think of good apps to write for practice. I always keep my eyes open for tasks in my job or at home I could try to do with an application in the language I’m trying to learn.

With the Thanksgiving holiday coming, I watched my wife sit down to plan the menu and shopping list for our traditional Thanksgiving dinner. She was asking herself, “What should I serve to accompany the turkey? How many people will I serve? What ingredients do I need to buy?”

Now, there’s no doubt that my wife can do this easily without a computer application—she’s probably faster doing it by hand than I am entering the information into the computer! Still, I thought a menu planner would be a cool application to write. I could access a database of recipes, do some calculations on the ingredients, and prepare a shopping list based on the number of people being served. It would be a great app to practice user input and database access. Plus, this process is similar to what an Enterprise Resource Planning (ERP) system does in a large company, so it’s a way to gain further understanding of a key business application.

The first step in any project is to sketch out the basic logic of your application. You need a database with a two tables: One for recipes, including fields for cooking instructions and the number of people the recipe serves, and another containing each recipe’s ingredients. You need Visual Basic 5 or 6 to create the application and Active Data Objects (ADO) to access the database, and Access to create the database with its tables and fields. Access contains a sample Recipe database organized in a similar manner. The database includes some sample data and provides a good starting point for the application.

Start Access and use the sample Database Wizard to create the RECIPE.MDB database (see Figure 1). Or you can download the database with its sample data and this Recipe application from the VBPJ Web site (see the Go Online box at the end of this column for details).

Perform Magic with the Wizard

Once you have a sample database, Visual Basic’s Application Wizard makes it simple to get started on your VB application. The Application Wizard helps you create a complete application with a main form and several ancillary forms. The main form includes a toolbar, status bar, and a complete application menu. The wizard even writes some of the code to activate menu and toolbar events. You can point to a database and the wizard will create data-access forms using a data control or, with VB6, it can use ADO code to access the database (see Figure 2).

The wizard also makes a great learning device. Let it build several different kinds of applications, then single step through the code of each one. Notice how the menus are organized, how the toolbar button calls the menu procedures, and how the data-access forms operate. Next, change some of the code, then add some menu items and forms. One of the best ways I know to learn Visual Basic is to explore the wizard’s many features. If you’re new to ADO, it can be helpful to see how the wizard writes the ADO code to access a parent table and a child table.

You need to choose the user interface when you start the Application Wizard. You can choose from three different types: Single Document Interface (SDI), Multiple Document Interface (MDI), or Explore Style Interface (ESI). SDI is the best choice for a menu planner application such as this, because it displays a single set of user data in a single form. It creates an application with a single main form containing menu items, a toolbar, and a status bar.

Next, use the Application Wizard to choose the menus you want on the form. The wizard gives you 30 menu items to choose from. For example, the View menu contains menu items to control the display of the toolbar and status bar. The associated object is displayed when you select these menu items. The wizard even adds code to these menu item click events to toggle the menu item’s Checked property and the object’s Visible property. All these menu items use a standard naming nomenclature and standard shortcut keys. You should get into the habit of using such standard nomenclature on all your applications—it simplifies future modifications you or another programmer must make to the code.

Next, the wizard creates data access forms from the tables and queries in a database. These forms can use the Data control, inline ADO code, or even a data-access class. It includes code to add, update, and delete records in the database, although you’ll probably want to add your own data-validation routines.

Finally, name your project Recipe, sit back, and watch the wizard build all the forms and generate standard code for your application. You can review a summary of the tasks the wizard performed and start on the To Dos the wizard embeds in the code.

Using ADO to access the database works well from an application like this; you’ll find it’s not too different than the DAO you might have used in the past. ADO is part of Microsoft’s Universal Data Access strategy to provide high-performance access to any data source, including relational and nonrelational databases, e-mail and file systems, text and graphics, and custom business objects. The hierarchy of objects found in DAO is built as a layer on top of Microsoft’s OLE DB and is de-emphasized in the ADO model. With DAO you generally open a database at one point in your program and leave it open while other procedures open and close recordsets. In ADO you don’t need to create a Workspace and then a Database object in order to open a recordset. ADO can operate in a stateless manner, where the database and recordset open and close simultaneously when accessed. ADO is ideal for data access from a Web site where a browser retrieves one page of information, then disconnects until the user needs another page of information. ADO even lets you to create temporary, unattached recordsets. If you don’t have ADO installed on your computer, you can download it from Microsoft’s Web site at www.microsoft.com/data/ado.

Create the Menu Planner

Now, you’re ready to add your own forms and begin building the Recipe application after the Wizard creates your app’s shell (see Listing 1). I like to start with a rough layout of the form and get the application loading some information from the database. I go back and clean up the form after I get the basic logic working.

Open your Recipe project and add a new form. Name it Menu, and drag and drop three ListBox controls onto the form. One control displays all the menu items from the database, the second displays the menu items you’ve selected for this meal’s menu, and the last one shows your shopping list (see Figure 3).

Add the following code in the Form_Load event to open the database as an ADO recordset:

Private Sub Form_Load()
Set db = New Connection
db.Open _
	"Provider=Microsoft.Jet. _
	OLEDB.3.51;Data Source=Recipes.mdb;"
Set adoPrimaryRS = New Recordset

Create a SQL statement to select the RecipeName, RecipeID, and NumberofServings fields from the Recipes table and sort by the RecipeName field:

adoPrimaryRS.Open "select RecipeName, _
	RecipeID, NumberofServings from _
	Recipes Order by RecipeName ", db, _
	adOpenStatic, adLockOptimistic

Add the following code to populate the first listbox from the ADO recordset:

Do Until adoPrimaryRS.EOF
	List1.AddItem adoPrimaryRS(0) & " _
		for " & adoPrimaryRS(3)
	List1.ItemData( _
		List1.ListCount - 1) = _
		adoPrimaryRS(1)
	adoPrimaryRS.MoveNext
Loop
adoPrimaryRS.Close
End Sub

You’ll need to step through the recordset until the EOF property is True, and use the ListBox control’s AddItem method to display the recipe’s name and number of servings. Note that it is important to save the RecipeID so you can look up the ingredients easily by this key field. The ListBox control provides an ItemData array, one long field for each item in the list as a convenient place to store such key information. It is invisible to the user, but always available to the programmer to look up a database record. Don’t forget to close the recordset when you are finished.

Now, even though you haven’t completed the form’s functionality, run your project to watch that first listbox load. Do you see the recipes from the database? Excellent! If you don’t see the recipes, single step through the code to debug this procedure. You should get in the habit of this kind of code-run-fix-code process—it is this kind of incremental development that makes VB such a powerful development language.

The next step is to let the user select the recipes they want in their menu by double clicking on a recipe to move it from the first ListBox control to the second. This code is simple—just remember to also copy the RecipeID key from the ItemData array:

Private Sub List1_DblClick()
	List2.AddItem List1
	List2.ItemData(List2. _
		ListCount - 1) = _
		List1.ItemData(List1.ListIndex)
End Sub

You’ll probably want to enhance this code to allow the user to change his mind and remove an item from the menu.

Shop Until You Drop

Once the user has built his menu, it’s time to prepare the shopping list. Again, think through the logic before you begin writing the code. You want to provide a textbox where the user can enter the number of people he wants to serve with this menu. You need to calculate a scale factor to adjust the ingredients to create the proper size recipe because each recipe serves a different number of people.

The more complicated calculation adds up the requirements for ingredients across all menu items. If your corn bread recipe needs two eggs and your sweet potato casserole needs two eggs, then you need to buy four eggs. An easy way to do this calculation, and one commonly used in ERP systems, is to create a temporary database table to store the menu information, then write a SQL statement to join this Menu table to the Ingredient table and add up the common components. Let’s see how this might work.

Back in Access, add a simple Menu table to the Recipe database with just three fields: RecipeID, RecipeName, and ScaleFactor. Then, when the user clicks the Create Shopping List button use the ADO Connection object’s Execute method to execute a SQL statement deleting all records from the Menu table that might be left over from a prior shopping list:

Private Sub butShopList_Click()
Dim item As Long
Dim ScaleFactor As Single
'first clear the Menu table
db.Execute "delete * from Menu"

Then create the menu table from the items in the second listbox. Note how to calculate the ScaleFactor based on the number of people the user wants to serve:

'then build it from the List2
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select * from _
	Menu", db, adOpenStatic, _
	adLockOptimistic
For item = 0 To List2.ListCount - 1
	adoPrimaryRS.AddNew
	adoPrimaryRS(0) = _
		List2.ItemData(item)
	adoPrimaryRS(1) = List2.List(item)
	'calc scalefactor as _
	'txtServe/number served
	ScaleFactor = txtServe / _
		(Mid(List2.List(item), InStr( _
		List2.List(item), " for ") + 5))
	adoPrimaryRS(2) = ScaleFactor
	adoPrimaryRS.Update
Next
adoPrimaryRS.Close

You can construct the SQL statement to calculate the shopping list now that the menu table is built. If you’re not a SQL expert, you can use Access to build the query graphically, then look at the SQL statement created by Access. In this case I use SQL to Sum the scale factor times the ingredient quantity using a join between the Menu table and the Ingredient table and grouping by ingredient:

'then create query for ingredients
adoPrimaryRS.Open "SELECT Ingredient, _
	Sum([scalefactor]*[Quantity]), UOM _
	FROM Menu INNER JOIN ingred ON _
	Menu.RecipeID = ingred.RecipeID _
	GROUP BY ingred.Ingredient, _
	ingred.UOM", db

Finally, step through the resulting recordset and add the fields to the third listbox to complete the shopping list:

'then build list3
List3.Clear
Do Until adoPrimaryRS.EOF
	List3.AddItem adoPrimaryRS(1) & _
		" " & adoPrimaryRS(2) & " " & _
		adoPrimaryRS(0)
	adoPrimaryRS.MoveNext
Loop
adoPrimaryRS.Close
End Sub

Now the fun part—fire up your application and create your favorite Thanksgiving dinner menu. Decide how many people you want to invite and create the shopping list. I’ll be waiting for my invitation!

About the author: Chris Barlow, a recognized expert in the Internet, Web, Messaging, and applications development, is a frequent speaker at VBITS, Tech·Ed, and DevDays. Chris holds degrees from Harvard Business School and Dartmouth College and is a Senior Engagement Manager with McKinsey & Company’s Business Technology Office in Palo Alto, Calif. Reach him at Chris@VBExpert.com or on the Web at www.VBExpert.com.