Need some help with DLookup 3

[SmallTime]

Hi,

I want to use paths stored in a table in rather then pointing to then directly. However I'm having some trouble.

The table containing the paths is "TblPaths" and is structured as follows:

ReportName DotPath
Introduction "C:\Templates\introduction.dot"
response "C:\Templates\response.dot"

The bit I'm having so problmes with is:

Dim SourcePath As String
SourcePath = DLookup("[DotPath]", "TblPaths", "[ReportName] = 'response'")

when I punch ?Sourcepath into the immediate window I just get a blance line, what I'm expecting is "C:\Templates\response.dot"


What am I doing wrong, or is DLookup the wrong way to go about this?

Regards to all.

 

[CautionMP]

SourcePath = DLookup("DotPath", "TblPaths", "ReportName='response'")

Just say no to barckets and spaces,
CMP


Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[SmallTime]

Thanks for the pointer. However I'm still having some problems with an amended version.

Dim SourcePath As String
Dim SavePath As String

SourcePath = DLookup("DotPath", "TblPaths", "ReportName='response'")
SavePath = DLookup("DocPath", "TblPaths", "ReportName='response'")

The SourcePath works OK but the SavePath doesn’t seem to take (no error messages either), any ideas would be appreciated.

Also, I'm not convinced I should be using DLookup in the first place. Is another way I could refer to the fields in TblPaths? TblPaths isn't associated with the underlying query (not shown here).

Many thanks.

 

[HandsOnAccess]

There is a typo in your second statement

While the first one says DLookup("DotPath", ...
The second statement says DLookup("DocPath", ...

Make the correction and try again...

SourcePath = DLookup("DotPath", "TblPaths", "ReportName='response'")
SavePath = DLookup("DotPath", "TblPaths", "ReportName='response'")

Regards,

 

[SmallTime]

Sorry forgot to mention that tblpaths now has an additional field Docpath.

To expalin, DotPath is where word templates are stored and DocPath is where the saved document is stored.

 

[HandsOnAccess]

In that case you will have to check if there is any data present in both the fields before actually assigning it to SourcePath and SavePath

You can do like this...

If Not IsNull(DLookup("DotPath", "TblPaths", "ReportName='response'"))=True Then
   SourcePath = DLookup("DotPath", "TblPaths", "ReportName='response'")
Else
   MsgBox "Source Path is Empty."
End If

If Not IsNull(DLookup("DotPath", "TblPaths", "ReportName='response'"))=True Then
   SavePath = DLookup("DotPath", "TblPaths", "ReportName='response'")
Else
   MsgBox "SavePath is Empty."
End If

Regards,

 

[HandsOnAccess]

There is a small error in the my previous post (I should have used "DocPath" for SavePath, By mistake I used "DotPath")
You can try this code...

If Not IsNull(DLookup("DotPath", "TblPaths", "ReportName='response'"))=True Then
   SourcePath = DLookup("DotPath", "TblPaths", "ReportName='response'")
Else
   MsgBox "Source Path is Empty."
End If

If Not IsNull(DLookup("DocPath", "TblPaths", "ReportName='response'"))=True Then
   SavePath = DLookup("DocPath", "TblPaths", "ReportName='response'")
Else
   MsgBox "SavePath is Empty."
End If

Regards,

 

[SmallTime]

I'll give it a try later on today, have to take the kids out for a bike ride now, their chopping at my heals already.

Huge thanks for your suggestion. it's intended that there’ll always be a valid entry in all three fields (they aren’t not exposed to the casual user). However, I'm going to use your suggestion as a 'just in case'. I've been so engrossed as to why those simple lines of code wouldn’t work that I haven't thought about making the refinements.
Many thanks.

 

[CautionMP]

It looks like your on the road to solution so one last thought.

There are some overhead issues with using Domain Aggregate Functions, since you are hitting the same table for each value you might consider writting your own function that returns both values with only one hit on the table.

Public Function DocumentPaths(ReportName as String, TemplatePath as String, DocumentPath as String) as Boolean
Dim rstPaths as New ADODB.Recordset
'Suggest opening the Recordset ForwardOnly, LockReadOnly
Set rstPaths.Open "tblPaths", CurrentProject.Connection
'Cycle through the table until RecordName is found
Do
  If rstPaths.Fields("ReportName") = ReportName Then
    'Found RecordName, get paths and exit loop
    TemplatePath = rstPaths.Fields("DotPath")
    DocumentPath = rstPaths.Fields("DocPath")
    Exit Do
  Else
    'No match found set function equal to false
    DocumentPaths = False
  End If
  rstPaths.MoveNext
Loop Until rstPaths.EOF
'Done with the recordset so close it
rstPaths.Close
Set rstPaths = Nothing
'Last check to make sure both paths have values
If Len(TemplatePath) > 0 And Len(DocumentPath) > 0 Then
  DocumentPaths = True
'Else
'  You could prompt for a valid filename here if you want
'  or use the False flag to prompt in the calling procedure
End If
End Function

This function will retrieve both values at the same time and alert you if either path is blank. To use in code you could do something like this:
[tt]If Not DocumentPaths("response", SourcePath, SavePath) Then
'What do you want to do if a value is blank?
End If[/tt]

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[SmallTime]

Thank you both for your kind help and assistance.

CautionMP, thats truely some awsome stuff, just the thing I needed but couldn't do myself.

One final thing tho I'm getting a syntax error at
Set rstPaths.Open "tblPaths", CurrentProject.Connection

Reguards

 

[RoyVidar]

Mind telling which errormessage?

Three guesses,
- first - remove the set keyword (it is alredy instantiated),
rstPaths.Open ...
- second - wrong table name - replace with the correct table name
- third - missing option

[tt]rstPaths.Open "tblPaths", currentproject.connection,,,adcmdtable[/tt]

Roy-Vidar

 

[SmallTime]

Sorry Roy,

The line was highlighted in red and on compile I got the message;

compile error:
syntax error

However your amendment did the trick just fine, nice shot.

Please accept my apologises and gratitude.

 

[CautionMP]

Sorry about the [tt]Set[/tt] statement. I was sitting in front of the TV using the media machine so everything was typed in Notepad (hence, no syntax checking).

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus