?
Solved

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

Posted on 2013-05-23
12
Medium Priority
?
781 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 200 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
 
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 - Microsoft MVP, Access and Data Platform) earned 1800 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

770 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