Help with populating Report from a Query

[kyuball]

Hey everyone,

I am sure there is another thread out there to this effect but I am having a hard time putting in the right search thread to find it.

I am on Access 2000 building an mdb database to keep track of attendance at monthly community meetings. Based on past attendance (criteria: presence at the last previous meeting or at least 2 meetings attended in last 3 – the number of people who meet this criteria on a month to month basis will fluctuate), those who are "regulars" have their sign-in information pre-filled into a sign in sheet which the database generates. I built the query to generate that information and it is working great but the matter is how

The problem is that if I make the report like a continuous form, it will only generate the pre-fill data and leave no blank spaces for “non-regulars” to write in their sign-in information. If I make it like a single data form, it will generate countless pages filling in only the top sign in line for each page. Ideally, I would like a two page sign-in form that has about 30 spaces altogether for sign in (we usually get 12 to 28 attendees per meeting) that fills in the top lines with pre-fills and leave the rest blank for others to write in.

Is there a way to, say, populate each sign-in line on the report with a text box that would be like:

NameField from Query Sign In, Record #1 and an IfError condition returns a blank (if say Record #17 does not exist because there were only 16 attendees that met the “regular” criteria for the upcoming month’s meeting)?

 

[plog]

I'd set up your sign-up sheet in a spreadsheet--format it however you want (2 pages, check box, line to sign, etc.) and assume that all lines will be blank. Then I'd run your query and paste the data you do have into it. That way the filled out data is where you want it and the rest of the page is formatted properly but blank.

 

[kyuball]

Really? Copying and pasting is the only way to go? I suppose I could just export it directly from Acces into Excel in which case all the pretty formatting stuff (presentation is important in all business, don't you think?) is lost on the export. I was just hoping for an easier way to get a RTF or actual report that simulates the sign in sheet we have been using all this time and have automatic data fill-in capabilities instead of me looking back over three months worth of attendance logs every month or re-formatting exported data... Oh well, if this is the only way to do it...

 

[plog]

No, not really.

I suppose I could just export it directly from Acces into Excel in which case all the pretty formatting stuff...instead of me looking back over three months worth of attendance logs every month or re-formatting exported data...

That wasn't my suggestion. My suggestion was to set up a blank, template sign-up sheet that printed however many blank lines you wanted in the format you wanted. Then everytime you wanted a hard copy you would make a copy of that template, run your query and paste the values from your query into the spreadsheet so that the data appeared where it needed to be and formatted like it was suppose to be.

That way you format once and you run your query to get your sign-up sheet data everytime you needed to produce one. Think of it as a poor man's mail merge.

 

[kyuball]

Hmmm.. I guess that would be a way to do it... I keep thinking that there has to be a better way. Maybe some way of autonumber type field that can be put into a query that gives a sequential reference to each line of data or some way of referencing the first, second, third, etc. record that appears in a query?

 

[plog]

I can see a way to do that as well. You would create a table with just one field of just numbers--however many lines you needed on the report. Then you would find a way to add a sequence number to your query that returns people's names. Then you create a LEFT JOIN query made up of the numbers table (Include all records from this table) and your data query that now has sequence numbers. That way it pulls in all your data as well as however many blank records you need.

 

[vbaInet]

Find attached two ways of doing it. rptMeetings (uses code) and rptMeetings2 (has no code).

 

[kyuball]

You're a genius vbaInet!! Worked like a charm without code!!


Thanks to both plog and vbaInet for all of your help!!

 

[vbaInet]

Happy to help!