To avoid getting an error when passing a variable to a form, using If Not against the EOF (I believe means End of File) property.
If Not Me.Recordset.EOF And Not Me.Recordset.EOF Then
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "someform"
stLinkCriteria = "[recordnumber]=" & Me.[recordnumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria
MsgBox "No Record Selected!"
Credits (As what I consider the hard stuff was actually done by other people):
TheSmileyCoder for the solid awesome little VBS File, this basically gets around the limitation of not actually getting Access to load another Access Database. At the time I only ever needed one modification (A rarely issue affected two P4 machines with a 5400RPM drive and bloated AV software). More than likely WScript.Sleep(200) won’t be required and the original file can be used.
UtterAccess for the AddTrustedLocation code.
http://www.accessmvp.com/DJSteele/DSNLessLinks.html – AccessMVP (LINK BROKEN!) as for this loader I tend to use the DSNLESS code to remove the need to deploy yet another ODBC link to user machines.
VBA Tips and Tricks for the FileExists module.
The following contains what I call the Microsoft Access 2010 Loader. It was put together to try and stop heavily used Microsoft Access database front ends from corrupting (We had a point where one particular Access front end would be corrupted up to 3 or 4 times a day as around 40 to 50 people would be in a 40MB Access front end at any time). The included version will require the table to be linked to a database, we use a central SQL database.
This access file will treat its own filename as the file it will launch, so be aware the loader and your actual access front ends needs to be located in different places.
What does it do:
It checks if a copy of the Access front end and VBS file exists locally (I use the %appdata% folder, you may want to use another location or %localappdata% instead)
If it exists it checks if the version in registry (HKEY_LOCAL_USER\Software\VB and VBA Program Settings\\Version\Version) matches what is in the database table “dbo_access_version_master”
If it doesn’t exist locally or the version does not match it then downloads from a location such as “\\myserver\access$\”
If downloaded the registry is updated with the new version number
It then passes parameters to a VBS file created by TheSmileyCoder and launches it
The first form of the called Access file MUST have VBA code of some sort or else the Access file simply closes.
I haven’t found a way to detect if a local copy of an Access ACCDR file has corrupt VBA.
I have found users copy and rename the loaders file breaking it, try and ensure shortcuts are used.
I haven’t been able to trap where Windows AD accounts have expired or locked, as the error occurs when Access connects to our central SQL database.
If the locations aren’t added to the Trusted Locations the user will be prompted twice to allow the Access files to open. The loader uses code to add this on first launch.
I sometimes find the repaints don’t always work.
Looking at the code used its a bit horrible looking, more than likely it can be optimized much better.
What do I need to change in VBA (Alt + F11):
Replaced in the VBA code “MyCompany” with your own on the main form and module SwitchFrontEnd.
Replace \\myserver\access$\ with where you will store your files (I used access$ as this is hidden if someone views the server)
If you don’t use .accdr files replace .accdr on the forms code
The actual files:
Microsoft Access Loader Files
Nearly every single Microsoft Access “database” I use has a proper database attached instead of using the internal Jet Engine, this means data is not actually stored in the Access “database” and I therefore call them Access Frontends.
Compiling your Access front ends to ACCDE provides a compiled copy of your Access Front End.
I find it is much harder for users to corrupt the Access front ends when compiled.
Making a compiled Access ACCDE file a runtime file is as simple as renaming the extension from ACCDE to ACCDR, all this does is make sure full installs of Microsoft Access launch in the runtime mode. It also means you can use the Access Runtime which is free from Microsoft (However always ensure you read through the EULA).
As runtime mode is used, its easier for development as I don’t have to hide menus.
To create an ACCDE from a Microsoft Access file, open the file in question, ensure your “Visual Basic for Applications” Code compiles otherwise the next step will fail.
Go to File –> Save & Publish –> Make ACCDE
Save the file in the location you want.
You can now rename the extension from ACCDE to ACCDR.
Now test the file launches.