We help IT Professionals succeed at work.

Access 2013 web app use combo box value in query grid to filter records

2,808 Views
Last Modified: 2014-05-28
How do I solve the problem of using the value in a combo box as a filter within a query so that only matching records are used in a subsequent form view.

Example:
Combo box on a screen called [SelectNumber], user selects number 3
After update of combo box will open a form linked to query1, where one of the table fields [MyNumber] is numeric.
This view should only contain those that match number 3.  Cannot reference [SelectNumber]

This is a simple explanation for what will be more complex implementation.  I have looked at setvariable but still cannot reference the variable in a query grid.  Massive data macro examples on line confuse the h**l out of me.  
There must be a simple solution for something that would be the bedrock of any decent system?
Have used Access desktop for many years.
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I can indeed use setvariable to be the value of the selected item in the combo box and then open another view with a where clause to filter [MyNumber] with that variable name.  This can only be done at master view level, any sub view with its own query cannot reference the variable as a setvariable does not exist as a data field when setting master and child settings for the sub view.  Even if the variable is populated into an unbound text box that does not exist to be used as subview child/master, so the underlying query used in the sub view needs to also be able to reference something if it is do do more than simply return the basic results.

Most examples I see is where Microsoft suggest you py the actual value in the query like Today() or Between 1 And 10.  This is too basic for any app that needs to give flexibility where what you are seeing is what you asked for from a form interface.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
As we've seen many times, the new Web Apps are not really ready for prime time, so you may run into limitations of the platform (and this seems to be one).

Perhaps you could write the value to the parent record temporarily, and then use that to link your mainview/subview?

Or somehow filter the subview in one of the mainview's actions?

Author

Commented:
Fortunately I am at the infancy of this app so can try many things, but as you know its good to use a similat methodology theoughout for consistency during devlopment and debugging. I had considered having columns in the main table to update when called but in a multi-user system this would create a bit of an identity crisis when 2 people do the same thing at the same time and the whole issue of updating back to null and the performance overheads with web interface.

Using setvariable gives me the opportunity to use a temp value from a form in a where clause, do you know for how long that variable exists to be referenced frlm that point forward. The only thing I know for sure is that any requery or where clause filter dissapear if the Internet Explorer/Firefox window is refreshed.  If I continue with setvariable I don't want to loose the reference during normal use of the form.  Interestingly any datasheet form filtered by a variable on open will still display all records in the underlying table when using the sort/filter utility associated with each column heading. Another reason why there must be a way to have filtered the data at query level instead of form query/on open where clause.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
From my understanding it's pretty much like the TempVars collection in 2010, which means it'll be there until you reset or remove it.

Another reason why there must be a way to have filtered the data at query level instead of form query/on open where clause.
Don't bet on it. Many of use were very excited with MSFT announced web apps, but we were all very disappointed when we started working with them. Unless you need the simplest of apps - essentially just basic listings and such - then they're not really ready for serious use yet.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"then they're not really ready for serious use yet."
I'm not sure I agree with that, and I think Jeff Conrad has proven otherwise. If you get Jeff's book Access 2013 Inside Out ... and download the sample Web Apps, he has done some pretty amazing things. For example, his full blown Restaurant Management App, complete with a 'back office' module (desktop side talking to SQL tables via VBA code) ... shows what can actually be done with Web Apps.  It's a lot more than just a 'list' :-)

If you are serious about Web Apps, the I highly recommend you get Jeff's book, as well as Teresa Hennig's book Professional Access 2013 Programming- which has contributions from several Access MVPs, all of which I have met in person.
The combination of these two books should give you a major jump start toward building Web Apps.
I have both books :-)

mx
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I'd have to see it to believe it ... while I think the web apps side of things is moving forward, I couldn't suggest anyone try to build a serious business-use app around that framework. As I've said many times before, if you want to create a web-based application, then use a web language and do it the right way.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
I guess that means you don't have the book and downloads then.

Author

Commented:
I suspect the biggest issue is that web apps with sharepoint 2013 is still quite new and the expertise has not yet filtered down to 'standard' users.  Any desktop Access question would be answered in seconds due to the huge user base.

Can anyone explain how I can open a view where the sub view results have been filtered by a selection from a combo box, and this subview has an underlying query as its data source.  Same technique hopefully with popup view.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
My apologies, I was only referring to Access 2013 web apps as being new. I actually have Jeff's book but every data macro example uses a fixed value i.e. Between Date()-7 And Date()   All query examples also only contain fixed value expressions i.e. Yes or 'Research".  

Imagine you are building a view to display some results that require data from 3 or 4 related tables  Your data source is a query, which itself is based on a more complex grouping and counting query.  I need to get a reference value from a UI form as a filter down at this first query level.  Does anyone know how this is done?

In desktop Access it would be easy as any form control can be referenced anywhere whilst it exists, and desktop Access also allows you to build temporary tables in each users front end that can be used to isolate lists of already filtered data that would not impact on any other user.

Author

Commented:
Still struggling but moved forward a bit.  I have a paramatized query, and the declared parameter is 'MyClass', number, no decimals.  I have entered that in the query design grid in the 'ClassLink' field which holds the ID number of the classs that a child is in.  How can I pass the parameter to this query in a 'Change View' macro, not an 'Open Popup' macro, and how can I pass this parameter to a query used in a form subview?

Author

Commented:
Jeff's book did help, but only for a popup datasheet view, which displays an area to feed a value to any underlying query parameter in  a macro when an 'Open PopUp' command is selected and the requested views data source has a query paramater   Helped along the way but still looking for an example of how to pass a parameter to a query that is used as the data source for a sub view form.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.