Save Report as "field value" & ".html"

[gussy]

Hi Folks

My VB is rather rusty so would appreciate a quick bit of help...

Aim: I want to save report 'M1' as a text file, to disk, with it's file name as the value in field 'Filename' from table 'Input'. The file name would also need to be appended with the suffix ".html".

I've tried the 'output to' option in the Access Macros. It allows me to save ok but doesn't give me the option to 'build' the file name from field values.

Any help much appreciated!

As a subsidiary question - to initiate the routine I would use the 'RunCode' option in the Macro list?

Regards

Gus

 

[gussy]

Ok - I've bungled along and made progress, but would appreciate someone's assistance in just completing one part of the code I'm having problems with. The function so far:


Function M1_Report()
On Error GoTo M1_Report_Err

DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", "filename.html", True, "", 0


M1_Report_Exit:
Exit Function

M1_Report_Err:
MsgBox Error$
Resume M1_Report_Exit

End Function


The highlighted bit in red is the filename I need to insert (ie value in field name "filename" in table "Input"). I don't know the syntax to refer to a field within a table...anyone any ideas?

Thanks.

 

[pdx_man]

Check out the DLookup function:

TextFileName = DLookup("Filename", "YourTable","YourID = 'A_Value'") & "".html"
DoCmd.OutputTo acReport, "M1", "MS-DOSText(*.txt)", TextFileName, True, "", 0

 

[gussy]

ah ha, thanks pdx man - this looks just like the thing I need.

In fact, I solved my problem by using me!filename which did the trick in that particular scenario, but the DLookUp looks like the function I need for the same reason elsewhere.

Spot on, many thanks.