Strange Problem With VBA in Access SQL

Question

Hi, I have written this VBA module for a school project in Access 2007, and it works fine in the Immediate Window in the Visual Basic Editor. However when I use it in a query (SQL) the value just doesn't show up. I have no idea why. Here is the module code:

Option Compare Database

Function LoopIngredients(itemName As String) As Long
Dim strSQL As String
Dim rst As Recordset
Dim field As field
Dim temp As Boolean
strSQL = "Select Table2.[Ingredient], Table2.[Price] From Table2"

'open the results read-only
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
If rst.RecordCount > 0 Then
'rst.MoveFirst
Do While Not rst.EOF
For Each field In rst.Fields
    If field.Name = "Ingredient" Then
        temp = False
        If InStr(itemName, field.Value) Then
            temp = True
            Debug.Print ("Name " & field.Value)
        End If
    End If
    If field.Name = "Price" Then
        If temp Then
            LoopIngredients = LoopIngredients + field.Value
            Debug.Print ("Price " & LoopIngredients)
        End If
    End If
Next field
rst.MoveNext
Loop
End If
End Function

And here is the output in the immediate window:

?LoopIngredients("Cheese and Tomato Sandwich")
Name Cheese
Price 1
Name Tomato
Price 3
3

Here is the SQL query:

SELECT Table1.ID, Table1.[Item Name], LoopIngredients([Item Name]) AS Price, Table2.ID, Table2.Ingredient, Table2.Price
FROM   Table1, Table2;

Any help would be appreciated.

Answer 1

The things are actualy working, but you are using this in you query...

LoopIngredients([Item Name]) AS Price

But you only get the following fields in the SQL statement...

strSQL = "Select Table2.[Ingredient], Table2.[Price] From Table2" 

and then the field is compared to Ingredient...

If field.Name = "Ingredient" Then 
    temp = False 
    If InStr(itemName, field.Value) Then 
        temp = True 

Here the field.Value -> Table2.Ingredient is compared to the content of the Item Name. So the return value of the function is never incremented with the price. In you immediate window test you added the incredients as the input parameter.

Answer 2

Sorry, I am not quite sure what you mean. Even if I call the function like this LoopIngredients("Cheese Sandwich") AS Price it still doesn't work. As for the InStr method, i am trying to see whether the current ingredient in the loop, (separate table) is contained in the items name. Eg. "Cheese and tomato sandwich" consists of cheese and tomato. Thanks for your reply.

Answer 3

Any solution? i will need an answer pretty soon.

Answer 4

Everything seems to work okay if the SQL query is changed to: SELECT Table1.[Item Name], LoopIngredients([Item Name]) AS [Price] FROM Table1;