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 

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.

-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Mary Beth" wrote:

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

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

"Jerry Whittle" wrote:

> 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.
> 
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "Mary Beth" wrote:
> 
> > 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 
> > 

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" wrote:

> This works great (I got both 1 and 2 to work)!!  Thank you for your help!
> 
> "Jerry Whittle" wrote:
> 
> > 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.
> > 
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "Mary Beth" wrote:
> > 
> > > 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 
> > > 

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?

"Mary Beth" wrote:

> 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" wrote:
> 
> > This works great (I got both 1 and 2 to work)!!  Thank you for your help!
> > 
> > "Jerry Whittle" wrote:
> > 
> > > 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.
> > > 
> > > -- 
> > > Jerry Whittle, Microsoft Access MVP 
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > 
> > > 
> > > "Mary Beth" wrote:
> > > 
> > > > 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 
> > > >