Access Sql Statement Path Information

[sibleytr]

Access Sql Statement Path Information

I would like to be able to read information from an access table in order to deremine where to copy a file from and where to copy the file to. Currently eveything works except I have the Source and Target information hard coded.

Through a serious of Combo boxes and user questions my form builds a file name: strFilename = Box1 & Box2 & Box3.Pdf

When I click my browse button I want the default path to be pulled from a table with "Source" as the qualifier. Once the file is selected I want the file to be copied to the default path listed in the table with "Target" as the qualifier.

This is what I have so far but I don't know how to put the tblInfoPath.strDetails information into my variable.

Table.Fields
tblInfoPath.strPathType
tblInfoPath.strDetails

Dim strSql as String
Dim rs as RecordSet
Dim strSrc as String
Dim strTrg as String

'Source
strSql = "SELECT * "
strSql = strSql & "FROM tblInfoPaths "
strSql = strSql & WHERE tblInfoPaths.strPathType " strSql = strSql & strSql & 'Source';"
DoCmd.RunSQL strSql
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)

'What do I do
strSrc = rs

 

[sibleytr]

Found it!

Dim strSrcPth As String
Dim strTrgPth As String

strSql = "SELECT * FROM tblInfoPaths "
strSql = strSql & "WHERE tblInfoPaths.strPathType = 'Source';"
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
strSrcPth = rs!strPathDetails

strSql = "SELECT * FROM tblInfoPaths "
    strSql = strSql & "WHERE tblInfoPaths.strPathType = 'Target';"
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
strTrgPth = rs!strPathDetails

 

[PHV]

Another way:
strSrcPth = DLookUp("strPathDetails", "tblInfoPaths", "strPathType = 'Source'")
strTrgPth DLookUp("strPathDetails", "tblInfoPaths", "strPathType = 'Target'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sibleytr]

Thanks

 

[rubbernilly]

You can look at automating the common File Open/Save box. There are a couple of API calls and plenty of variables involved, but it gives the users a screen they are used to, and gives a very professional, finished look to your database.

Here is some code to look at for that:

http://www.mvps.org/access/api/api0001.htm