Query By Form

[justsalsa]

Hello everyone. First question on the forum and I'm looking forward to learning a bunch.

First of all, I'm not sure if this should be in the query sub forum, because I'm having issues with a Query by Form. The results I'm trying to accomplish may even require coding, but I'd like to be aware so I can begin to read up.

If you look at my attached database, the Exclusion Producer form is the input for my query. Here is the scenario:

You must enter a Sales Org and CG1.

Sales Org must be 4 letters, but it defaults to USDS. CG1's must be a combination of 3 alphanumeric characters. i.e. "C60"​

If the user wants, they can click Rolling 12 Month check box. This checkbox results in "Exclusions" to be moved a row over to the ZRUP columns instead of ZVIN columns in my query.

When the user clicks on any of the Classification check boxes, it should replicate the same way in the query results. The ProdClassificationT table shows all lines associated with that classification.
In other words, I want the user to select broad categories in order to produce a list of multiple parameters, but I need to only display those that they want to exclude from the Incentive, the Revenue, or both.

The image attached is an example as to how it should look if you click Competitive Bid sales as "Excluded from Revenue" and Buyouts as "Excluded from Incentives"

After many different attempts of making this work, I'm back to same issue that all parameters show up with "X" on both columns.

I'm not sure if I was too vague or way wordy. let me know if you guys can help me out. I'm just stuck at this point!

Thanks in advance!

Austin

 

[justsalsa]

Any input is appreciated. I'd really like to know if it's possible to accomplish this without coding. If not, I'm going to have to start reading up and I'm 3 or 4 chapters away from it haha.

 

[JHB]

Hmm - looking at your data and example what the result should be, in which column and why, can't you explain a little further, some sample of result plus an explanation why, could bring some light I think?

 

[justsalsa]

Yeah I'm sorry I should have probably given more explanation behind the classification. Please see the attached form picture and the resulting query picture.

In the example, I also selected Rolling 12 Month, so it moved the X's over to the "ZRUP" columns.

In the report I would not want the "Classification" column to appear, but I want the selections of the user on the form to reflect be reflected on the sheet.

Let me know if that helps in solving this issue.

 

[justsalsa]

Anyone got any ideas?

 

[JHB]

Yeah - why do you think it should move over, you have nothing in your query that indicate it should move over.
It means, there are no checking if a check box in your form is set or not!
So you need to start from there.
I would suggest you create a table that reflect the choices made in the form.
You need some code to insert the choices in the table.
The code has to run before the query.
You also need to make a new query.

 

[justsalsa]

Thanks for the help.

I understand that there isn't anything there for designating what check boxes to do. That's why my original question was whether I needed coding or not. I already have a table that designates each condition to the checkbox type.

Anyway, the database has all it needs for coding, but my issue was that I was trying like crazy to make IIF functions work. I'm a newbie at coding, so I'm gonna have to face the facts and learn it.

 

[JHB]

I've made an example for you, maybe it can help you further, database attached.
Else try to explain what fault it has!

 

[justsalsa]

WOW! JHB, you rock my friend! I had totally had given up on this since I wasn't going to have it ready by the end of the month deadline. I just ran through it quickly and looks like it works perfectly. I'm gonna try to study what you did and try to replicate for other uses.

Thanks a million. I had kinda hit a coding wall that I didn't want to go over, but now I'm so pumped to start up.

 

[justsalsa]

The first time I ran the DB, it looked like it worked perfectly. I started analyzing your code, and now I can't replicate the results that I originally achieved. It's like defaulting to showing all records again and doesn't show the ZVIN Revenue side "X" for some records. I redownloaded and it's still not replicating the results. Maybe I'm going doing something dumb?

 

[JHB]

Post a print screen of where the result is wrong, (and what you expected it should be), and also which chooses you made in the form.
I'm sure it can be done, but I'm still unsure which result you expected.

 

[justsalsa]

JHB thanks for the quick reply.

Please see the attachments below.

I think whats happening is that it's not putting X's on both sides of Revenue and Incentive if they are checked on the form. It's also displaying all items regardless if they have been checked or not. For the results, I only need the categories that were checked on the form.

Let me know if everything makes sense.

Thanks again for all your help.

 

[JHB]

Hmm - yeah it was lot different as showed in the other print screens.
Hope it is correct now. Database attached.

 

[justsalsa]

JHB this thing works perfectly! I'm not sure if I would ever come up with a solution like this. I feel like mine would have been much more messier. It's exactly what I asked for and in a way that makes sense to me. Many many thanks.

Just tell me if I'm correct on this.... From what I see from your code, you dont have anything directly associating the individual checkboxes to the classification table. Instead, you are using my checkbox names CompetitiveBidSalesIncentiveC and extracting the first part to compare it. Right?
So theoretically, I could create a new classification and name the check box with the same name type (i.e. ServicesIncentiveC), then it should pair up with the code no problem.

 

[JHB]

Just tell me if I'm correct on this.... From what I see from your code, you dont have anything directly associating the individual checkboxes to the classification table. Instead, you are using my checkbox names CompetitiveBidSalesIncentiveC and extracting the first part to compare it. Right?

Yes exactly.
I use the classification table to get the name of your check boxes, (without spaces).

So theoretically, I could create a new classification and name the check box with the same name type (i.e. ServicesIncentiveC), then it should pair up with the code no problem.

Yes the only requirement - name in the classification table and the "first" part of your check box must match exactly 100%.
I wish you good luck with your project.