Store reports in form list and print in order (1 Viewer)

[JoseO]

Hello -

I have a form that allows users to enter a song's:

• Title
• lyrics
• And has a Yes/No field (PrintSong) which selects the song to be printed

As the user goes from record to record, they may select this yes/no field to select that specific song to be pulled up in a report in print preview mode. So far so good. All works well.

Here's what I'd like to do: I would like to add a subform (maybe a different control would work better) to "store" each selection (Song title) but here's the catch....in order.

In other words, as the user selects the song they would like to print the subform/control would keep tally in the following way in that subform/control:

1- Song 1
2- Song 2
3- Song 3

...And so on. This control would not only serve as a visual queue for the user of the songs they have selected thus far but I would REALLY like for the songs to be printed in the shown 1, 2, 3, order.

I have tried building a new query, formulas, looking in the report's properties, etc. Maybe I've been at it for so long I have missed the really simple solution. Any help is much, much appreciated.

Thank you!

 

[JHB]

Should the printing order (1, 2, 3 ...) always be in that order they select a title/song?
Ex:
User select song no. 2, then song no. 10, then song no 3.
Then the printing order would be:
Song no. 2 - is first
Song no. 10 - is second
Song no. 3 - is third.

 

[JoseO]

Thank you JHB! Yes! that's it!

 

[JHB]

The easiest would be to create a table and put in the songs when they get selected in that order. Remember also to delete them when they get unselected.

 

[JoseO]

Thank you again JHB.

Makes total sense. My limitation is that I am not quite sure how to place/add the song title to the table and then delete it once the report has ran.

 

[JHB]

Post your database with some sample data, zip it (+ name of the form).

 

[JoseO]

JHB -

I really can't thank you enough for your assistance. I have attached the DB.

The 'MainSongF' form is where the fix would occur. In the upper left hand corner, with red text that says, "Select this song for my worship set" is the where the user selects that specific song.

As a way of recap: It would be nice to have a control (subform, Listbox) within the MainSongF form to provide the user with a visual queue of what songs the user has selected thus far and of course their print order of 1, 2, 3, etc.

Please let me know if you have any other questions you may have. I have been doing Access for about 5 months now.

Again, THANK YOU , THANK YOU JHB. I am very grateful for your assistance.

 

[JHB]

There is an error in the "Form_MouseWheel" so I've comment it out, remember to turn it on again.
I've added a list control to show which songs are selected, listed in the order they are selected - modified database attached.

 

[JoseO]

JHB,

I cannot THANK YOU ENOUGH! Thank you for your patience in dealing with me through this problem. Thank you for taking time out of YOUR day to fix the DB. I am humbled by how you have helped me.

If you didn't live in Denmark I would SO treat you to Starbucks for the biggest latte they have available!

You have no idea how much this helps me. As a worship leader in my service to the Lord, these tools are extremely handy. Again, cannot express how grateful I am!

Take care.

 

[JHB]

..
Take care.

You too, good luck.

 

[JoseO]

JHB,

May I please ask you just one last thing - I have been trying, honestly, but I am just having the hardest time for some reason. I am trying to sort the SelectedSongT table ascending but I keep getting compiler errors:

I have tried the following with no success in the PrintSong_AfterUpdate() Sub:

DoCmd.RunSQL "SELECT * FROM SelectedSongsT ORDER BY [PrintOrder];"

-and-

rst.Sort = "[PrintOrder]"

I promise next time I'll make a general post rather than bother you again. Thank you so much JHB.

 

[JHB]

JHB,

May I please ask you just one last thing - I have been trying, honestly, but I am just having the hardest time for some reason. I am trying to sort the SelectedSongT table ascending but I keep getting compiler errors:

I have tried the following with no success in the PrintSong_AfterUpdate() Sub:

DoCmd.RunSQL "SELECT * FROM SelectedSongsT ORDER BY [PrintOrder];"

-and-

rst.Sort = "[PrintOrder]"

I promise next time I'll make a general post rather than bother you again. Thank you so much JHB.

DoCmd.RunSQL is an action query so it can't be use in the way you've it here. (Lookup the Help file in MS-Access for explanation how/where to use DoCmd.RunSQL).
You can do the sorting in a recordset.

Set rst = dbs.OpenRecordset("SELECT * FROM SelectedSongsT ORDER BY [PrintOrder]")

 

[JoseO]

Will do. Thanks again JHB!