Clicky

I need to be able to have my report with the check boxes filled in if there values are selected in a form. The users right now only have the ability to select one entry in the form drop-down\combo box right now, but they need to be able to select multiple entries which get stored into a SQL table. From there, the report needs to pull that data into a column of checkboxes (check all that apply).

Please help me out with this one as I have tried multivalued lookup fields using a list box, but that won't work with the SQL table. I tried a really long IIf statement, but an error came up and cut off the IIf statement because it was too long.  

asked 12/05/2011 08:51

IEHP1's gravatar image

IEHP1 ♦♦


17 Answers:
Create a temp table for the selection options and add a boolean field to the temp table.  Create it as its own form with the boolean field bound to a checkbox control in multi-record mode and then display that form on your report selection form as a subform.  Then in the report you can lookup the individual items from the temp table using dlookup() or just display them as a subreport on the report.
link

answered

kmslogic's gravatar image

kmslogic

Need more info.
Table t(a, b, c) c values are Yes or No
Form f (is it bound to table t)
CheckBoxes represent what in table (t)?
Report r (bound to what and checkBoxes are the one on the form)?

Explain the problem using a table with few fields and few records.
link

answered 2011-12-06 at 05:19:03

hnasr's gravatar image

hnasr

I have a "temp table" as you call it, but it isn't temporary. It is a table where I hold all of the entries (the drop-down box on the subform uses those entries to have them show up on the subform's drop-down box).

The subform has the drop-down box and it is driven by a query that uses 1 SQL table. In that subform, it has a drop-down box that lists the entries that the user can select. The users can only select one choice right now, but I think they will have to be able to select multiple because the report needs to show "check all that apply."

The checkboxes are only on the report and I need them to be checked off to show on the report (check all that apply) and allow for mulitple selections on one report. The report is driven by a query that has the same field from the same SQL table as the subform. The record source of the checkboxes are that same field from that same SQL table.
link

answered 2011-12-06 at 06:09:16

IEHP1's gravatar image

IEHP1

i am thinking that the only way to do what I would like it to do is to have fields\columns in the SQL table for each of the entries in the subform's drop-down box and change it to a list box and the multi-select property to simple (so that users can select multiple entries). But I don't know if there needs to be some kind of IIf statement or programming to make the entries go into the correct fields\columns.

If it was set up like that, then I can use an IIf statement in my report's query to pull the checkbox to be on or off depending on how it is stored in the SQL table. I actually believe that I would have to create a tag for each of the 11 checkboxes and create an IIf statement for each one of them seperate from the other IIf statements.

Please let me know what you think and how to approach this. I am not that experienced doing this kind of stuff so I need someone to help me out at my level.
link

answered 2011-12-06 at 08:29:32

IEHP1's gravatar image

IEHP1

For me at least, this would all be clearer if you posted a sample database...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
link

answered 2011-12-06 at 10:28:27

boag2000's gravatar image

boag2000

ok I will work on it. Let me go eat and then I will post it. Thank you in advance.
link

answered 2011-12-06 at 11:47:01

IEHP1's gravatar image

IEHP1

Here is my sample database. So for the report it needs to be check all that apply so whatever gets stored into the SQL table, that needs to show as a check box for each one applicable. Let me know please and thanks for your help....
link

answered 2011-12-06 at 12:07:36

IEHP1's gravatar image

IEHP1

Still confused....

It is not clear how the objects in your sample are related to the issue at hand...
(For example, it is not clear how a user would select more than 1 reason...?)

As I requested:

"also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data."


In other words, please explain , step by step, (from the users point of view) exactly what steps must be taken, and present an example of the *EXACT* output required.
link

answered 2011-12-06 at 13:44:42

boag2000's gravatar image

boag2000

I want the drop-down to list all of the Reasons and the users to be able to select as many as they need to select. This data that they select will go into the SQL table to be stored there. From the SQL table, I need to pull in that data (those Reasons) so that they check off the correct check box.....

1. User selects Reason(s) in the form
2. Data is stored in SQL table
3. Report pulls in the information from the SQL table to check off the proper check boxes in the report

What are you confused about? I don't have an example of the "EXACT" output required because I am creating it right now.

The attached word document would have all the checkboxes checked off as an example. Please work with me to figure this out.
link

answered 2011-12-06 at 14:03:09

IEHP1's gravatar image

IEHP1

I provided the dummy records in the SQL table writing them in there manually, not from any update query or anything. So that way we can pull them into the report.
link

answered 2011-12-06 at 15:04:11

IEHP1's gravatar image

IEHP1

Still confused.

1. Can you first take a step back and explain what this system/report is supposed to be doing?
Explain what each record represents, what each field represents
Is this a report for one record?, All records,  One Party? ...?
...etc


2. Why does the report only have checkboxes on it, when there are other fields in the reports record source?
This is what I mean by the exact output...
Is this report only a list of checkboxes?
Do you need to display all 11 reasons, even if only one is selected? (If so, then this will be more complex)
Do the reasons have explanations behind them, or does everyone viewing the report know all of the reasons by the number?

 
3. This is why an explanation of your design is important.
Perhaps there is a more efficient approach...

4. I am not sure this system is designed properly to give you what you want.
Is this design etched in stone?

5. Your table does not appear to be "Normalized".
If one "Party" can have many records, then you need a (main) Party table, and a related (Child) Party/Reasons table (AKA "Many-To-Many table)
There are also repeating "Parties" in this table, again standard database design says that this is then a "Child" table to a "Party" (Main) table
The multiple, repeating dates for each record again would require a related "child" table.

Again, this is why an explanation of this system is so important...

So if your question is that you want to stay with your original design and you want me to: "Make it work", ...I can't
(but perhaps another expert will be willing....)

I would rather take a more structured approach and first make sure my data is properly normalized, *then* create the most appropriate interface.
Make sense?
Here is a sample of how I would handle something like this


JeffCoachman
link

answered 2011-12-06 at 15:05:42

boag2000's gravatar image

boag2000

I just put some miscellaneous dummy fields to show that yes, there are fields that get pulled in by the query, but those are fine, they can pull fine. It is the checkboxes pulling into the report that I am worried about. Yes this is a report for one record (hence, it is hard).

We would only need to display the checkboxes that are checked, not all 11 reasons. The reasons are written out in plain english, not a number (it is just my dummy data).

Oh i know about the party field because I just copied and pasted because I was tired of having to put in 11 records manually. Sorry about that (you can change the numbers to different values can't you?)

I think you are way overthinking this. It is just dummy data that you can manipulate. If you don't want to help me that fine.


Somebody else please help with my problem.
link

answered 2011-12-06 at 21:34:29

IEHP1's gravatar image

IEHP1

I see what you did and I'm sorry but I am not as good as you are with these kind of operations yet.

I like your design, is there a way I can have the frmReturnsMain expand into the table structure if they click on the Reason field in my real database (not the dummy one i sent you). I see how it is storing the data in TblReturnReasons and it is unique by the ReturnID and ReasonID so I believe that I will have to create a new local Access table and link it to the existing query by that field Reason~~~~~ , but that field Reason~~~~~ is not unique in the SQL table (not the primary key). So I can link it to a local Access table on the Reason ~~~~~ field and make the local Access table's Reason~~~~ field the unique primary key right?

Let me know what you think.
link

answered 2011-12-07 at 16:40:57

IEHP1's gravatar image

IEHP1

<I like your design, is there a way I can have the frmReturnsMain expand into the table structure if they click on the Reason field in my real database (not the dummy one i sent you)>
Not sure, ...I don't have access to your real database...

The sample I posted is a fully functioning example of how these systems work, ...in the way that this is commonly done (before Multivalued fields).
...again, I do not have access to your real db, so I cannot give you the specifics of how to adapt it.

<I see what you did and I'm sorry but I am not as good as you are with these kind of operations yet.>
Then please bear in mind that the question you have asked here requires that you have this knowledge...

This is why Experts always stress that you ensure that your tables are properly normalized and related, *before* you create any queries forms or reports...

Here are some great links to help you along with the basic concept of a "many-to-many" relationship:
http://www.databaseprimer.com/relationship_xtox.html
http://technet.microsoft.com/en-us/library/ms170463.aspx
http://www.techrepublic.com/article/accommodating-a-many-to-many-relationship-in-access/5285168
You can do a Google search for more, ...there are even videos on YouTube as well.

;-)

JeffCoachman
link

answered 2011-12-07 at 16:57:07

boag2000's gravatar image

boag2000

Thank you so much for your help. Your database is really good and that is what I needed to see. I am going to work on implementing it at a later date because it isn't a high priority project as of now.

Thank you Respectfully
link

answered 2011-12-08 at 01:12:47

IEHP1's gravatar image

IEHP1

I posted my comments in reply without seeing the database he posted and I was a little frustrated that we couldn't understand each other, but the database he posted and I downloaded is a great example of what I was looknig for. Thank you.
link

answered 2011-12-08 at 16:36:45

IEHP1's gravatar image

IEHP1

;-)
link

answered 2011-12-08 at 16:38:15

boag2000's gravatar image

boag2000

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×2
×2
×1
×1

Asked: 12/05/2011 08:51

Seen: 365 times

Last updated: 12/08/2011 08:38