How do I e-mail just the current record in Access using a form.

[lazyj]

I want to e-mail just a single record as a form or a report but it appears
Access does not support this. I can e-mail every record in a data base, there
are potentially thousands, but not just the current record. Can anyone
please advise me.

 

[Albert D. Kallal]

The following steps are needed to accomplish this goal.

1) We'll build a report that lays out the information from your form as
needed.

2) will set up coach said only the current lacquered were viewing to that
report

3) once we've accomplished one and two, then we'll use the command
sendobject the results of the 1 + 2 steps...


OK assuming you've built a report that's based on the same table as the
form, here's how we send just the one current record to that report (by the
way this information is useful for printing invoices, or all kinds of things
that are based on the one form that you're viewing).

So, we go:

me.Refresh
docmd.OpenReprot "ReportName",acViewpreview,,"id = " & me!ID

You'll have to replace the "id" with the actual name of the primary key
field you're using in your table. (the default field name is usually id, but
simply use whatever you used for your unique identifier for each record).

so the above opens reporting to preview mode, the next thing we would do is
e-mail it, the following code does all the steps we should need

Dim strReport As String

strReport = "Name of my Report goes here"
Me.Refresh
DoCmd.OpenReport strReport, acViewPreview, , "id = " & Me!ID
Reports(strReport).Visible = False

DoCmd.SendObject acSendReport, strReport, acFormatRTF, , , , "For your
information"
DoCmd.Close acReport, strReport

if you look closely at the above, if you have a field on the current form
that has the e-mail destination for the particular customer whatever, then
the above could be modified to actually insert the e-mail address for you
and you would not even have to type that.....

eg:

DoCmd.SendObject acSendReport, strReport, acFormatRTF,"(e-mail address removed)" , , ,
"For your informaton"

In the above I've harded coded the "to" name for email in this eample, but
you could replace that with the name of a field name in the form....

 

[lazyj]

I'm sorry, I do not understand. Where do I do these things and how?

 

[Albert D. Kallal]

Lets do step #1 first:

1) We'll build a report that lays out the information from your form as
needed.

Ok, so, for this step you need to learn the skills and enough of ms-access
to build a report. Have you ever built a report before an access, it's hard
to give you instructions if I don't know what your skill levels are and how
much experience you have with MS access.

So our first step in this whole process is to build the report. You can use
the wizard to build this report, or manually layout the report the way you
want. It's really up to you to use the wizard or manually build and layout
the report yourself. You will base this report on the same table as the form
is based on. Keep in mind during testing and viewing and playing and laying
out this report it will display all records from that table. However later
on in this instructions and learning curve I will explain to you how to
restrict to report to the one current record that were viewing in the
current form. however before we do that we must build this report and laid
out the way you want it to look...


So, are you able to build a report in MS access?

 

[lazyj]

Yes, I have created several reports and as you say, they show all the records
in the database. And thank you for taking the time. It is most appreciated.

 

[Albert D. Kallal]

Yes, I have created several reports and as you say, they show all the
records
in the database. And thank you for taking the time. It is most
appreciated.

Great!

Ok, we done step #1, now lets do step two (by the way, my apologies, looking
at the original text of step #2, it is a mess (I am using voice dictation
software --- it often munges things...).

Ok, now, lets do step #2. Step #2 is:

2) set things so that when the report is launched, it will show ONLY the
current reocrd we are
viewing in the form.

This step is more difficult because we are going to place a button
on your form. I'm going to suggest
that you do not use the wizards here. So,
we now need to open up your form in design mode. You then need to select a
button from the toolbox and place it on your form. You should also consider
disabling or turning off the wizards just for this one task, and then turn
the wizards back on when done.

You now want to right click on that button on the form in design mode and
then
select build event.

At this point in time you should get three options:

Macro Builder
Expression Builder
Code Builder

We want to choose the 3rd option.

I also have a series of screen shots here that shows you how to place a
button on a form and then bring up the event designer. You can find a series
of screen shots here:

http://www.members.shaw.ca/AlbertKallal/wordmerge/Details.htm

please scroll down to the following part in the above article:

"Adding the command button to your Form"

Notice on the above instructions I also mention how to turn off the wizards.

So either of the above the two approaches should work for you. Once again
I'm not sure of your skill level and I not sure where I lost you in my
original post. However at this point we are going to place a buttion on the
form (when in design mode). When you finally get into the code editor as
shown in those screen shots, then we're ready to type in some programming
code behind that button. The code that's going to be placed behind this
button on the form is going to be as follows:

dim strReport as string

strReport = "Name of you cool report goes here"
me.refresh
docmd.OpenReport strReport,acViewPreview,,"id = " & me!id

The above code will have to be modifed to your needs. Lets take this step by
step:

strReport = "Name of you cool report goes here"

In the above line of code, you're going to have to replace the text "name of
your cool... with the actual name of the report you created. Once again I
can't read your mind! So, you'll have to modify the above line of code to
reflect the actual name of the report that you used.

The next line of code does not need to be changed:
me.Refresh

The above line of code simply forces the current forms data to be written to
disk (the table). We need to do this so that when we launch a report it
shows the latest updated data. Keep in mind the report pulls the data from
the actual table, not from your form. so unless we force the forms data to
be saved, the report will not reflect any changes you've just made to the
form.

Ok, the next line of code that will have to be modified to work on your
system is:

docmd.OpenReport strReport,acViewPreview,,"id = " & me!id

In the above you're going to have to replace "id" with the actual name of
the unique identifier for each record in your table. This unique identifier
is typically Referred to as a primary key in database lingo terms. So for
example if your primary key in your table was contactID, then you'd replace
the above line of code with:

docmd.OpenReport strReport,acViewPreview,,"Contactid = " & me!Contactid

OK once you've modifed the above lines of code, you can now close the form
and save it. If you open the form in view mode, you should be able to press
this button you've just added and it should launch a report with only the
ONE record that you're viewing. At this point you could even test printing
out the report to the printer. Now, close the report, and try moving to a
few different records and pressing this button and see if it launches a
report to the one record you are viewing.

So the above is step number two, and we have to get this part working before
we can advance the next step number three in which we attempt to e-mail the
report.

So let's get the above working, once you get the above process up and
running, then we can move forward again.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)






as I mentioned since you're unable to follow my original structure as it's
hard to tell how far we can get along in this process



, and then get into the code builder





As mentioned it somewhat difficult to know the skil level of your abilities
here. Im not really sure at what point in mto my original structure is that
what you got lost, , since you could not follow my original and structure as
I'm not sure if you've ever designed are built forms and design mode, I
thought the structure quite clear and simple, but

so from that




to that
report

 

[lazyj]

Hi. Thanks again for your help. My experience with databases is as a user but
I am attempting to set up a number of databases for my employer that make use
of forms that have to be filled out, then printed for a hardcopy file and
e-mailed to various managers as appropriate. I have never done any form of
programming but I can create tables, forms and reports as I am, as a user,
reasonably computer literate. What you are demonstrating to me is just how
little I do know, but I am more than willing to learn. I have the necessary
form, I have followed your instructions, I hope to the letter, now I am
creating the report. I did already have a report created but for some reason
it wouldn't launch with your code so I am creating a new report.

 

[Albert D. Kallal]

Idid already have a report created but for some reason
it wouldn't launch with your code so I am creating a new report.

Hum, it should work. The code is:

dim strReport as string

strReport = "Name of you cool report goes here"
me.refresh
docmd.OpenReport strReport,acViewPreview,,"id = " & me!id

The trick is to repalce the above parts to the names of your report you
used. In this case you need to set strReprot to the name of your reprot.
Lets say rptCustomer.

Hence you would have:

dim strReport as string

strReport = "rptCustomer"
me.refresh
docmd.OpenReport strReport,acViewPreview,,"id = " & me!id

You also have to change "id" to the PK field as explained in my post.

 

[lazyj]

Hello. Just for the record, I am using access 2007. The report I have created
is called "HANDOVER" AND THE PRIMARY KEY IS HANDOVERid. The command box is
"command 21"
When I open the visual basic box there are two option boxes across the top.
The left one says "command21", the right one says, "click".
In the code I write:
dim strReport as string
strReport = "rpthandover"
me.refresh
docmd.openReport strReport,acViewPreview,,"id =" & me!id

I save this, then close it, then open the Form "HANDOVERS". I click in
command button "command21" and get an error message box telling me I have an
"ambiguous name" which I believe relates to the name of the command box.

Hope I'm not being totally stupid and missing something basic here.

 

[Albert D. Kallal]

Hello. Just for the record, I am using access 2007. The report I have
created
is called "HANDOVER" AND THE PRIMARY KEY IS HANDOVERid. The command box is
"command 21"
When I open the visual basic box there are two option boxes across the
top.

You must open up the vb editor FROM that control you JUST placed on the
form. (you can NOT just open up the vb editor...you MUST do this from the
control on your form).

I gave a series of screen shots here as to how to do this:
http://www.members.shaw.ca/AlbertKallal/wordmerge/Details.htm

Scroll down to the part titled:

Adding the command button to your Form

Start reading from the above text. It shows how you place a button on your
form and put BEHIND that button. If you don't follow these instructions,
then
your code will NOT be attached to the particular button.

So, you must follow the above. Delete your existing command button, and
place a new one on the form as per above instructions.

you code will look like:

dim strReport as string

strReport = "rpthandover"

so which is the name of your reprot? In the above you have "rptHandover",
but you are telling me your reprot name is called HANDOVER

Remember, I said replace that text with the EXACT name of your report.
Futhermore, things like additional spaces in the name of things is
important.

Your primary key is HANDOVERid, then your code will be:


dim strReport as string
strReport = "HANDOVER"
me.refresh
docmd.openReport strReport,acViewPreview,,"HANDOVERid =" & me!HANDOVERid

Once again, please read that web page as to how to add code behind a button.

And, keep in mind you must replace things EXACTLY as stated. So, I had:

strReport = "Name of you cool report goes here"

So, in the above, you are to replace the name of your report inside of "Name
of..."

Also, please open up your table in design mode and DOUBLE check the name of
the primary key. Are you sure it is HANDOVERid ? Also, while we are at this,
what is the type of field HANDOVERid is when you look at it in table design
mode (ie: is it text, number etc). ?

 

[lazyj]

Hi. Perhaps we're making progress and thanks again. I have ensured that in
the table, handovers, the primary key is HANDOVERid.
In the box to the lef tof the main screen is a list box titled "Unassigned
Objects".
In that column I have three titles "HANDOVERS", which is the name of the
table, "HANDOVERS" which is the name of the form, and "HANDOVER", which is
the name of the report.

In form view, I single click on Command22, now nothing happens at all. The
code I write is as follows:-
dim strReport as string
strReport = "HANDOVER"
me.Refresh
docmd.OpenReport strReport,acViewPreview,, "HANDOVERid ="&me!HANDOVERid

 

[Albert D. Kallal]

Hi. Perhaps we're making progress and thanks again. I have ensured that in
the table, handovers,

In the box to the lef tof the main screen is a list box titled "Unassigned
Objects".
In that column I have three titles "HANDOVERS", which is the name of the
table, "HANDOVERS" which is the name of the form, and "HANDOVER", which is
the name of the report.

Good, when you click on and launch the report handover, does it run ok? This
report should work just fine when you click on it. If it works ok, then lets
close the report....

In form view, I single click on Command22, now nothing happens at all. The
code I write is as follows:-
dim strReport as string
strReport = "HANDOVER"
me.Refresh
docmd.OpenReport strReport,acViewPreview,, "HANDOVERid ="&me!HANDOVERid

As I said, if you made mistakes, you should delete the command button, and
start over. Also IT IS VERY important that each time to make modifications
to that code you go through the steps outlined in that series of screen
shots. In other words, you are entering the code editor from the contorls
properity sheet, and selecting the "on click" event in that properity sheet?
Correct?

Hum, the above is confusing and does not make sense. Remember, follow that
series of screen shots I gave you as to how this is done. If you don't
follow those screens, this will not work for you.

docmd.OpenReport strReport,acViewPreview,, "HANDOVERid ="&me!HANDOVERid

The above looks ok, but there should be some spaces such as:

docmd.OpenReport strReport,acViewPreview,, "HANDOVERid = " & me!HANDOVERid

Also, after you typed in all of the above code, then use the menu bar and go
debug->compile whill still in the code editor...does it compile??

 

[Albert D. Kallal]

By the way, perhaps you "security" settings are not allow code to run.

I would check your macro security settings..

office button->access options->trust center

on he right side click on the button Trust Center Settings

now, on the left nav pane, select "macro settings"

then, choose the bottom option:

[x] Enable all macors....

After you do the above, you have to exit, and re-enter the application. This
might explain why your button code is not working...

You should perhaps make a "test" button that ONLY displays the message
hello.

Try the folwling code behind a button:

MsgBox "hello"

Does the above code work when placed behind a "test" button you place on the
form?

So, try the macro setting, and also try a "test" button with ONE LINE of
code as above. lets get a simple button working, then we can go back to
attempting to get that more complex code to run....

 

[lazyj]

You are a very patient man and thanks again. I did use properties and
selected "Click". I deleted every attempt at compiling the code prior to
"Command 22" and I very carefully followed your code and i'm sure I put all
the spaces in the right place. I am about to follow your advice on ensuring
the right macro and VB are selected and will get back to you as soon as I can.

 

[lazyj]

Hi I knew the answer would be simple. I set the macro settings as you
suggested and bingo, it all worked. I did the MsgBox thing and that worked
perfectly also.

My report is launched when I click on Command22, as it should. I get the
report with all the field names but not the information contained in the
report. I assume this is for the next stage?

You are a very patient man and thanks again. I did use properties and
selected "Click". I deleted every attempt at compiling the code prior to
"Command 22" and I very carefully followed your code and i'm sure I put all
the spaces in the right place. I am about to follow your advice on ensuring
the right macro and VB are selected and will get back to you as soon as I can.

By the way, perhaps you "security" settings are not allow code to run.

I would check your macro security settings..

office button->access options->trust center

on he right side click on the button Trust Center Settings

now, on the left nav pane, select "macro settings"

then, choose the bottom option:

[x] Enable all macors....

After you do the above, you have to exit, and re-enter the application. This
might explain why your button code is not working...

You should perhaps make a "test" button that ONLY displays the message
hello.

Try the folwling code behind a button:

MsgBox "hello"

Does the above code work when placed behind a "test" button you place on the
form?

So, try the macro setting, and also try a "test" button with ONE LINE of
code as above. lets get a simple button working, then we can go back to
attempting to get that more complex code to run....

 

[Albert D. Kallal]

Hi I knew the answer would be simple. I set the macro settings as you
suggested and bingo, it all worked. I did the MsgBox thing and that worked
perfectly also.

My report is launched when I click on Command22, as it should. I get the
report with all the field names but not the information contained in the
report. I assume this is for the next stage?

great...we making progress!!

OK, first things first.

We need to address the issues to why information's not displaying in that
report. So, lets put aside our button + form. lets just simply launch the
report directly from access (the reports grouping).

When you launch the report, does information display on that report from the
table? Of course it'll display all records from the whole table as you page
along in this report. We just trying to ensure that this report is working.
You can also use a wizard to build this report if you wish.

So, once the report is working, then we can go back to our code:

Our code was/is:

dim strReport as string
strReport = "HANDOVER"
me.Refresh
docmd.OpenReport strReport,acViewPreview,, "HANDOVERid ="&me!HANDOVERid

the things to check:

docmd.OpenReport strReport,acViewPreview,, "HANDOVERid ="&me!HANDOVERid

"HANDOVERid ="&me!HANDOVERid

there should be some spaces here, like:


"HANDOVERid = " & me!HANDOVERid

Next, take a look at the field list for that report, is there a field
called:

HANDOVERid

The spelling MUST BE EXACT. One extra space, or any change in the name will
prevent this from working.

Check the actual above names from the table design mode. Also, when you
speak of HANDOVERid, is this a text field, or a number field as defined in
the table design view? (you have to bring up that table that the form (and
report) is based on, and look at this field. check the "type" of field, and
check the name....

If the HANDOVERid is text type field, then we need:

"HANDOVERid = '" & me!HANDOVERid & "'"

If the HANDOVERid is number type field, then we need:


"HANDOVERid = " & me!HANDOVERid

You also might consider checking the report in design mode to make sure the
above field is in the report, and you also want a check your form in design
mode and make sure the above field also exists. In both cases were talking
about a field called HANDOVERid

 

[lazyj]

Whoopee. The report was launched complete with data. I examined my code
considering your insistance on being exact. I realsied I had used an extra
quptation mark after "HANDOVERid". I deleted that and the report launched
perfectly. I tried to print it but it wouldn't do that. Instead it printed
all the form records but i am now getting what you said I would get.

 

[lazyj]

Mr Kalla, I cannot thank you enough, both for your help and your patience.
Today, using your code, I was able to e-mail a single record. It opened in
Word perfectly. I can print the orginal form, then click on command22 and
e-mail a report. It is just what I was trying to do from the beginning. I
hope that someday I can provide help for someone else, just as you have
helped me. Thanks again. The sad thing is, why couldn't Microsoft have
thought of doing this very fundemental task and made it easy for all of us.