Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-05-23
12
Medium Priority
?
784 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

604 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