How to select certain records in a query to print (1 Viewer)

Lkwdmntr

Registered User.
Local time
Today, 14:23
Joined
Jul 10, 2019
Messages
277
I have a cut down version of my database that focuses on only the problem at hand. Still too big to attach, but I have a google drive link to download the database.


If you open the AchmntProgress form and then hit Print All, a report that has all the actions is open and is working great. The other option on the form is to Select and Print. What happens now is that the query is called and a form showing all the actions pops up. What I need is to be able to select which actions I want and then pull up the report with only those actions. I hope you can help. Thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
Code:
Private Sub Print_Click()
 DoCmd.OpenReport "AchmntProgress_Report", acViewPreview, , "Action = '" & Me.Action & "'"
End Sub
 

plog

Banishment Pending
Local time
Today, 16:23
Joined
May 11, 2011
Messages
11,611
Whenever a database is posted my first reaction is to look at your tables and not even register what you are posting about. I do this because if the tables are not structured properly there's no point trying to fix whatever your issue is because it would be applying a fix to a system that has bigger problems than what was posted about.

This is true in your case. I suggest you put away whatever this issue is and learn about normalization (https://en.wikipedia.org/wiki/Database_normalization). Your tables are not set up correctly and whatever issues you have are probably just a symptom of this much larger issue.

Although I have very little knowledge of the what this database is to do, or how you've set up the tables I see common major errors in what you have done:

1. Not using autonumber primary keys. First, no table you posted has a primary key defined--this is important, ensuring uniqueness in a relational database (where you link tables together in queries) is imperative, primary keys accomplish this. You did use an autonumber for Users.UserID, but you did not make it the primary key.

2. Data suffixed/prefixed to field names. When you have fields like [MonACYes], [MonACNo], [TuesACYes], [TuesACNo], [WedACYes], [WedACNo] you have done both these things. Mon/Tue/Wed, etc is data and should not be in a field name, but in a field as a value; likewise Yes/No are values that should be stored in a field. Those 14 fields (7 days * 2 Yes/No) should become 3 fields in a new table-->1 field to hold a foreign key that ties back to WeeklyChallenges table (foreign key/primary key are integral concepts to database, learn them: https://www.w3schools.com/sql/sql_foreignkey.asp), 1 field to hold the day of the week and 1 field to hold the Yes/No. Then in that new table you have 7 rows for a week of data, not 1 field per day of the week.

3. Duplicated data. I am not sure you are doing this, but you have tons of fields in Weekly_StartTime_Challenges that are also in Weekly_Challenges. Is this the same data in both tables? It shouldn't be. Using the concept of primary/foriegn keys you only store data in one table and then link the tables by those keys when you need it all together.

4. Calculated data. This data does not belong in a table. I see numerous calculated fields in Weekly_StartTime_Challenges and they simply should not be there. Instead they should be in a query. Access did a bad thing allowing those types of fields into their tables and should not have done it.

So, I would put aside whatever issue you posted about and start about fixing the tables of your database by following the rules of normalization.
 

Lkwdmntr

Registered User.
Local time
Today, 14:23
Joined
Jul 10, 2019
Messages
277
I do realize that this database has a lot of issues, but believe it or not, it does work well. There are many reasons why things are the way they are and when the next version of this comes out, all of these issues will be addressed. The needs of this project were not given all at once, but slowly throughout an entire year. This issue is one of the last major needs before we start using it.

So, if you can put aside the need for normalization and please help with a solution, it would greatly be appreciated. The second post does not appear to be a solution. What is needed is a way to select the actions in the query, maybe adding a checkbox field and then using that criteria in the query to print.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
The second post does not appear to be a solution. What is needed is a way to select the actions in the query, maybe adding a checkbox field and then using that criteria in the query to print
Trusted me it works, I do not post non working solutions.
 

Lkwdmntr

Registered User.
Local time
Today, 14:23
Joined
Jul 10, 2019
Messages
277
My apologies MajP, it does work for the one action that is showing. My hopes were to pull up the list of actions and select which ones I would like to print. I would think that there will be times when more than one, but not all, will need to be printed. Just trying to accommodate all possibilities.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
See demo and function on how to do a multiselect.
 

Attachments

  • ActionsSelect.zip
    28.8 KB · Views: 106

Lkwdmntr

Registered User.
Local time
Today, 14:23
Joined
Jul 10, 2019
Messages
277
MajP, You Are The Man!

This solution works great, it took me a little time to get it all straight and looking professional, but I got it. Thanks to you.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:23
Joined
May 21, 2018
Messages
8,463
Save that module somewhere you will likely want to reuse it in other applications.
 

Users who are viewing this thread

Top Bottom