Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

=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
0
LJG
Asked:
LJG
  • 5
  • 4
  • 3
2 Solutions
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
 
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now