VB6 The Menu Planner Application

Listing 1 This form contains all the code for the application. Notice that the database connection and the recordset are declared at the form level so they persist through all procedures.

Option Explicit

Dim adoPrimaryRS As Recordset
Dim db As Connection

Private Sub butShopList_Click()
Dim item As Long
Dim ScaleFactor As Single
'first clear the Menu table
db.Execute "delete * from Menu"
'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
'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
'then build list3
List3.Clear
Do Until adoPrimaryRS.EOF
	List3.AddItem adoPrimaryRS(1) & " " & _
		adoPrimaryRS(2) & " " & adoPrimaryRS(0)
	adoPrimaryRS.MoveNext
Loop
adoPrimaryRS.Close
End Sub

Private Sub Form_Load()
Set db = New Connection
db.Open "Provider=Microsoft.Jet.OLEDB.3.51; _
	Data Source=Recipes.mdb;"
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select RecipeName, RecipeID, _
	RecipeDescription, NumberofServings from _
	Recipes Order by RecipeName ", db, _
	adOpenStatic, adLockOptimistic
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

Private Sub cmdClose_Click()
	Unload Me
End Sub

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