Access 2013 web app use combo box value in query grid to filter records
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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.
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.
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.
"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
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
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.
I guess that means you don't have the book and downloads then.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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?
ASKER
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.
ASKER
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.