VBA problem in query

[geniass]

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("Cheese and Tomato Sandwich") AS Price, Table2.ID, Table2.Ingredient, Table2.Price
FROM Table1, Table2;

Any help would be appreciated.

[RuralGuy]

This link may help: http://allenbrowne.com/ser-16.html

[geniass]

I was trying to work out why the function works fine in in the debugging area, but as soon as i use it in a query is doesn't return anything at all, not even 0.

[RuralGuy]

Is it in a Standard Module rather than in a form module?

[RuralGuy]

Does the immediate window still show the Debug.Print's?

[ajetrumpet]

Here is the SQL query:

SELECT Table1.ID, Table1.[Item Name], LoopIngredients("Cheese and Tomato Sandwich") AS Price, Table2.ID, Table2.Ingredient, Table2.Price
FROM Table1, Table2;

Any help would be appreciated.

Does it have anything to do with pulling values from 2 dif tables that are not joined? in the actual function your sql string only include table 2. It's been a while since I've messed with this idea of pulling from two dif unrelated tables, but could it be that issue I wonder?

Or maybe also, something that has to do with opening table2 in memory once it is already opened by the query?

[geniass]

Here's a screenshot of the VBA editor:

I think it is a regular module.
I really have no idea what is going on because it debugs fine.

[RuralGuy]

OK, your code is in a Standard Module and Public since that is the default. What happens when you run the query? Have you looked at the Immediate window *after* running the query to see if the Debug.Print statements executed? Have you set a break point to see what is being returned?

[ajetrumpet]

gen,

Ruralguy is probably the one to ask about all this, but I just wonder what your tables are like. Relationships between 1 and 2? On ID perhaps? what do you want out of this situation? Example - if you want the total price of each product listed in table A and you've got 1 and 2 related one to many on the ID field, the DSUM() function is the solution (no function required).

[geniass]

Ok, basically, I have 2 tables.
The first table has the fields: ID, Item Name and Cost Price.
The second table has the following fields:ID, Ingredient and Cost Price.
Now basically what this query must do is, for each item in table 1, check if this item contains each ingredient in table 2. If it does contain this ingredient, add its cost price to the items total price. Eg. Cheese and tomato sandwich, Does it contain cheese? Yes, add 1.50. Does it contain lettuce? No. Does it contain Tomato? Yes Add 2.00.

Also what exactly does joining tables do?
Thanks

[RuralGuy]

Did you try anything I suggested in Post #8?

[geniass]

How do I check the immediate window while running a query?
Thanks

[RuralGuy]

*After* you run the query use ^G to open the Immediate window and see what is there.

[ajetrumpet]

Now basically what this query must do is, for each item in table 1, check if this item contains each ingredient in table 2.

That's great, but HOW do you check? does the ID in table 2 match one of those in table1? These are the types of questions you need to ask. For example, if IDs in table2 are arbitrary and mean nothing except to identify the record numbers in that table, there is no way to know which items have what ingredients because there's no connection between the two tables' data at all. Does that make sense?

Also what exactly does joining tables do?
Thanks

joining tables happen automatically when queries are built through the interface and not code. One to many relationships between tables are the most common situations that result in joins. To know more, type in JOIN and read the help articles on the different types.


This might not be best, but the way I see your tables set up is like this:

table1
1 - ID (auto)
2 - item name
3 - price (if this is different than all included ingredient prices combined to equal this price)

table2
1 - ID (matches IDs in 1, meant to relate ingredients to what item they make up
2 - ingredient
3 - price

If you had this setup, the query would be as simple as:

SELECT ID, [ITEM NAME],

DSUM("price", "table2", "[ID] = " & [ID]) AS TotalPrice

or ... if the price you want equals the item price + all the ingredient prices related to that item, it's a little different:

SELECT ID, [ITEM NAME], 
([PRICE] + DSUM("price", "table2", "[ID] = " & [ID])) AS TotalPrice

Remember, I'm not this great expert but please also remember to give as much (relevant) information as you can when you post. If you know what is relevant that is (some people don't)

[geniass]

You mean Ctrl G right? I pressed that and it just beeps.