Repost -- no replies :-(

[Lawrence]

I am building a relatively simple application and have made some progress but I'm stuck on the reports. Hope you can help me out! I have two questions

1) The db I am building will track billing and progress notes for my staff. Each note will correspond to one billing entry and will be about one client (of many) and written by one staff (of many). Each day, many notes may be written about a given client; each note may be authored by the same or by different staff. So client X may have 4 notes written by John, 6 by Susan, 1 by Elsa, etc. I have tables: Staff, Client, Note (which contains all the data needed for the billing report, so I don't need a separate billing table). So far so good.

Now I need to design a report to print all the notes written about client X. The user who prints the report will need to be able to select client X -- or whatever client will be the subject of the report -- from a list of all clients. At least that is what I think I want to do so the user won't have to type in the last name, first name or other id. I just can't figure out how to do it, though I really have tried! I do have a form already (data entry for notes) that includes controls where the user selects the client by name from a list. I know this control should be useful, but... Once I figure this report out, I imagine it will be easy for me to use the same model to print a report of all billing by staff Y for date mm/dd/yy, in which the user begins by selecting the staff from a list

2) The other problem I am facing is that we need to make sure notes only get printed once (in other words, that printed reports will not contain overlapping data). So I'm thinking that maybe when the report is printed, each note can be somehow marked as "printed" so that actually my report would be filtered for all notes for client X that have not yet been printed. I have no clue how to procede with this one. Hope this makes sense. Thanks so much for any help you can provide.

 

[Jeff Boyce]

Lawrence

(see notes in-line below)

1) The db I am building will track billing and progress notes for my

staff. Each note will correspond to one billing entry and will be about one
client (of many) and written by one staff (of many). Each day, many notes
may be written about a given client; each note may be authored by the same
or by different staff. So client X may have 4 notes written by John, 6 by
Susan, 1 by Elsa, etc. I have tables: Staff, Client, Note (which contains
all the data needed for the billing report, so I don't need a separate
billing table). So far so good.

You haven't described the data structure of your "Note" table (and I wonder
if Access uses "Note" as a reserved word...hmmm?)

Now I need to design a report to print all the notes written about client

X.

Have you already built a query that returns this information for all
clients?

The user who prints the report will need to be able to select client X --

or whatever client will be the subject of the report -- from a list of all
clients. At least that is what I think I want to do so the user won't have
to type in the last name, first name or other id. I just can't figure out
how to do it, though I really have tried! I do have a form already (data
entry for notes) that includes controls where the user selects the client by
name from a list. I know this control should be useful, but...

Once you have a query that returns whatever info for all clients, you can
add a parameter for the selection criterion. The parameter can point back
to the form control which holds the selected ClientID -- the expression is
something like (actual syntax may vary):

Forms!YourFormName!YourControlName

(this assumes a combobox or listbox, with the ID field/column bound and
matching the ID used in your Note table to identify the client.)

Once I figure this report out, I imagine it will be easy for me to use the

same model to print a report of all billing by staff Y for date mm/dd/yy, in
which the user begins by selecting the staff from a list.

Exactly analogous, but using a combo or list box control with a source of
the Staff -- and you would need another query that refers to this info.

In addition, you'll need to add a command button that opens the appropriate
report, using the:

DoCmd.OpenReport ...

syntax. Check Access HELP for more info on this.

2) The other problem I am facing is that we need to make sure notes only

get printed once (in other words, that printed reports will not contain
overlapping data). So I'm thinking that maybe when the report is printed,
each note can be somehow marked as "printed" so that actually my report
would be filtered for all notes for client X that have not yet been printed.
I have no clue how to procede with this one.

Now the bad news (<g>)... There are so many ways that you could "start" a
print job and have to cancel or have it fail that you really, really DON'T
want to try to do this automatically. As a next best option, consider doing
something like:
1. fill a temp/working table with the NoteIDs to be printed
2. print them
3. in the code you use to print them (see above), but after the printing,
add a pop-up dialog form that asks if all printed successfully (e.g., the
printer might run out of ink!)
4. the dialog form can offer to update the "Printed?" field (your data
table would need a new field -- this could be as simple as a Yes/No field,
or you might want to keep the DatePrinted.)
5. if you/your user is satisfied that all selected NoteIDs have been
printed, clicking on the <Update> command button would run an update query
against the Note table (provided you've written the code to launch it and
the update query to run).