Passing VBA variable to Access Query Criteria

Question

I have a simple task giving me a lot of trouble.

What I need is to simply move a variable from my VBA macro to a function, to be able to use it in an Access Query.

ADDED: I have created a small and simple access document with a simple VBA. Download it at: http://sharesend.com/olpy1o7d

If someone could be kind to download and see what I am doing wrong, I would really prefer it. In the form f_main I need to be able to change counter to whatever number and hence get an updated query when the button is pressed in the main form.

It is described below, but downloading it may explain my situation easier.

EDITED:

Public counter as Integer

Private Sub Command_Click()

counter = 1

End Sub

And in a module:

Public Function n()
n = counter
End Function

In access, I have a SQL Query as:

SELECT t_Data.ID, n() AS Expr1 FROM t_Data;

Access says it can not find the function n()

If i use the function builder, i can see n(), so i guess it is because the function n() is empty - It can not find "counter" declared in my form.

What can i do?

I've deleted my answer since the question has changed quite a bit, I would add tags for Access and VBA, and you may get more help, but I've run out of time on this end. — Lunster
Your question doesn't make much sense in its current form. Where does binsize come into play here? Or the kW? How is the SELECT statement called? And why does it need a column with the value of the counter? How are the queries q_PowerBinned_0x defined? — Ansgar Wiechers
The binsize and kW was only to explain the issue to Ian, as he asked for the application purpose. It does not matter. I just need VBA to pass variables to the Query criteria. Thats it. It is a quite large program and it would take a really long time to walk through it. So the binsize and kW are just used as examples. — Emil Olsen
Make sure that your function is declared in a regular Module, not a Class Module for a Form or Report. — Gord Thompson

Emil Olsen Emil Olsen · Accepted Answer · 2015-01-05T07:51:39

To help future bummers like mine, I found the error.

I named the module the same as the function, which caused the problem.

By changing the name of the module from n to m_n, and keeping the function name n() it worked perfectly fine.

Stupid mistake is stupid. But thank you guys who looked into my issue.

Passing VBA variable to Access Query Criteria

2 Answers