Accesss/Excel Record Exchange

[JeepsR4Mud]

Access/Excel Record Exchange
This post is my first on this BB, so please forgive any etiquette errors.

I work for a government agency. I have an Access database for one of the weekly reports that each site is required to provide. If Outlook allowed sites to send Access databases, there wouldn't be a problem, but...

As near as I can figure out, the best way to send the record is to export it to Excel and then send it to a central site (me), via a file attachment. I can then put the data back into Access.

However, some of these people are not very computer literate, so I need to streamline the process as much as possible.

Is there a way to use a button to

1. Export the last record to Excel
2. Save the Excel file with the name: Weekly Report - 00-00-2002

Would be great if one button generated the e-mail and the record, but that would be hoping for real miracles.

I'm by no means an expert in Access programming. I do manage to function at a level slightly above what is offered, and am really great at using what is offered to achieve my goals. Although I readily admit that I probably don't achieve the desired outcome in the most efficient way, but...

ANY help would be greatly appreciated. Any other ideas also welcomed!

Thanks so much.

Gayle Ann

 

[yellow]

To save a query/table as an Excel file:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Your Query Or Table Name", "Path to be saved and filename", True

To send as an email lookup:

Docmd.SendObject

This code is in this forum hundreds of times, people ask the same question hourly! It is also in Access' help.

To export the last record from a table would depend on what information is in your table. If you took the last record and the table had been sorted, then the last record may not be the newest. Therefore you want to export the newest record? To do this you need to have a field like an autonumber of date field. then do a query on the table, select the fields you want to export and look up MAX in HELP.

If you put all this code together under a command button it can all happen at the same time.

Hope this helps...

 

[Krysti]

Hi Gayle Ann,

You could create a query that shows only the last record:

If you have an ID field that is an autonumber, you could sort it by descending order. Go into the properties of the query and set the "Top Values" to 1. This will return the last record entered.

Then you could use the SendObject macro (or Docmd.SendObject code) to e-mail that query to you in the .xls format. (The file name will be the name of the query).

Hope this helps!

Christy

 

[JeepsR4Mud]

Okay, what if I have a linked table?

For example, one of the question is Type of Drugs COnfiscated, and Location of Confiscation.

What I see is a table with 3 fields:

Facility
Date
Type of Drug
Confiscation Location

Facility would be the primary key, with a relationship to the main table, where each records corresponds to the facility's weekly report.

As far as data entry, the above is simply a subform.

But, how do I export subforms to Excel and e-mail them, all in one step?

I also have a similar dilema, adding another subform for weapons confiscations.

If I could specify each table as a separate worksheet in the Excell file, it would work, but how can I do that?

It would be part of the codes you gave be above.

Thanks so much!

Gayle Ann