I need help setting a report's record source from VBA

[GitarJake]

Hello all,

I hope I can ask my question so you can understand my need.

I have a song database that autoformats lyric sheets and chord charts for
printing. Usually, these are 1 to 3 sheets max. Now, I want to add the
ability to format many more songs (150 - 200 or so) into a songbook. I
want the user to select songs, then click the Songbook button. A pop-up
form displays where the user will enter a name for their collection (in
"txtSongbookName", an unbound textbox) and indicate whether they want
Lyrics, Chord Charts and/or a Table of Contents via ungrouped option
buttons. This form stays open. Then they press the "Go" button.

So far the "Go" button performs these tasks:

1. Creates a table named whatever they typed into txtSongBookName.
2. Appends their selected songs into the new table.
3. Opens the report: "rptChordBookTemplate" but without a recordsource.

I figured once I got this one to work, I could alter the code for Lyrics and
TOC.

What I need to happen is this:

1. Open the report/s with the new table as a recordsource
then:
2. Save itself as "rpt" & [txtSongBookName] & "Chords"

I've tried various sql strings in the Click event of the "Go" button and in
the Open event of the report, but I feel like I am grasping at straws.
Right now among varying unsatisfactory results, Access is returning error
2580. How can I do this?

TIA

Jake

 

[Allen Browne]

Jake, there is a *much* easier way to achieve this.

Presumably you already have the songs in a table, with a SongID for each
one.

Create another table named (say) tblBook, with fields:
BookID AutoNumber primary key
BookOwner Text person who creates this song book.
BookDate Date/Time when they created the song book.

Now another table named (say) tblBookSong, with fields:
BookSongID AutoNumber primary key
BookID Number (Long) foreign key to tblBook.BookID
SongID Number (Long) foreign key to tblSong.SongID
SortOrder Number (Long) order of song in book.

Create a form bound to tblBook.
Within that form, place a subform bound to tblBookSong.
The user can add, remove, and reorder the songs in the book.

Now, to print out the song book, you can use a query that combines the 3
tables. No temp tables needed. No reassignment of record source for the
report.

It is possible to programmatically reassign the RecordSource of a report in
Report_Open only, but the approach above will be much simpler and faster.