Form vs Report vs Query Question???

[JNana]

What is the best way to handle the following:

I have a query that needs to be sent via email. The emailed FORM transmits
via email as desired with the exception of:
1. I need to total some of the fields.
Can this be done?
2. I need to lock some of the fields so that there is no data entry
allowed on the transmitted form.
Can this be done?
3. I need to have background color on the unlocked fields.
Can this be done?

The emailed REPORT can handle all of the above with the exception of
emailing, which of course will not work for me since the format is lost.

The emailed QUERY emails in good format and is much easier to handle.
However, I have the same questions as listed under the form above. My
query
has 50 fields - and yes, it must be on one query without any subqueries so
that when I email this it transmits as an excel worksheet.

Now, my next problem is how do I return the info from the emailed (back to
me) worksheet to access?

Thanks in advance,
JR

 

[Ron2005]

In all of the send aspects, the data shown is now independent of the
tables

Your best bet if you are trying to do all those things is to export the
query to a spreadsheet and then from access go in and update the
spreadsheet with the extra stuf you want including locking etc. In
some cases here we have created a dummy master spreadsheet with much of
the formating etc done ahead of time and copy that to a working version
which we then export to and then go in and play with it from access,
including getting rid of some of the exported tabs.

Getting it back in will require planning, especially if they are being
sent to many people. But basically you will have to link and/or import
the returned spreadsheet (after it has been saved with a name you
recoginize in a location you can get to). After it is imported then you
need to use that imported table to update what you want in your tables.

Not a fun co-ordination job of have I gotten all of the sheets back?
has other data changed in the access file that I do not want
overwritten by the spreadsheet.? Not a lot of fun.

 

[JNana]

Thank you for your reply. May I ask a question?

Having never done this, I am not sure what you mean. I am assuming you mean
to export the query to excel via tools, which I understand. "Then from
access go in and update the spreadsheet with the extra stuff you want
including locking, etc." -- how do I do this? It sounds as if you are
working in excel, but updating from access. I have never done this. It
does sound as if you are doing what I am hoping to accomplish.

And, yes, the spreadsheet is going to many people, not necessarily at the
same time though. I believe that you are saying once I get the spreadsheet
back, I need to import/return this info to a table specifically designed for
this spreadsheet, and then update my table with this info?? What if I am
using multiple tables?

Thank you for your assistance.
JR

 

[Ron2005]

As to your questions:

via tools? No but instead using code similar to what I will post
at the end of this. Once you open a spreadsheet in Excel from Access
you can do about 90% of the things that you can do in Excel itself.
What I usually do is go step by step. Export the file into the
spreadsheet. the separate from Access open Excel and create a macro and
do all of the operations that I want access to do. Then edit that
macro, select all and copy it into a function inside an access module.

From the example you can see that in most cases the instructions end up

being the same but simply have the es (in my case) in front of each.
That seems to work better for me than using the "with" function that is
available.

Some of the excell functions/actions have not worked for me this way,
but you can also have a macro in that base spreadsheet that is copied
and you can execute the macro from within access, so that can get
around that part. My range in the copy has J600 as the end because I
know that I will never have near 600 entries being exported. The
..visible property says whether or not you can see excell do it. I have
found a couple of excell functions that need a yes/no reply need to be
visible to run. I have not been able to find a workaround for some of
them, which simply means I can't find the workaround yet, not that it
is not there. Sometimes in testing I will make visible true just so I
can watch it work and see where I may be making a mistake.

The example has both the excell part and the creation of an email with
attachment.

2) about getting the data back in. Yes that is how I would handle, If
you extracted the data from 5 tables, then you will need to be able to
turn around and run as many update queries as required to turn around
and overlay fields in the tables that you want to update with the new
information. Obviously the key will be that you will need to have in
that spreadsheet (now a returing table) any information you will need
to properly identify which record in which table needs to be updated.
This may entail record keys that could be hidden/locked to the excell
user but would be necessary for you to properly identify which record
to update.
Deleting lines out of the spreadsheet will not obviously delete lines
out of the tables, you will have to figure out some other flag system
to do that if that is going to be allowed.

3) The key in all of this is play around with doing it in macros and
then expanding on the code, once you begin to feel comfortable with
what you are doing. Until you get it all down pat, you obviously need
to backup your tables and starting spreadsheet so that you do not need
to restart from 0 or recreate tables.

=====================================================

VBA.FileCopy MasterDir & "Time Analysis Ding Master.xls", ReportDir
& ReportFileName

ReportFileName = ReportDir & ReportFileName

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Query - All Employee Ding Only", _
Filename:=ReportFileName, _
HasFieldNames:=True, _
Range:="ExportData"


Set es = CreateObject("Excel.Application")

es.Visible = False

es.Workbooks.Open Filename:=ReportFileName

es.Sheets("Master Analysis Tab").Select
es.Range("B2:B2").Select
es.ActiveCell.FormulaR1C1 = Now()
es.Range("B4:B4").Select
es.ActiveCell.FormulaR1C1 = strtdate
es.Range("D44").Select
es.ActiveCell.FormulaR1C1 = EndDate
es.Range("F4:F4").Select
es.ActiveCell.FormulaR1C1 = NumDings

es.Sheets("ExportData").Select
es.Range("A2:J600").Select
es.Selection.Copy
es.Sheets("Master Analysis Tab").Select
es.Range("A7").Select
es.ActiveSheet.Paste


es.Range("A1:A1").Select
es.ActiveWorkbook.Save
es.ActiveWorkbook.Close (False)

es.Application.Quit

If Forms![HiddenKey]![HKCreateEmail] = True Then
Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)

m.To = (e-mail address removed)

m.Subject = "Time Analysis report as of " & Date
m.bodyformat = 2
m.htmlbody = "<body><br><br>" & _
"<b> Date: " & Date & " </b><br><br></body>"

' if you don't want the html/rtf format then drop the bodyformat
and load m.body instead.

m.attachments.Add ReportFileName


m.display

End If

 

[JNana]

Ron - thank you for the time and effort that you took explaining this to me.
I am on a mission to make this work. Thanks to you.

As to your questions:

via tools? No but instead using code similar to what I will post
at the end of this. Once you open a spreadsheet in Excel from Access
you can do about 90% of the things that you can do in Excel itself.
What I usually do is go step by step. Export the file into the
spreadsheet. the separate from Access open Excel and create a macro and
do all of the operations that I want access to do. Then edit that
macro, select all and copy it into a function inside an access module.

From the example you can see that in most cases the instructions end up

being the same but simply have the es (in my case) in front of each.
That seems to work better for me than using the "with" function that is
available.

Some of the excell functions/actions have not worked for me this way,
but you can also have a macro in that base spreadsheet that is copied
and you can execute the macro from within access, so that can get
around that part. My range in the copy has J600 as the end because I
know that I will never have near 600 entries being exported. The
.visible property says whether or not you can see excell do it. I have
found a couple of excell functions that need a yes/no reply need to be
visible to run. I have not been able to find a workaround for some of
them, which simply means I can't find the workaround yet, not that it
is not there. Sometimes in testing I will make visible true just so I
can watch it work and see where I may be making a mistake.

The example has both the excell part and the creation of an email with
attachment.

2) about getting the data back in. Yes that is how I would handle, If
you extracted the data from 5 tables, then you will need to be able to
turn around and run as many update queries as required to turn around
and overlay fields in the tables that you want to update with the new
information. Obviously the key will be that you will need to have in
that spreadsheet (now a returing table) any information you will need
to properly identify which record in which table needs to be updated.
This may entail record keys that could be hidden/locked to the excell
user but would be necessary for you to properly identify which record
to update.
Deleting lines out of the spreadsheet will not obviously delete lines
out of the tables, you will have to figure out some other flag system
to do that if that is going to be allowed.

3) The key in all of this is play around with doing it in macros and
then expanding on the code, once you begin to feel comfortable with
what you are doing. Until you get it all down pat, you obviously need
to backup your tables and starting spreadsheet so that you do not need
to restart from 0 or recreate tables.

=====================================================

VBA.FileCopy MasterDir & "Time Analysis Ding Master.xls", ReportDir
& ReportFileName

ReportFileName = ReportDir & ReportFileName

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="Query - All Employee Ding Only", _
Filename:=ReportFileName, _
HasFieldNames:=True, _
Range:="ExportData"


Set es = CreateObject("Excel.Application")

es.Visible = False

es.Workbooks.Open Filename:=ReportFileName

es.Sheets("Master Analysis Tab").Select
es.Range("B2:B2").Select
es.ActiveCell.FormulaR1C1 = Now()
es.Range("B4:B4").Select
es.ActiveCell.FormulaR1C1 = strtdate
es.Range("D44").Select
es.ActiveCell.FormulaR1C1 = EndDate
es.Range("F4:F4").Select
es.ActiveCell.FormulaR1C1 = NumDings

es.Sheets("ExportData").Select
es.Range("A2:J600").Select
es.Selection.Copy
es.Sheets("Master Analysis Tab").Select
es.Range("A7").Select
es.ActiveSheet.Paste


es.Range("A1:A1").Select
es.ActiveWorkbook.Save
es.ActiveWorkbook.Close (False)

es.Application.Quit

If Forms![HiddenKey]![HKCreateEmail] = True Then
Set o = CreateObject("Outlook.Application")
Set m = o.CreateItem(0)

m.To = (e-mail address removed)

m.Subject = "Time Analysis report as of " & Date
m.bodyformat = 2
m.htmlbody = "<body><br><br>" & _
"<b> Date: " & Date & " </b><br><br></body>"

' if you don't want the html/rtf format then drop the bodyformat
and load m.body instead.

m.attachments.Add ReportFileName


m.display

End If