how do I save MS access query parameters in a form?

how do I save MS access query parameters in a form?
Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A Query Parameter is an attribute of Query, and you really wouldn't "save" that value on a form. You might use a value on a Form as a query parameter, of course - is that what you mean?

If not, please try to restate your question so that we better understand what you're doing.
1
John TsioumprisSoftware & Systems EngineerCommented:
I am afraid the question is a bit vague...
I guess there are 2 situations

You want to use some controls from your form to pass parameters to your query....
e.g.Dim rst as DAO.Recordset Dim db As Database Set db = CurrentDb Set qdf = db.QueryDefs(YourQuery) qdf.Parameters(A_Parameter) = me.ParameterTextBox set rst = qdf.OpenRecordset


You want to take a look at the parameters of your query
e.g
Dim prm as Parameter Dim ParametersInfo as String set qdf = db.QueryDefs(YourQuery) for each prm in qdf.Parameters ParamentersInfo= prm.Name & vbcrlf next Me.TextBoxShowParametersInfo =ParametersInfo
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you want to use values in your Form to filter a Query, you can also simply use the Criteria row of the Query to "look" at your form. For example, in the image below I have a Form named Form2, and that form has a Textbox named txFilter1. The query is filtered for the value I enter in that Form:
Query Criteria RowThe trouble with this is that your query becomes tightly bound to that form - which means you can use that query ONLY with that form, and if you need another Query that is identical but needs to be filtered differently, you must add a new Query to your database. This can grow unwieldy very quickly, and can become a real maintenance problem.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mohammed DallagPetroleum ConsultantAuthor Commented:
I have many Quires that need to use the same parameter. So, I need to input that in a form and save it somehow. So every time I use any of the queries that parameter will be used unless I changed it.

Regards,

Dallag
0
Dale FyeCommented:
In that case, I generally use a Tempvar.  This was a feature added, I think in A2007, which is a collection of variables.  These variables, once set, can be used anywhere in your application, and can even be set in the immediate window.  To set them, I usually use the afterupdate event of a control on a form, something like:
Private sub txt_StartDate_AfterUpdate

    Tempvars!StartDate = me.txt_StartDate.Value

End Sub

Open in new window

Then, in a query, you would use a criteria like:

WHERE [StartDate] >= [Tempvars]![StartDate]
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you actually using a Parameter in the query, or are you using the Criteria method that I showed?

If you're using the criteria method, just set it up as I showed in my example.

If you're using the Parameter method, then I'm not sure how you'd go about doing that.
0
PatHartmanCommented:
I use a hidden form.  It is similar in concept to the tempVars solution.  I just standardized my method long before TempVars appeared and never felt the need to change.  The advantage of the form is that you can leave it visible for testing and it makes it easier to actually change values midstream.  The problem with TempVars is that there is no easy way to see all of them and they are defined as you populate them so they have the same problem that you would have if you didn't use Option Explicit in your code modules.  You can create a typo and not notice that you are using two variables when you thought you were using one or just as bad - have one variable when you thought you had two.
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
All of these suggestions will work if you only want to save the criteria for the duration of the Access session.  but the OP said "So every time I use any of the queries that parameter will be used unless I changed it."

If you prompt for the criteria on a form, you can modify the .SQL property of the query with a new Where clause.  This avoids using Parameters, and the change will remain until you change it again.  To make this easier, you can download our free J Street SQL Tools code from http://www.jstreettech.com/downloads.  It includes a function called ReplaceWhereClause that does exactly what it says - it finds and replaces the Where clause in a SQL statement.

Hope this helps,
Armen Stein, Access MVP
J Street Technology
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Dale Fye,

Can I use this to read the value from the form

Tempvars!StartDate = forms![form1].test1

Please find my Access database attached.

Regards,

Dallag
test.accdb
0
Dale FyeCommented:
from my iPad, so cannot open your db, but, no.  Tempvars require a value, so you must explicitly indicate the value propery of a control if you are referring to a control.  You cannot assign an object to a tempvar.

tempvars!StartDate = forms!txt_YourTextField.Value

It looks like, from your example code above you are actually trying to access a value on a subform.  The syntax for that would be:

tempvars!StartDate = forms![form1]!form.test1.value

Dale
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
can I save the value from the form so it can be used after closing the form. If yes how can I do that?

Regards,

Dallag
0
Dale FyeCommented:
@dallag,

Yes, I generally use the afterupdate event of the control where the values are being entered.  For example:
Private Sub txt_StartDate_AfterUpdate

    Tempvars!StartDate = me.txt_StartDate.Value

End Sub

Open in new window

This will save that date and you can still use this value after the form where the date is entered is closed.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Dear Dale Fye,

Thank you for your help but how can I read the form value in your script? Should I use

Private Sub txt_StartDate_AfterUpdate

    Tempvars!StartDate = forms!form1.test1

End Sub

Please see the access attached and test it for me

Regards,

Dallag
test.accdb
0
Dale FyeCommented:
dallag,

In order to use the AfterUpdate event of a control, you must click on that control in design view, open the properties dialog, move to the Events tab in the properties dialog, find the After Update event, click the dropdown associated with that control and selecte "Event Procedure".  Then click on the ellipse to the right of the drop-down to get to the vba code window for that event.  See the changes I made to your sample
test.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mohammed DallagPetroleum ConsultantAuthor Commented:
Thank you so much. It is working now.
0
Dale FyeCommented:
glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.