Trying to add VBA code to Access Database

[farmerscott]

Hi Everyone,

I have just upgraded to Access as part of Office 365 (Windows 10 Pro).

Am trying to write a small piece of VBA code to update a calculated field (before I move to the next record).

1. I keep getting the following error- "Microsoft Visual Basic for Applications- File not found". Upon clicking the help button it takes me to File not found (Error 53).

2. Also where does the code go? In a module?

What am i missing?

thx.

 

[ajetrumpet]

reading the article, scott, I'm pretty sure it can be a multitude of things. Access has always had serious problems when it comes to being even remotely reliable. I've said this many times, and I still believe it today....MS has never cared about it because they don't make any money with it. Their baby is the .NET stuff and their cloud services. so anything else takes a back seat. and there are actually many people out there that still wonder why Access even still exists. case in point, a recent article: Microsoft Access: The Database Software That Won’t Die.

the best way to help you, if no one else has time to, is for you to upload the file. u got a cloud service or free cloud drive at your disposal? if you want, upload it there and I'll look at it. or someone else for that matter.... please note that I have both acc versions 2016 and 2007 to use, if need be. access is also notorious for being buggy from version to version, more than likely for reasons mentioned above.

 

[JonXL]

Adam,

I am all ears........

Will be away from the computer off and on for the next 12 hours, so I might not reply straight away.

thx

FS

Open the VB editor (Database Tools tab > Visual Basic) then go to Tools > References.

Are there any marked MISSING?

 

[Joe4]

reading the article, scott, I'm pretty sure it can be a multitude of things. Access has always had serious problems when it comes to being even remotely reliable. I've said this many times, and I still believe it today....MS has never cared about it because they don't make any money with it. Their baby is the .NET stuff and their cloud services. so anything else takes a back seat. and there are actually many people out there that still wonder why Access even still exists. case in point, a recent article: Microsoft Access: The Database Software That Won’t Die.

the best way to help you, if no one else has time to, is for you to upload the file. u got a cloud service or free cloud drive at your disposal? if you want, upload it there and I'll look at it. or someone else for that matter.... please note that I have both acc versions 2016 and 2007 to use, if need be. access is also notorious for being buggy from version to version, more than likely for reasons mentioned above.

I think you are veering off on a tangent here, I really fail to see what the first paragraph in your reply has to do with the question asked. The issue is most likely a missing reference, as Jon has pointed out (note that this happens just as often with Excel VBA too!).

I have used Access for about 20 years in business, and if you use it the right way, and have proper expectations of it, reliability usually isn't an issue! I would caution against making blanket statements about its reliability and buginess. I find that most people who experience these issues are people who don't understand how to use it properly.

 

[ajetrumpet]

I have used Access for about 20 years in business, and if you use it the right way, and have proper expectations of it, reliability usually isn't an issue! I would caution against making blanket statements about its reliability and buginess. I find that most people who experience these issues are people who don't understand how to use it properly.

you're right, Joe. I find it quite disappointing that Access is neglected by MS, because I've always loved the program myself. So to your point, thanks for correcting me. I've always had problems with access, and I do my research quite well most of the time, so I'm pretty sure I'm not wrong. But that all aside, yeah let us get back to the point of this thread. If you wanna know what problems I've seen over the years, you're welcome to send me a PM. thanks.

 

[Legacy 456155]

Have you been able to compile your VBA project? Have you Compacted and Repaired? (from database tools tab) If you receive the error while trying to compile, create a shortcut to your database file and add the /decompile argument.

Example: "C:\Some Folder\MyFile.accdb" /decompile
Quotes are necessary, I think, if your path contains any spaces.

Anyway, after opening decompiled, compact and repair. Hold down the shift key if you have any startup code.

Now try to compile. If successful, Compact and Repair again.

If you are still getting the error, there are additional steps we can take.

 

[welshgasman]

Check your references.
Alt + F11
Tools
References

You should have Visual Basic for Applications checked. If not, find it from the list and put a tick in the checkbox by clicking it.

HTH

Edit: Sorry guys, a good few posts were not displayed, and I only just spotted a link to expand the thread.

 

[farmerscott]

Adam,

copy of the file on Google drive Finance DB 19.1.2020.accdb. Your article was interesting. Agree it is people like me that keep it going.


Welshgasman,

The following were checked under references-

Visual Basic for Applications
Microsoft Access 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Access Database engine object.

hope that helps.

Dataluver,

Not sure if I need to compact and repair as I am only starting to build the database.

thx

Farmerscott

 

[Legacy 456155]

Ok. If you are getting that error, you have at least added a module or userform. The steps I gave you usually solve your particular problem.

 

[farmerscott]

Ok. If you are getting that error, you have at least added a module or userform. The steps I gave you usually solve your particular problem.

Correct. I can get a form working and write the code (the environment is there) but can't execute it either by F5 or F8. I keep getting the above error code.

 

[ajetrumpet]

scott,

I have no idea how you got into this situation, but here's what the problem probably is. You have your main form called *cash transaction*, but there is a form object in the VBA editor called *cash dissection*, which does not exist. how did that happen!? when you delete a form through the interface, it's supposed to disappear from the object list in the vba editor too. here's what your interface looks like:

but your VBA editor looks like this:

and even FURTHER weirdness! when I imported all the objects into a new database, I saw THIS in the *forms* tab:

after importing all the objects into a new file, I got no error. so now you try it. by the way, this type of weirdness is usually solved by access experts by a typical response when all else fails. either they say: IT'S CORRUPT. or they suggest importing all the objects into a new file like this. That just comes from years of working with access experts. and this is exactly why I moved away from using it. but none the less, it's still quite powerful in terms of being a tool. let us know if this works. here's my fixed file for you: illustration new file.accdb