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