Passing variable to query

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Dear all,

I have create access query named "ABC". And then I created a form and define
a variable named "work_field" and want to pass to query. But it doesn't work.

The following it my code:

dim work_field as string
work_field = "1"
DoCmd.OpenQuery "ABC"

Thanks
Jason
 
Hi Jason,
To pass the parameter to a query, you need to create a parameter form and
set your value to the field on the form. Also your query criteria need to
use the fields on the form. For more detailed information, please refer to
this article:
Using parameters with queries and reports
http://office.microsoft.com/en-us/access/HA011170771033.aspx

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
Hi Charles,

Thank you very much for your reply.

I know the method that you mentioned. But I need different forms to call
same query. Do you have any idea ?

Thanks
Jason.
 
Hi Jason,
In this case, I recommend that you first create a blank query named
TempQuery and then create a global procedure for query like the following:
======================================
Public Sub GetTaskByTitle(strTitle As String)
Dim strSQL As String
strSQL = "SELECT * FROM TASK WHERE Title='" & strTitle & "'"
CurrentDb.QueryDefs("TempQuery").SQL = strSQL
DoCmd.OpenQuery "TempQuery", False
End Sub
=======================================

Then in each form, you can call the procedure like this:
Call Module1.GetTaskByTitle("Task2")

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.


Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
A couple of other options.

1) Have your query refer to a specific form, and have your other forms open
that specific form (and put the value in the text box on the form, if
appropriate). Heck, the form doesn't even have to be visible for it to work
with the query.

2) Create a global function that can pass the value of the variable to the
query and use the function to pass the value to the query. In other words,
declare work_field as a global variable in a module (not a class module nor
a module associated with a form or report), and create a function that
returns the value of that variable:

Public work_field As String

Public Function WorkFieldValue() As String

WorkFieldValue = work_field

End Function

Since work_field is a public variable, you can change its value from
anywhere in Access. In your query, use

WHERE MyField = WorkFieldValue()


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jason said:
Hi Charles,

Thank you very much for your reply.

I know the method that you mentioned. But I need different forms to call
same query. Do you have any idea ?

Thanks
Jason.

"Charles Wang [MSFT]" said:
Hi Jason,
To pass the parameter to a query, you need to create a parameter form and
set your value to the field on the form. Also your query criteria need to
use the fields on the form. For more detailed information, please refer
to
this article:
Using parameters with queries and reports
http://office.microsoft.com/en-us/access/HA011170771033.aspx

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================
Click to expand...
Click to expand...
 
Back
Top