=Dir(CurrentDb.Name) Not working in Report --> Control Source

Version -->: Access 2010

Problem:
Dir(CurrentDb.Name) works in vba but does not work as a control Source in a Textbox on a report.

Facts:
1) It's in the text box as --> =Dir(CurrentDb.Name)
2) It came over from Access 2003 where it was used all the time.
3) The database is an --> accdb
3) I don't know if this has anything to do with it, but I can't put DAO reference (Microsoft DAO 3.6 Oject Library) - It gives me an error -->Name conflicts with existing module, project, or object Library

Thanks in advance for any help.
LJG
LVL 2
LJGAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
David L. HansenProgrammer AnalystCommented:
Take a look at this.  If that does not help I have some other ideas that may help.
http://answers.microsoft.com/en-us/office/forum/office_2007-access/dir-function-in-vba/c0651b08-8d77-4d03-a4aa-a29eeb4d1155
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" It came over from Access 2003 where it was used all the time."
Not surprising, because a lot of things got 'tightened up' starting in A2007, some of which were probably never intended to work, but did.

"It gives me an error -->Name conflicts with existing module, project, or object Library"
Can you post a screen shot of your References ?

mx
0
 
LJGAuthor Commented:
Sl8rz

Thanks for the response.  
The article doesn't help - but thanks for trying.

I can make Dir(CurrentDb.Name) work in code so I can make things work, but I'm looking for why it doesn't work as a control source for a field in a report in 2010 but works fine 2003.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LJGAuthor Commented:
DatabaseMX

With you saying  things got 'tightened up' are you saying that
= Dir(CurrentDb.Name)
should never of worked as a control source on a field?
Even further are you saying Internal and written functions shouldn't work as a control source in fields?

Are you trying to scare me?  :)

Found the answer for  - "It gives me an error -->Name conflict..." -
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27819249.html
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm  saying that I suspect CurrentDB.whatever was never supposed to work in a Control Source.  I've experienced the exact same issue ... using RecordsetClone.Recordcount, etc.
0
 
David L. HansenProgrammer AnalystCommented:
Well now, you say Textbox right?  Seems to me you would want only one file listed in a textbox, correct?  If so, I'm guessing that from Access 2007 and beyond, the Dir function was altered so that it only returns the full directory list (like Dir in DOS) not just the first one in the list.  That's cold consolation I'm sure; however, if you are just looking for a cause and already have a solution in hand, then perhaps this explanation will put your mind at rest.
0
 
LJGAuthor Commented:
OK - for discussion purposes

I consider the below facts (You know - Perceived Reality)

1)  CurrentDb.Name will return something like --> C:\Develop\MyDatabase.accdb
2) Dir( "C:\Develop\MyDatabase.accdb") will return -->  MyDatabase.accdb  <-- because there is only one file in that directory with that name.
3) If the above is true then Dir(CurrentDb.Name) will return  -->  MyDatabase.accdb <-- (which by the way it does in code)

So why doesn't  -->  =Dir(CurrentDb.Name) work in a text box but it does in code.

Here is the workaround - but I am one of those guys who wants to know WHY something doesn't work so I can avoid things in the future.

If the text field has a name of txtDBName then the below works.
Me.txtDbName.ControlSource = Chr(61) & Chr(34) & Dir(CurrentDb.Name) & Chr(34)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
It also does not work in A2013 - just checked.
Nor does just =CurrentDB.Name in either A2010 or A2013.

I think the bottom line here is ... you will need to find a work around, which fortunately is quite simple.  Just call a Function from the Control Source that returns Dir(CurrentDB.Name)

mx
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
David L. HansenProgrammer AnalystCommented:
Yes, I'm being lazy here by not testing this myself...sorry.  However, I think that the DIR command (since it's been tightened up by MS) will return a collection of some sort.  And even if that collection is one item, you have to specify which item you want.  My thinking follows that if an array (not a true collection I know) has only one item in it, I still have to specify the zeroth item to do anything with that item.  I can't just assign the contents of the whole array to a control that display only one item.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"So why doesn't  -->  =Dir(CurrentDb.Name) work in a text box but it does in code."
I think I explained that in my first post. However, I do not know the exact reason, sorry.

" but I am one of those guys who wants to know WHY something doesn't work so I can avoid things in the future"
Me too ... but still.

Also:

Whereas Chr(61) & Chr(34) & Dir(CurrentDb.Name) & Chr(34) in code works when the ControlSource is set to this, it's (apparently) not equivalent to doing it directly in the Control Source.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"DIR command (since it's been tightened up by MS)"
I seriously the DIR command has changed at all. That would be a disaster.

If you are referring to my use of 'tightening up', I was only referring to using constructs like
=CurrentDB.Name in a ControlSource.

mx
0
 
LJGAuthor Commented:
Thanks All!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.