use control to assign value to query parameter

hey guys i've got a query with parameters as per the picture. very simple query.query
i've also got a form which uses this query as its record source. form
i know that i can create parameters in queries that refer to form controls doing something like [Form]![Control]! etc etc (sorry i'm not using the proper syntax)

however i don't want to do that. because my query's parameters are already named parameters and i don't want to change them. instead, i want to link my form's controls to the parameters as per the picture. so i put in the control source of my txtDate - [dteValueDate]

but somehow that's not working.

could someone advise me how i can achieve what i'm trying to do? i don't want to ever refer to form controls in my query as it hardcodes the form control reference in the query.

thanks guys!! = ))
============================================================
P.S. guys below is one post that Christian (Bitsqueezer) spent quite some time typing to me (really appreciate this super generosity!!) and it really helped me understand these parameter queries better and also forms the basis of why i never want to hard link form controls in my query as parameters. i've included it below. apologies for the funny formatting

Hi,

Allen Browne is for sure one of the top experts in Access - but that doesn't mean that everything is right what he says...:-)

The article describes only Form references used as parameter - I'm sorry to say that, but that is a complete misunderstanding of the correct usage of parameters in a query. The reason is the error which he gets which he describes as bug - I would describe it as "wrong way of using it", like with your Interface problem.

1.) A query, especially a parameterized query, should simply NEVER contain any reference to an object which has nothing to do with SQL, except VBA functions. A control reference is one of the really badest things MS allowed in Access, there is no other SQL based database which allows you to do that and you will get in heavy problems if you ever need to upgrade your backend from Access to a normal database server.
The thing is: You create a hard link between a form and a query. If the form is not open, the query fails. If the form is renamed (and autorenaming is off like recommended from many experts), the query is broken. If the query should be upsized it fails again as no database server can work with such references.
The point is: It is unnecessary in ALL cases (my opinion). You see that if you work a while with ADP and SQL Server where you can't use queries with control references. It's always possible to do it without such references. I always try to keep the objects (forms, reports, class modules, modules, queries and so on) completely independent from each other.

2) a control value is nothing else than a variant. You can enter any type of value into it and also the NULL value is possible. You can declare a type in the PARAMETERS list which seems to be only the expected type but can (in opposite to VBA) also contain NULL, so SQL data types are always the specified type plus the possibility that it can also be NULL (in VBA you can declare a string, but it can't get the NULL value). That's important in future cases when you use any other database, too.

3) Allen says that's a bug if the control value will be converted to a "", I say, in the way he designed the query, it's more a bug that it works with a long value.
Let's look into his query:
PARAMETERS [Forms].[Form1].[txtCustomerID] Text (255);
SELECT Orders.*
FROM Orders
WHERE (([Forms].[Form1].[txtCustomerID] Is Null)
OR (Orders.CustomerID = [Forms].[Form1].[txtCustomerID]));
1:
2:
3:
4:
5:
Select all
Open in new window

So he defines that a control on a form (which has nothing to do with a SQL datatype) should be handled as Text. Then the query says "return to me all records where the contents of the form's control is NULL" - when should that be true? Always, if the field is empty (NULL). So I would expect to get all records as you say "WHERE True Or ...". As the textbox control indeed returns "" instead of NULL it works this way if you use "WHERE (([Forms].[Form1].[txtCustomerID] ="")". So that's pure nonsense - sorry,Allen.

You can't decide if the textbox is really NULL or "", you don't see that, in both cases you have an empty field on the form. So to handle both, you must use Nz. As the field which should be compared, can be NULL (or, depending on the definition of the field in the table), can also be "". So the only really meaningful way of comparing these two is this:

WHERE Nz(Orders.EmployeeID) = Nz([Forms].[Form1].[txtEmployeeID])
1:
Select all
Open in new window


As that works in any case you simply don't need the PARAMETERS list, and this is also another surprising thing: That MS programmers allowed to do such things.

4) Only use variable names as parameters!!
A really meaningful way of using the PARAMETERS list is to define variables which then can be used outside of the query like this:

PARAMETERS strMyTextParam Text ( 255 ), datMyDateParam DateTime, bolMyBooleanParam Bit;
SELECT Table1.Test
FROM Table1
WHERE Table1.Test = strMyTextParam
   AND Table1.Test2 = datMyDateParam
   AND Table1.Test3 = bolMyBooleanParam;
1:
2:
3:
4:
5:
6:
Select all
Open in new window


Now wherever you need to call this query, you can fill the parameters using their names and the Parameters collection of the QueryDef object. That's by the way the same method you would use to fill parameters of a stored procedure in ADO. The best thing with this method is that you don't need to think about if you must use #, ' or nothing - always the value will be assigned to a parameter, nothing else.

So a parameter list is very useful, you have a typed variable and you can assign that with the Parameters collection.

5) As JDettman said earlier: Nz always returns a string to the query, so that has nothing to do with a calculated field. You must use a convert function to convert it back to the wanted type. So again, no points, Mr. Browne...:-)

6) the rest of the list is really no argument for not using a saved query. The number of brackets for example is irrelevant, add as many as you want as long as the logic is not changed - and that was never the case in all the time I used Access. "Running out of rows in the designer" - I would not have thought that someone like Allen has a problem with this, as he should be able to simply change the SQL command...:-)
That the SQL editor of Access is really trash is not new, I don't think MS will ever change that (keep the professional possibilities bad to push other MS products...). The target group for Access is an Office user and he would never use the SQL view...:-)
A saved query is always MORE efficient as a dynamic SQL.

Cheers,

Christian
developingprogrammerAsked:
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.

mbizupCommented:
You're using Access 2007 (and higher), correct?

Just use TempVars.  This is exactly what they are designed for.

Set your TempVars in the form (or wherever the value is coming from):

TempVars.Add "dteValueDate", Me.txtDateStart.Value

And in your query, omit the Parameters clause and just refer to your TempVars in criteria as needed like this:


TempVars("dteValueDate")


Note that I've bolded the Value property in the Tempvars.Add statement.  Generally, .Value is optional (default) when referring to a control's value.  However, when working with TempVars, you must specify the Value property when referring to controls.
0
mbizupCommented:
As a more direct answer to your question -- you CAN set parameter values, using the Parameters of a querydef object.  This method is very .Net-ish IMO (not necessarily a BAD thing, but just not used much among Access folks):

With CurrentDb.QueryDefs("YourQueryName")
   .Parameters("dteValueDate") = Me.txtDateStart
    '  additional code goes here to apply your query where needed, eg:
    Me.recordsource = .SQL
End With

Open in new window



But why go to this trouble when you have TempVars at your disposal?
0
developingprogrammerAuthor Commented:
Whao cool!! But mbizup if my users are using Access 2007 whilst I'm using Access 2003 will this technique still work? Thanks so much for your great solution!! = ))
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mbizupCommented:
<<
whilst I'm using Access 2003 will this technique still work?
>>

Nope.  You can't use TempVars as an Access 2003 developer.  They simply aren't available in earlier versions.
0
mbizupCommented:
A method similar to TempVars that I've seen people use when the criteria for ONE query may be set from a variety of forms is to use public functions in conjunction with global variable.

ie: in some module, define a global variable:

Global gdtDateParameter as Date

Open in new window


and a function:

Function getDateParameter() as Date
      getDateParameter = gdtDateParameter 
End Function

Open in new window


And in a form's code,
gdtDateParameter = Me.txtDateField

Open in new window


Your query's date field criteria would look something like this:

WHERE DateField = getDateParameter()


Just a note about this method -- it will work in all versions of Access, but I'd generally choose the hardcoded form references over this to keep my global variables to a minimum.  The only time I would opt for this method would be if I had a query whose parameters could come from several different forms.  For example a recordsource query from a form or report that can be opened from several different places.
0
Dale FyeCommented:
Before TempVars, I used a technique similar to Miriams, but which allowed me to set the value of the function or call it, without using the global variables:
Public Function fnStartDate(Optional SetDate as Variant=Null) as Variant

    Static StartDate as date

    If not isnull(SetDate) Then StartDate = SetDate

    fnStartDate = StartDate

End Function

Open in new window

To set the value of the function you would simply use:
fnStartDate Me.txtDateStart

Open in new window

And to use it in query you would do something like:
SELECT * FROM yourTable WHERE TransactionDateTime = fnStartDate()

Open in new window

However, the name of that field [TransactionDateTime] implies that the field is filled using the Now() function which includes not only the Date() but also a time value other than "00:00:00".  If that is the case, and you want the criteria to return all transactions for that date, then a better syntax would be:
SELECT * FROM yourTable WHERE DateValue([TransactionDateTime]) = fnStartDate()

Open in new window

0
developingprogrammerAuthor Commented:
hi mbizup! thanks so much for your great sharing!!

i'm so sorry my reply was quite scant just now and also taking awhile to get back to you, been awake for well more than 48hrs and i am about to shut down soon ha = )

that technique is really cool and circumvents quite a few things (the function). hrmm but i think i'm leaning towards the .NETish method you mentioned. seems like a more "proper" way albeit a bit more troublesome. and yup i'm using access 2007 whilst my users will be using 2003.

OH NO! i just re-read what i typed above just now!

I -- ME ME!! I AM USING ACCESS 2007!!

my users will be using Access 2003. haha sleep deficit!!

so does TempVars still work if my users are using Access 2003?
0
developingprogrammerAuthor Commented:
fyed thanks for your help!!

i like the use of static a lot! i read about static last time but didn't really have much of an idea how to use it. but your example's perfect! i will try and think of more ways how i can use static now that i see how you've used it so elegantly in a function instead of creating globals (which has been what i was doing haha = P  )
0
Dale FyeCommented:
DP, I used that syntax a lot in 2003 apps, but now use TempVars in 2007 and later.
0
developingprogrammerAuthor Commented:
thanks fyed!! = )

so if you are developing in Access 2007 but your users are opening your .mdb using Access 2003 do the TempVars still work fyed? thanks!! = ))
0
Dale FyeCommented:
No, TempVars are not present in 2003.

I NEVER develop in 2007 for users that have 2003.  I have 2000, 2003, 2007 and 2010 installed on various machines and always develop on the same platform that the user with the oldest version of office will be using.

This avoids the issue of backward compatibility of Access capabilities and also avoids the references problem which you will inevitably encounter if you develop in a new version and expect users to run it in an earlier version.
0
developingprogrammerAuthor Commented:
ah yes! the references problem. super good point fyed = ))

unfortunately i'm using my office laptop and they only allow one installation of Access AND we don't get to choose haha. i'm a hidden developer in office!

Crouching Employee Hidden Developer!!

haha = PP

hrmm i'm thinking whether i should develop on a personal laptop. but that's a bit too "extreme" in the sense i'm actually not even allowed to do development at my workplace but if this succeeds then doors will SLIGHTLY become AJAR haha.

hey fyed i bought the FMS Ultimate Suite. i'm hoping that that will take care of the deployment!! i'm super excited about using it!! = )) boag2000 was also recommending their Components product to make animated GIFS.

hey i also came across this SUPER cool article about progress bar. i haven't gone through it yet but i really wanna do so during the weekend = )) here you go if you're interested!! = ))

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_1756-A-VBA-Progress-Bar-for-Excel-and-Other-Microsoft-Apps.html
0
mbizupCommented:
<<
so if you are developing in Access 2007 but your users are opening your .mdb using Access 2003>>

A little more on that... ALWAYS develop on the lowest version used by your clients.

Also a direct answer... NO, TempVars will not work if you develop in 2007 for 2003 clients.

You asked the converse of this earlier - developing in 2003 for 2007 clients.

The problem is not references; the problem is that TempVars simply do not exist in versions earlier than 2007.  (Its like going back in time and telling a caveman to drive a car)
0
Dale FyeCommented:
I wrote an article on Progress Bars here in EE a year or two ago.  

I use that when I want to display progress bars in my applications.
0
developingprogrammerAuthor Commented:
haha mbizup, maybe cavemen can learn how to drive cars if they have a Cavemen'sExchange!! haha = PP

but thanks for your very clear answers! really help! = )

ah fyed ok! got your article! reading it now!! = ))
0
BitsqueezerCommented:
Hi,

using TempVars is really similar to using hardcoded references - because it is an object which only exists in Access. It was introduced to gave Macros the possibility to work with variables. You can also use that in VBA because it has the advantage that it's contents will not be deleted in case of an unhandled error (as any other global variable).

So I would not use that in a query, keep the queries away from specialities of VBA, that makes it easier to upsize the backend later where you would have no chance to use TempVars.

Instead, the method of mbizup is the right one, the ".NETish" one....:-)
But the syntax was not correct, you cannot assign a parameter value and then use the SQL property as this will of course not change when you set a parameter value.

This one would work:
With CurrentDb.QueryDefs("YourQueryName")
   .Parameters("dteValueDate") = Me.txtDateStart
    Set Me.Recordset = .OpenRecordset(dbOpenDynaset, dbSeeChanges)
End With

Open in new window

That's the only way to use a parameterized query inside of a form without any VBA methods.
But be careful: A parameterized query assigned through the Recordset property sometimes can be a little bit strange, depending on the query, the fields in the form, the functions you implemented. It needs more tests than a normal bound query.

An alternative method is: Don't work with WHERE in a form query, except it is a static WHERE without parameters. Instead, simply use a filter.

I don't have that problem as I normally use SQL Server as backend and ADP as frontend where I have the ServerFilter property which is directly translated into a WHERE. That's the really fastest method of getting filtered data back from the server if the data source is a view. We work with ADO in ADP and so there's also no QueryDef needed. I could also use a stored procedure and simply send the parameters to the procedure. That's again one of the things why I always say: Throw away Access Backend and use a database server.

(@mbizup: Little hint: The keyword "Global" is outdated, you should use "Public" only.)

Cheers,

Christian
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
mbizupCommented:
<< using TempVars is really similar to using hardcoded references >>

I disagree that TempVars are similar to hardcoded form references.

The major functional difference that makes them applicable here is that form references refer to a specific form (very rigid). TempVars on the contrary are more generic, not limited to a specific form - and the value can be set from anywhere in the database (much more flexible).

 (assuming a 2007 + environment all around, of course)
0
BitsqueezerCommented:
Hi mbizup,

yes, that's all true what you've written, that's not what I meant. Of course is TempVars more flexible and the query will not break if a form is not open like with a form reference.

What I meant is: Simply always don't use any references to objects which are only available in special environments. The best example is what you've already said above: If you use A2003 or older there is no TempVars object and your application breaks.

With the parameter query you do not have this problem as this should work in older and in newer versions, so you use it without thinking about the environment.

Moreover: The TempVars is a Variant object whereas a PARAMETER clause can use an exact datatype.

Cheers,

Christian
0
Dale FyeCommented:
I can understand Christian's avoidance of TempVars, global variables, and function calls as critieria in Access queries, since a lot of his work appears to be ADPs which have SQL Server as the backend.  But for many companies, Access is sufficient for the BE of the application and in those cases my argument is "use the tools you have".
0
mbizupCommented:
<<
Simply always don't use any references to objects which are only available in special environments. The best example is what you've already said above: If you use A2003 or older there is no TempVars object and your application breaks.
>>

I think understand what you mean here... however if you are working in a known, controlled environment, you might be selling yourself short by NOT working with some of the better new features.  (think of some of the differences between A97 and 2003).  But you do have to pick and choose which new features you use.  For example, there is no parallel to Multivalue or Attachment fields (which generally both were a bad idea) in SQL Server. As a result, we see the occasional question here by frantic developers whose tables will not upsize to SQL Server as cleanly as they would like.

For example, if your organization as a whole has upgraded past Access 2003 as many have, there is no issue with using with using TempVars.  They simply work with newer versions of Access, and since they are a UI (front-end) tool in their usage, they work regardless of whether the backend is in Access or upsized to SQL Server (so they are a 'safe' new feature in this regard).

Granted, they will not convert to .Net front-ends, but then again very little in an Access database WILL seamlessly convert.
0
BitsqueezerCommented:
Hi fyed,

you are of course right, I don't work with Access BEs and so I personally of course avoid any such things as an ADP simply always uses T-SQL and there is no chance to use such things.

Nevertheless, I can only always repeat: "Access is sufficient for the BE" is more a decision of lazyness or insufficient knowledge (like often in management levels) or that they let themselves terrorize by their own IT department which doesn't "allow" other solutions. I'm have the strict opinion that nowadays an Access BE is no longer useful, not in the smallest office, as there are a lot of free database server alternatives (including SQL Server Express) which are all more powerful and more secure and also really simple to work with if you do not expect or use more than what you do with Access BE (a table repository, nothing else). So if I have the choice to use a sports car or a bycicle, why should I ever use a bycicle if I get the sports car for free? I really doesn't understand why so many people say "Access is sufficient as BE" - I would say: Access is sufficient as database in case of a standalone solution, i.e. to forward a file to a customer to let him enter his quotes in a database so I can replicate that later when he sends the file back - superb. It's sufficient if I manage my home collection of CDs in a single file - perfect. But whenever I begin to use it with more than one user or want to use a separate BE I immediately switch to SQL Server, in ANY case. Interestingly I never saw a real argument which clearly gives any REAL advantage using Access as BE, other than "my boss don't allow that, our IT don't allow that, it's easier to setup (it is not)" and so on. It's up to us to convert such constructs to a real database solution made by people who have only read how to start Access... but really: How much does it cost to program a database, even by a novice? If you add the costs and compare that to the cost you need to purchase a full version or even a free one with the needed hardware (which can be a simple PC if you want) it is NOTHING in comparison, especially if the data is lost because of a user who has corrupted the BE (which is no big thing in Access).

Hi mbizup,

you are of course right, that thing about "isn't there in older versions" is also the case for any other feature. It's also true that you must pick the right ones carefully as MS loves to add senseless features like the Attachments, the Multi-Valued Fields and also improvements to create Macros or to make Webdatabases but cut off any VBA function so that you are forced to use Macros. A2013 is the horriblest version until now, especially because they removed one of the last professional features, ADPs. I'm waiting for the day when they remove VBA and stuck on Macros. A2013 is in my eyes a toy for Office users, nothing more.

What I wanted to say is: Especially in the case of SQL objects should always be separated as good as possible so they are always independent from each other. I also see often things like direct manipulations of controls in other forms from another one and so on, that's the same. You CAN do that with Access, but only because you can do there is no need to do it as long as there is a better way. Access was always created to be used by beginners, and so it is full of ways to allow even worse things and still works. That's the way it was meant by MS, making anything possible for any type of user. Access is not wanted to be seen as a professional development platform to not be a real concurrency to the professional Visual Studio (which is full of strange issues so a programmer here will get white hairs in a short time...). So if we as professional developers want to get a clean product at least we should use the most professional ways of doing things to be sure that the product runs stable. The result is of course that it takes longer, it is more uncomfortable to create a professional solution, but it is also easier to maintain. For example about the TempVars: What if you change the variable name you used in TempVars? The query would not work anymore. No problem, it's only one query. But if you used the same TempVars variable in 25 queries you have a lot to do to keep them maintained. But if you have anything which should belong into the code inside of the code there is no need to do that. You would not need any variable name in case of the parameterized query, only the name of the parameter you used in the query. It is more likely that you sometimes change the name of a TempVars variable than that you ever change the name of a query parameter. And if, then it is only this one query you would need to change. 1 of 25.

If you keep objects separated and use clear interfaces between all (like public properties in forms or like a parameter name in queries) you would have a lot less errors and problems in the application, and that is what I wanted to state with all this.

Cheers,

Christian
0
developingprogrammerAuthor Commented:
hi fyed i read your article on the progress bar and i love it!! = )) i will definitely implement it in the wrapping up stages of my project = ))

thanks Christian for taking a look at this question of mine!!

yup yup i agree with everything yall are saying and i guess the decision is really based on how we want to structure our application to fit our scenario. whether we're using just Access or whether we're using SQL Server as a back end.

Christian whilst reading your reply i saw that you mentioned we should use public properties for forms and that led me to think about many things you said previously about inheritance, interfaces etc.

i came out with a reply and wanted to post here but i think as the topic is a little different i posted it to another question - here is the URL if you don't mind having a look! thanks Christian!!

guys thanks for all your help once again and what a great discussion it has been!! so many angles to learn from!! = ))
0
developingprogrammerAuthor Commented:
hi Christian! i also have got another question regard what you said in this portion

But be careful: A parameterized query assigned through the Recordset property sometimes can be a little bit strange, depending on the query, the fields in the form, the functions you implemented. It needs more tests than a normal bound query.

i've posted it in another question so that others can also benefit from it without reading too long a question = )

if you don't mind taking a look here's the link!! thanks Christian!! = ))

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28226852.html
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.