Request for Assistance naming a file

[SupCom]

Greetings,

First time poster so I'll try to do my best.

I'm currently working with Access 2007 building a way to create PODs to clients. I have most everything laid out, one problem I can't seem to get around is naming the file the SOW #. Below is the code:

Function Run_all_PODs_01()

Dim myPath As String
Dim name As Object


    DoCmd.OpenQuery "Q5 SOW bill requested data points all", acViewNormal, acEdit
    DoCmd.OpenQuery "Q5 SOW bill requested All 01", acViewNormal, acEdit
    DoCmd.OpenQuery "Q loop 02", acViewNormal, acEdit
    DoCmd.OpenQuery "Q5 SOW bill requested 2", acViewNormal, acEdit
    DoCmd.OpenQuery "Q5 SOW bill requested 3", acViewNormal, acEdit
    DoCmd.OpenQuery "Q5 SOW bill requested 4 all", acViewNormal, acEdit
    DoCmd.OpenQuery "Q5 SOW bill requested 5", acViewNormal, acEdit
    DoCmd.OpenQuery "Q5 SOW bill requested 6", acViewNormal, acEdit
    
    DoCmd.OpenTable "SOW bill requested data points", acViewNormal
    
    name = Table![SOW bill requested data points]![SOW #] + ".pdf"

    myPath = "C:\Users\xxx\Desktop\"
   
    DoCmd.OutputTo acOutputReport, "Report1", "PDFFormat(*.pdf)", myPath + name, False, "", , acExportQualityPrint
  
   End Function

I can't seem to get the name to equal the SOW number.

The table that I'm trying to reference is "SOW bill requested data points" it is a basic table one row with a column header as SOW #. I'm not sure how to tell it name the file that data point in the column.

Any help would be appreciated.

 

[tfurnivall]

Hi SupCom

Without wishing to cause any offence, may I suggest that you not use abbreiviations like POD and SOW in a problem statement like this? My mind does not work the same as yours, and I had images of you throwing female pigs at a client.....

However, if I am interpreting what you want to do correctly, it's something like this.

I have a report that am creating and I need to use a piece of data from the database as part of the external filename of the report. (Oh, and I'm having trouble to figure out how to do this).

This being the case, there are some reasonable assumptions:

1) The report runs fine when you don't try to save it to an external file.
2) Everything except naming it to that pesky old external file is fine.

Given that, the first thing I'd suggest is that you create the external filename somewhere else, and pass it in as a parameter. Kind of like this:

Function Run_all_PODs_01(FileName as string)

Ahah! You say, but I'm trying to run ALL the PODs at the same time, and they can't all have the same filename.

True, sez I, but you are only trying to get one filename (for Report 1).

The point of that little mental exercise is that you need to separate the work of creating the report from the work of creating a filename.

I'd question your use of DoCmd.OpenTable. The online help for this says:
The OpenTable method carries out the OpenTable action in Visual Basic.


Well when I open a table I certainly don't expect to be able to return a value directly. To do that I'd use a recordset and a SELECT statement
(This is why getting this value is best done in a separate procedure!)


Try something like this

Function GetReportName() As String

Dim rs As ADODB.Recordset
Dim SQLString As String
Dim ResultName As String
Dim ReportPath as String

SQLString = "SELECT [SOW #] from [SOW bill requested data points];"

Set rs = New ADODB.Recordset
rs.Open SQLString, _
        CurrentProject.Connection, _
        adOpenKeyset, _
        adLockOptimistic
        
ResultName = rs.Fields(0)
ReportPath="C:\User\xxx\Desktop\"
GetReportName = ReportPath & ResultName & ".pdf"

End Function

Then you can simply use GetReportName as the reportname, without having to confuse the issue of creating the reports with the issue of naming the reports.

HTH

Tony

PS I'm certain that there will be many wiser than I who will give the requisite 50 lashes with a limp noodle for allowing TableNames and FieldNames to have embedded spaces !

Bad idea!

 

[redalert]

I can surely be forgiven for thinking that I have joined the wrong forum with the abuse by flying pigs and lashes with a limp noodles being suggested!!

I support the need to avoid using spaces in Access object names, field names, control names etc.

If the name of the filename to produce is contained within a one record table then a DLOOKUP will return the value that you want.

strFileName = DLOOKUP("[SOW #]","[SOW bill requested data points]")

I am not sure why you are opening all of the queries before you run the report.

What difference is there in the query criteria for each of the queries as maybe you can run a loop and create the querydef and run the report in the loop.

What is the format of the filename and how will it differ for each POD / Client?

 

[SupCom]

Greetings,

Thank you both for your quick responses.

tfurnivall to answer your question about the docmd, I created the macro in the access gui first then transfered the code to VBA, which might be why it looks odd, but it works, which at this point is what I need.

I have read over your instructions and though its a little over my head I'll try to piece together on the side for another project what you responded with.

Redalert to answer your question, the queries that run before prep the data to be extracted, placed in format and grouped into a report. I will need to loop through them a few times but needed to name the file first. Thank you for the dlookup, I had tried it earlier but I was adding the last criteria, didn't know if there is one, it will bring back the first, almost like a vlookup. I placed that into my query and it worked, also I see now why spaces are probably not the greatest thing when programing.

Thanks again.

 

[CJ_London]

I'd also like to point out that

Dim name As Object

is a bad idea, Name is a reserved word and may well be contributing to your problems