Query Parameter Form - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query Parameter Form

Jan Holm

6 years ago

Hi,
When runing a query sometimes the content in the result is controlled by a parameter on a Form. Then you have as a Criteri e.g. Forms!MyForm!MyParameter.
However, if many forms wants to use the same Query a more global parameter (than a Form) is needed. Is there a way that the criteria in the Query could be changed from VB.
(I know I could write the SQL statement in VB, I do that a lot, but I have a case where I want to avoid that)

Regards Jan Holm

Richard Rost @Reply

6 years ago

Not really. You could open a HIDDEN form who's sole purpose is to provide parameters to your query. For example, your CustomerSearchF could say:

    DoCmd.OpenForm "QueryParameterF", acNormal, , , , acHidden

    Forms!QueryParameterF!State = "NY"

    DoCmd.OpenForm "WhateverOtherF"

    DoCmd.Close acForm, "QueryParameterF", acSaveYes

This would open your parameter form, set whatever criteria you need, then open the NEXT form (whatever it happens to be that needs that query criteria) and then close the criteria form. The user would never see anything, and you could use the same query for multiple forms.

	Richard Rost @Reply 6 years ago
However, writing the SQL in VBA for each form would be better. That ^ is kind of a cheesy way around it.

	Richard Rost @Reply 6 years ago
You COULD use a global module with some functions, but again that's a crazy workaround. You could define a global variable in a module, use a function to set it's value, and then in your query read that value using a function. There are a lot of options to get around what you SHOULD do. LOL

	Richard Rost @Reply 6 years ago
This might make an interesting TechHelp video. I think I'll add it to the list.

	Richard Rost @Reply 6 years ago
With the functions, you could have them prompt to enter a value if one doesn't exist, OR get it from an open form, OR provide a default value. I see some potential here. BUT AGAIN, writing the SQL in VBA is the preferred method.

	Jan Holm OP @Reply 6 years ago
Ok, thanks for the advice. I will do the SQL In VBA solution. I was hoping for a another solution. Jan Holm

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View