Pass column contents to module as variable?

[exo]

I have a simple function that expects a string variable to be passed along to it.

My intent is to send the contents of a column from a table as this string, so basically for every row in my table I want to pass the contents over to my code and have it run.

So lets say I only had 3 rows in a table named 'Food' in a field named 'Fruit' that are:
apple
orange
banana

I want to call my function (lets pretend it is called Basket) for each of these.

I tried going to macros and making one using the run code command that said:
Basket([Food]![Fruit])

This didn't work though. Can anyone tell me what I'm doing wrong? Thank you much.

 

[jdraw]

You could open a recordset based on a query of your field "fruit" in your table.
Then in a loop
call your function with the current value of "fruit"
move to next record.

General format of vba approach:

Dim tmp as string
Dim db as dao.database
Dim rst as dao.recordset
set db = currentdb
set rst = db.openrecordset("Select fruit from MyTable")

Do while NOT rst.eof
tmp = rs!Fruit
debug.print myFunction(tmp)
' do whatever you want with your function here
rst.movenext
Loop

 

[exo]

I know how to make a query using SQL or through the design wizard... how do i make a query using VBA? I have only used VBA in the module :/

edit: ah wait, i read it wrong. This is a function that calls a query.... I get that now. But I'm not sure how to modify this to work for me.... have to stare at it a bit longer.

 

[exo]

it is telling me it can't find the input table.

and the debug takes me to this line:
Set rst = db.OpenRecordset(Food)

If food is the name of my table, this should work, right?

edit1;
i changed it to:
Set rst = db.OpenRecordset("SELECT * FROM Food")
and that worked great for me.