Error messages on unbound text (1 Viewer)

[jco23]

long time searcher, first time poster...

i created a database and released it for my team (frontend/backend setup). however, most of the members on my team are getting #Name? or #Invalid on some dlookup formulas in unbound text boxes. However, on my version, I see the actual data shown.

I tried adding the Microsoft references via the VBA screen, but that only worked for one person.

Any help would be greatly appreciated.

thanks!

 

[spikepl]

42 is the answer to life, the universe and everything.

If you want a more specific answer, you'll need to provide more specific information than "some dlookup formulas". Specific examples or attach compacted and repaired db zipped and say how to recreate issue.

 

[jco23]

I don't think I need to post the DB (plus, I believe it violates the code of conduct here). but the formula references the "date()" function - could that be causing the issue? I know that with Excel, if you use certain formulas that others do not have installed (such as edate for example), then they will not see the data shown, and instead get an error.

for example, if I want the year for today, I'm using year(date()). is there another more appropriate function?

 

[vbaInet]

1. Sounds like each user has their own front end, but I wanted to confirm. Is this the case?
2. Have you checked the references?
3. Does any other code run?

 

[jco23]

1. Sounds like each user has their own front end, but I wanted to confirm. Is this the case?
2. Have you checked the references?
3. Does any other code run?

correct, each user has their own front end (they get an installer link which places an icon on their desktop and installs the frontend on their PC)

i checked the references, but not with a fine tooth comb. I presume it is some reference, somewhere....

no other relevent code is running

 

[vbaInet]

Is any code running at all? And are you installing a mde/accde?

 

[jco23]

Is any code running at all? And are you installing a mde/accde?

when you say running any code, I presume you mean a macro or query? there is a query that runs when the form loads (continuous), but that information always pulls before the dlookups calculate. no macros are running at the time.

it installs accdb on the users PC

 

[vbaInet]

What I mean is if you click a button, does the code behind the button work? If no VBA code is running it's a good indicator that the db is not in a Trusted Location.

 

[jco23]

all code is running correctly, but it appears that users are no longer experiencing issues after enabling the Windows Script Host Object Model reference and waiting a few minutes.

thanks for your responses though, VBAInet!

 

[vbaInet]

Interesting! So none of the references were broken? Also, try re-enabling the VBA reference if the problem persists.

 

[jco23]

what's interesting now is that I made sure to check the Windows Script Host Object Model reference on the next release, and when they installed it, the errors returned. when they went in and UNCHECKED the Windows Script Host Object Model reference, then the error went away.

still can't figure out what could be causing this? could it be the install script, are others missing a driver? this is bugging the heck out of me...

if anyone has any additional insight, I would be most appreciative. thanks!

 

[vbaInet]

Ok, what are you using WScript for and is it absolutely necessary? And what is the order of WScript in the list of references? That tells me what priority it has.

Show me a screenshot of your references.

If you can, you can send me your front end and tell me which form is the problem form and I'll take a quick look.

 

[GinaWhipp]

Hmm, what version of Access and what References do you have checked?

 

[jco23]

I don't think that I am trying to using WScript - perhaps could be related to some date formatting when the page loads?

the order of my references are as follows:
Visual Basic for Applications
MSFT Access 14.0 Object Library
OLE Automation
MS Office 14.0 Access Database engine Object Library
MS ActiveX Data Objects 2.8 Library
MS ADO Ext. 2.8 for DDL and Security
RASAL (internation database reference for employee names)
MS ActiveX Data Objects Recordset 2.8 Library
MS Excel 14.0 Object Library
MS Jet and Replication Objects 2.6 Library
MS Outlook 14.0 Object Library
MS Scripting Runtime
MS VBScript Regular Expressions 5.5
MS VBA for Applications Extensibility 5.3
MS Word 14.0 Object Library
Windows Script Host Object Model

access version 2010 using MSFT Office Professional Plus (version # 14.0.7145.5000 32-bit)

vbaInet - thanks for the offer on taking a look at my frontend (wait, that didn't sound right)... but I don't feel comfortable uploading it, as the coding contains proprierty information.

 

[GinaWhipp]

Make a backup, again, make a back and then uncheck the bolded ones, one at a time, removing this one...

MS VBA for Applications Extensibility 5.3

...first. Between each removal make a BACKUP and restart Access. The first posted above is probably the reason for your issue. The balance don't look to be needed but may be depending on what your code looks like. Removing them and then running Debug>Compile will let you know if they are needed. If you uncheck and Compile and get a message, you need it EXCEPT for the one above, you do not need that one, only used in Access 97 databases. Here's a link showing the ones typically check, as well as, ones that will cause issue (see bottom of page)...

http://www.access-diva.com/d5.html

Visual Basic for Applications
MSFT Access 14.0 Object Library
OLE Automation
MS Office 14.0 Access Database engine Object Library
MS ActiveX Data Objects 2.8 Library
MS ADO Ext. 2.8 for DDL and Security
RASAL (internation database reference for employee names)
MS ActiveX Data Objects Recordset 2.8 Library
MS Excel 14.0 Object Library
MS Jet and Replication Objects 2.6 Library
MS Outlook 14.0 Object Library
MS Scripting Runtime
MS VBScript Regular Expressions 5.5
MS VBA for Applications Extensibility 5.3
MS Word 14.0 Object Library
Windows Script Host Object Model

 

[vbaInet]

vbaInet - thanks for the offer on taking a look at my frontend (wait, that didn't sound right)... but I don't feel comfortable uploading it, as the coding contains proprierty information.

No it didn't

Actually MS VBA for Applications Extensibility 5.3 is used for coding the VBA Editor itself. For example, if you want to add/remove code to/from a module/class programmatically, it's that library that you use to do just that.

But I agree with GinaWhipp, there are possibly some redundant libraries there.

 

[GinaWhipp]

@vbaInet

While that was true in Access 97, no longer needed and actually causes issue going forward. That ability is now included within the standard libraries.

 

[vbaInet]

@GinaWhipp, I'm aware that it causes issues sometimes and it's not sensible including it in the deployed front end, but the VBE, VBProject, VBComponent etc which are all members of the this type library are still only accessible via the VBIDE object library.

I'm currently on an Access 2007 on one of my old laptops (because my new laptop with Access 2010 went berserk and I'm having to rebuild it now) and the library is still required. And I remember needing this library on my Access 2010 version too. Don't know about 2013 though!

 

[GinaWhipp]

@vbaInet

Concur... however, unless you are directly using, causes more problems than it solves, best to uncheck.

 

[vbaInet]

That's right!