Solved

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

Posted on 2013-05-23
12
752 Views
Last Modified: 2013-05-23
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
0
Comment
Question by:LJG
  • 5
  • 4
  • 3
12 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39191568
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
 
LVL 75
ID: 39191612
" 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
 
LVL 2

Author Comment

by:LJG
ID: 39191617
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
 
LVL 2

Author Comment

by:LJG
ID: 39191639
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
 
LVL 75
ID: 39191647
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
 
LVL 15

Assisted Solution

by:David L. Hansen
David L. Hansen earned 50 total points
ID: 39191649
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Author Comment

by:LJG
ID: 39191721
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 450 total points
ID: 39191742
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
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 39191760
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
 
LVL 75
ID: 39191800
"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
 
LVL 75
ID: 39191809
"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
 
LVL 2

Author Closing Comment

by:LJG
ID: 39191883
Thanks All!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now