Need specific record in report

[Mary Beth]

I have inherited a Nurse Assessment database and am trying to add the feature
of attaching a report to an email when the Assesment date field has been
filled in. The report must contain the specific person's record data from
the form which the Assessment date was just filled in.

The form is based off a table called 'tblAuthorization'
The report is based off of a query with 'tblConsumer' and
'tblAuthorization'.
I have the email function set up in a macro.

I have two problems I need help with and please, don't make this too
complicated, I have never done this before.

1. The person who created the database put a 'pick the date from a
calendar' function, so the user does not enter the cursor into the control
field. Because of this, the macro does not run on any event. I need to know
how I can trigger the macro to run when the date is filled in.

2. In testing, if I enter the date manually, the macro will be triggered to
run. The macro runs great, the email pops up to edit the message mode, but
the record is incorrect in the report. I tried putting -
Forms!frmAuthorization!ConsumerID - in the ConsumerID field in the query and
it pops up a box wanting the ConsumerID. I need the specific consumer data.

How can I accomplish both of these things?

Thanks,

Mary Beth

 

[Jerry Whittle]

1. Run the macro in the 'pick the date from a calendar' function. Make one of
the last lines of the function read something like below. That way the date
should be in the text box before the macro runs.

DoCmd.RunMacro "Print Sales"

2. I'm assuming that you put this in the criteria of the query. Is the
frmAuthorization still open? It needs to be for this to work.

 

[Mary Beth]

This works great (I got both 1 and 2 to work)!! Thank you for your help!

 

[Mary Beth]

I am testing this today, and am having problems again.

1. For the calendar & email. I put the RunMacro event in the On Exit event
of the calendar command button. Also, I put -
Forms!frmAuthorization!ConsumerID - in the ConsumerID field of the query
which the report is based. When I Exit the command button after putting in
my date, a box pops up with Forms!frmAuthorization!ConsumerID on it. What is
wrong? How do I fix this?

2. Also, I put a lock/unlock button on the database using code to lock the
form, and set AllowAdditions, etc. to No, but I am still able to change the
date using the calendar button. How do I prevent this from happening?

 

[Mary Beth]

Scratch the part about the box popping up. It's not doing it anymore.

I've noticed that the date in the table does not update to the date entered
into the field. I tried to Requery in VBA and also in the macro and the
wrong date is in the report. Any ideas?