CODEWITHSUNDEEP
sqlvbafunctionms-access
Emil Olsen
Emil Olsen

Reputation: 352

Passing VBA variable to Access Query Criteria

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?

Upvotes: 1

Views: 3319

Answers (2)

Emil Olsen
Emil Olsen

Reputation: 352

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.

Upvotes: 3

KevenDenen
KevenDenen

Reputation: 1726

Declaring a variable as public in a form seems to not actually make it globally public. This has annoyed me many times in Access. Move your declaration of counter to your Module code and you should see a change in operation.

There is a good discussion of this behavior in the following SO post (Public variables are not REALLY public in VBA in Forms).

Upvotes: 2

Related Questions