Code to pull record from Table.

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Hello all,
I have a piece of code that outputs a report and saves it as a .snp file by
the report name, ie rptA-Recap.snp. To give the .snp file a unique name, I
want to pull a record from the "Branch" field of the table "tlbBrDateTemp",
then add it to the name of the file. There is only one piece of data in the
table, so I just need to pull the first branch record.

I think I need something like MyBr = Branch.tlbBrDateTemp but I am not sure.

Following is my current code...

Public Function ReportDownload3()
Download3
End Function

Public Sub Download3()


Dim rpt As Access.Report
Dim path As String
Dim target As String
Dim MyUser As String
Dim MyName As String
Dim MyBr As String
Dim stDocName As String
Dim stOutputName As String

path = Environ("HOMEDRIVE") ' determine the windows drive
target = Environ("HOMEPATH") ' determine the documents and settings folder
MyUser = Environ("USERNAME")
MyBr = This is the code I don't know!!!!
MyName = Screen.ActiveReport.Name
stDocName = MyName
stOutputName = "C:\Documents and Settings\" & MyUser & "\My Documents\"
& MyName & ".snp"
DoCmd.OutputTo acReport, stDocName, acFormatSNP, stOutputName, True


End Sub



This code give the file the name "rptA-Recap.snp. I wan to attach MyBr so
it will look like "rptA-Recap018.snp"

Thanks all
 
Tim said:
Hello all,
I have a piece of code that outputs a report and saves it as a .snp file
by
the report name, ie rptA-Recap.snp. To give the .snp file a unique name,
I
want to pull a record from the "Branch" field of the table
"tlbBrDateTemp",
then add it to the name of the file. There is only one piece of data in
the
table, so I just need to pull the first branch record.

I think I need something like MyBr = Branch.tlbBrDateTemp but I am not
sure.
Click to expand...


How about:

MyBr = Nz(DLookup("Branch", "tlbBrDateTemp"), "(unknown)")

? The key part is the DLookup, but I wrapped it in an Nz() expression in
case there should happen not to be a Branch recorded in the table.

Is "tlbBrDateTemp" really the name of the table, or is that a type for
"tblBrDateTemp"? "tbl" is a common prefix for table names, whereas "tlb" is
not.
 
Dirk,
Thanks for the help.. Your piece of code worked, but I am getting a
run-time error "2103" when I try to attach MyBr to the file name. See Code
below.

Public Function ReportDownload3()
Download3
End Function

Public Sub Download3()


Dim rpt As Access.Report
Dim path As String
Dim target As String
Dim MyUser As String
Dim MyName As String
Dim MyBr As String
Dim stDocName As String
Dim stOutputName As String

path = Environ("HOMEDRIVE") ' determine the windows drive
target = Environ("HOMEPATH") ' determine the documents and settings folder
MyUser = Environ("USERNAME")
MyBr = Nz(DLookup("Branch", "tlbBrDateTemp"), "(unknown)")
MyName = Screen.ActiveReport.Name
stDocName = MyName & MyBr
stOutputName = "C:\Documents and Settings\" & MyUser & "\My Documents\"
& stDocName & ".snp"
DoCmd.OutputTo acReport, stDocName, acFormatSNP, stOutputName, True


End Sub

As for your question regarding my "tlb" vs "tbl", it is both. It started
out as a dumb mistake or typo on the first couple of tables, so I went with
it on the rest to do being lazy and not wanting to go back and fix a couple
dozen queries, marcos, forms and reports.
 
Error 2103 is "The report name you entered in either the property sheet or
macro is misspelled or refers to a report that doesn't exist". Your code
creates stDocName by taking the name of the active report and concatenating
a branch name onto it. You're then using stDocName in the OutputTo method.

I suspect you need

stDocName = MyName
stOutputName = "C:\Documents and Settings\" & MyUser & _
"\My Documents\" & stDocName & MyBr & ".snp"
 
Douglas,
Thanks for the quick response.

I changed to the code to

MyName = Screen.ActiveReport.Name
stDocName = MyName
stOutputName = "C:\Documents and Settings\" & MyUser & "\My Documents\"
& stDocName & MyBar & ".snp"
DoCmd.OutputTo acReport, stDocName, acFormatSNP, stOutputName, True

As you suggested, and it only names the file the stDocName. It didn't add
the MyBar to it.

Thanks.
 
Sorry Douglas, I am a poor typer..

I had a typo in my code. Once I fixed that, it worked...

Thanks for you help!!
 
Back
Top