No parameter selected (1 Viewer)

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
I have a totals query that shows results in a chart. It takes a parameter to limit results, by a combobox in a form.

Parameter in the query includes the OR “*” expression, in case someone wants to get the results unfiltered.

The Combobox in the form, has an AfteUpdate event that opens the chart (form) every time its value changes, by the [DoCmd.OpenForm “ChartForm” , acNormal] expression.

Problem is, I don’t know how to make it open the ChartForm when no parameter is selected in the combobox.

Any ideas?

Thank you
 

Never Hide

Registered User.
Local time
Tomorrow, 00:40
Joined
Dec 22, 2011
Messages
96
You can change the Row Source of the combobox to include a "*" value. You can do this with a UNION query.

Something like this
Code:
SELECT ID,Description FROM tblData
UNION
SELECT TOP 1 "*","ALL" FROM tblFake

This will give you a combobox with 2 columns, you can use the 1st comlun as bound and the second to display and get something like this
Code:
Combobox
ID|Descreption
*|ALL
1|Data1
2|Data2
.
.
.
 

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
You can change the Row Source of the combobox to include a "*" value. You can do this with a UNION query.

Something like this
Code:
SELECT ID,Description FROM tblData
UNION
SELECT TOP 1 "*","ALL" FROM tblFake
This will give you a combobox with 2 columns, you can use the 1st comlun as bound and the second to display and get something like this
Code:
Combobox
ID|Descreption
*|ALL
1|Data1
2|Data2
.
.
.



Yeah but, what would be the data in tblFake, 'cause I've tried it and comes up empty when I choose "All"



Thanks
 

Never Hide

Registered User.
Local time
Tomorrow, 00:40
Joined
Dec 22, 2011
Messages
96
Try using the same table in both select statement
Code:
SELECT ID,Description FROM tblData
UNION
SELECT TOP 1 "*","ALL" FROM tblData

It would help if you could provide a copy of the query in which you pass the parameter
 

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
Try using the same table in both select statement
Code:
SELECT ID,Description FROM tblData
UNION
SELECT TOP 1 "*","ALL" FROM tblData
It would help if you could provide a copy of the query in which you pass the parameter



Problem is, when I use the OR "*" expression in query parameter, I always get unfiltered results no matter what I choose from the combobox. When I don't use it, everything works fine except the "All" selection which brings up nothing.

I attached sample database to help you through checking it out.


Thanks
 

Attachments

  • SalesTest2.zip
    30.7 KB · Views: 63

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
Solved it finally!
I used the procedure you suggested but instead of querying by parameter, I queried by parameterID.
Someone said that lookup fields are causing trouble in parameter queries and at a certain degree was right.

Thank you so much, you've been very helpful.
 

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
Well it seems I hurried up a bit :(
Can't manage to get results from the query when I place parameters by form. It all seems to tumble down.
Attached a sample of db in case anyone wants to bother.

Thank you.
 

Attachments

  • SalesTest2.zip
    25 KB · Views: 68

Never Hide

Registered User.
Local time
Tomorrow, 00:40
Joined
Dec 22, 2011
Messages
96
Hey there AlexN,
sorry it took me this long to reply but I was away during the weekend :D
I checked your sample and I didn't see any code in your form, so I assume you select what you want in the form and then run the query. I tried different combinations with selections from the form and it all seems to be working correctly.
Maybe there's a problem with the copy your are running. Have you tried to compact and repair? You can also try to create a new blank database and import everything to the new one
 
Last edited:

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
Hey there AlexN,
sorry it took me this long to reply but I was away during the weekend :D
I checked your sample and I didn't see any code in your form, so I assume you select what you want in the form and then run the query. I tried different combinations with selections from the form and it all seems to be working correctly.
Maybe there's a problem with the copy your are running. Have you tried to compact and repair? You can also try to create a new blank database and import everything to the new one


Thanks for your reply. Well, it seems my machine is haunted.
I've tried everything, and had the same results. If I use only a single criteria it works alright, when I put in the second , it gets confused, and when I put in the third, shows nothing no matter what I choose.
Sometimes it seems that it works fine if I leave no combo blank, but this is not what I want. Same thing happens when I change the sequence of putting criteria in.

Anyway, I'll try creating a blank database and import tables, but I'm afraid this would do nothing. I'll be back with feedback.

Thanks again.
 

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
Tried everything. I even turned back to Access 2010. Nothing doing.
When only one criteria is applied it works fine, when second is applied it goes crazy. After applying the third shows nothing. :(
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:40
Joined
Aug 11, 2003
Messages
11,696
The best way to work with dynamic requirements is to make them dynamic and not try and capture it in a static query

use the
Code:
Currentdb.Querydefs("YourQueryName").sql = "Select ..."
to adjust your sql to suite the need at the time, then simply open the form and be happy :)
 

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
The best way to work with dynamic requirements is to make them dynamic and not try and capture it in a static query

use the
Code:
Currentdb.Querydefs("YourQueryName").sql = "Select ..."
to adjust your sql to suite the need at the time, then simply open the form and be happy :)


Thanks for your reply.
By this time I've changed my aspect of doing things and instead of using a query with three different parameters, I use three queries with one parameter each, more suitable for my case 'cause I want to present results in charts. Advice noted though.


Thank you.
 

namliam

The Mailman - AWF VIP
Local time
Today, 23:40
Joined
Aug 11, 2003
Messages
11,696
Using multiple queries for one purpose may seem OK for now having only 3, however as you progress some day you may have 100 and then if something changes you have a maintenance nightmare on your hands.

Also form entries are so much more friendly and controlable vs popups from queries...
 

AlexN

Registered User.
Local time
Tomorrow, 00:40
Joined
Nov 10, 2014
Messages
302
Using multiple queries for one purpose may seem OK for now having only 3, however as you progress some day you may have 100 and then if something changes you have a maintenance nightmare on your hands.

Absolutely right, but I don't thing I'll ever create such a big database needing 100 queries, I'm not a professional, I do this just to learn something. In my case charted results depend on slighlty different parameters and present slightly different outcome, thus making the use of three different queries (each one based on its own combination of tables) more suitable.


Also form entries are so much more friendly and controlable vs popups from queries...
Yeah, I use form comboboxes to enter parameters to queries.



Thank you
 

Users who are viewing this thread

Top Bottom