Hello -

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

    *Title - Table field is [SongTitle]
    *Lyrics - Table field is [SongLyrics]
    *And has a Yes/No field [PrintSong] which selects the song to be printed

As the user goes from record to record in the form, they may select this yes/no field to select that specific song to be pulled up in a report in print preview mode. The PrintSong checkbox is tied to a query which feeds the report. 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:

Print Oder:  Selected Songs:
1            Song 10
2            Song 23
3            Song 31

...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.

The only solution that makes sense to me is to create a new table to add these selected songs but here's the problem: I suspect that this may require some level of SQL statement concatenated into a VBA macro which I am not sure I can pull off.

This form where the user enters all data to include the PrintSong checkbox feeds my main table - SongT.

So while the selected song does show as selected in the SongT table, it is in an order that does not follow the 1, 2, 3...output I would like, plus I wouldn't know how to add each song the user selects to a visual control the user can see.

I really hope this makes sense.

Thank you, thank you!

---

Reply from Alex Hedley:

I'd create a Temp table called temp_SongT
ID, SongID

You could run this code on selection.

Dim strSQL As String
strSQL = "INSERT INTO temp_SongT (SongID) VALUES ('" & SongID & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

SubForm.Requery

In a subform set the source to temp_SongT
You'll need a way to remove selections too, so research a DELETE query.