Category Archives: VBA Code MS Access

Using VBA to write Word Document

Writing to Microsoft Word Firstly a warning – this creates doc documents that can be opened in Word 2010 but are strictly speaking 03 iterations hence the doc suffix First need to load in the library for Microsoft Word (this … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Using VBA to write Word Document

Using VBA and Databases to create HTML

Here’s some code I used to generate HTML for a web configuration file. It takes a database (the current open one) then looks to a query called QueryTargetInformation and places the fields – PlaceName / EastingMn / NorthingMn / EastingMx … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Using VBA and Databases to create HTML

Early and Late Binding

Good article on Early and Late Binding MSDN Early and Late binding Coding can appear very complicated sometimes but to simplify the difference in terms of implementation this line is very clear; The only difference between early binding and late … Continue reading

Posted in All, MS Access, VBA Code MS Access, VBA Code MS Excel | Comments Off on Early and Late Binding

MS Access and Forms – Create a Filtered Autonumber for Child Records

The following uses a function and the before update event of a form. Sometimes it can be useful to have some kind of order field in the child records to indicate the order or version numbers of items. Although an … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access and Forms – Create a Filtered Autonumber for Child Records

VBA Function Boolean Switch to test for specific character sets within a field

Boolean Switch to test for specific character sets within a field. This codes tests whether a field contains blanks or the specified characters only and will return -1 if true and 0 if false. If a character occurs that is … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on VBA Function Boolean Switch to test for specific character sets within a field

Ranking of Child Records according to Groups

Imagine you have a school full of Students and they have done a variety of exams. All results are collected in a table and you would like to obtain rankings by subject. How can you automatically rank all the students … Continue reading

Posted in All, SQL MS Access, VBA Code MS Access | Comments Off on Ranking of Child Records according to Groups

Using SQL to parse, clean and format strings

Many datasets can be somewhet confused by the time you get them. Maybe you had no control of the export from the database or maybe you asked for the right information and it came back somewhat warped. SQL has powerful … Continue reading

Posted in All, Data Cleansing, SQL MS Access, VBA Code MS Access | Comments Off on Using SQL to parse, clean and format strings

AutoHotKey : Navigation between Satellite Applications to improve Work Flow

A while back I wrote a post about how allowing parameters to be passed to URLs is a big benefit in increasing the speed with which you can navigate to individual records in apparently non-connected web applications. But what do … Continue reading

Posted in All, AutoHotKey, Productivity, Programming, VBA Code MS Access | Comments Off on AutoHotKey : Navigation between Satellite Applications to improve Work Flow

VBA function to Pivot and Concatenate Child records

In situations where a Parent record has a limited number of children (0 to 10 works well) and you would like to list those children next to the parent somewhat like you would with a Pivoted table. Pivoting the table … Continue reading

Posted in All, VBA Code MS Access | Comments Off on VBA function to Pivot and Concatenate Child records

Save Record before Event

If users are editing or creating a record and there is an option on the form to print out the form if they press it they will expect the information that they have just created to appear on the print … Continue reading

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on Save Record before Event

HIDE MENUS – My UI Design Patterns

Really simple – create a new module and create the following simple functions. I usually name them TurnMenuOn and TurnMenuOff. Public Function TurnMenuOn() Application.CommandBars(“Menu Bar”).Enabled = True End Function Public Function TurnMenuOff() Application.CommandBars(“Menu Bar”).Enabled = False End Function Then you … Continue reading

Posted in All, Database Design, MS Access, My UI Design Patterns, VBA Code MS Access | Comments Off on HIDE MENUS – My UI Design Patterns

The devil is always in the detail – Setting constants to Russian cyrillics in VB

With programming the devil is always in the detail. Just out of curiosity I was thinking about the code that I have posted that randomises information in a database and I was thinking why don’t I try to randomise the … Continue reading

Posted in All, Database Design, Learning, MS Access, Programming, VBA Code MS Access | Comments Off on The devil is always in the detail – Setting constants to Russian cyrillics in VB

VBA access code – Passing a selection of e-mail addresses to Outlook

This is similar to the previous post except uses the inbuilt SendObject object to pass the string sBcc directly to Outlook. Outlook picks up the variables and so there is no need to set up public variables. CODE Private Sub … Continue reading

Posted in All, Database Design, E-mail, MS Access, VBA Code MS Access | Comments Off on VBA access code – Passing a selection of e-mail addresses to Outlook

VBA access code for pulling together a list of e-mails and passing to a form

Below some standard code that I use to loop through a selection of records and create a string from the individual [Email] s in the below case the e-mails are passed to a form (FORMTOOPEN) – if you are using … Continue reading

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on VBA access code for pulling together a list of e-mails and passing to a form

Wanting to demonstrate a database and need to scramble the data?

Here’s a nice function I found that will completely randomize information within fields of a database. Data will not be recoverable from this process which of course is its strength. Good if you are wanting to demonstrate a database to … Continue reading

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on Wanting to demonstrate a database and need to scramble the data?

Keyser Söze, Code and the World Cup

It seems appropriate given the name of this site and 2014 being a world cup year that I might post something on code that I found a while back now related to establishing fixtures in a league coded by, for … Continue reading

Posted in All, Applied Mathematics, MS Access, VBA Code MS Access | Comments Off on Keyser Söze, Code and the World Cup

Step through forms and alter properties.

A nice patch of code that will allow you to cycle through a series of forms and make them read only. Useful if you don’t have immediate access to make changes to the backend, SQL Server or active directory. If … Continue reading

Posted in All, MS Access, Programming, VBA Code MS Access | Comments Off on Step through forms and alter properties.

Using VBA to open URL in chrome

Haven’t tried this out but could be useful. I have a digital mapping web application that I link to from a database and it has issues with IE but works perfectly in Chrome… shell(“C:\Program Files (x86)\Google\Chrome\Application\chrome.exe -url http:google.ca”) Update : … Continue reading

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on Using VBA to open URL in chrome

Scope of variables – And Getting Confused

Note to self If you are wanting to pass parameter values between forms ensure that you place public variables in a module outside of the form. IMPORTANT – additionally ensure that the same variable names are NOT also listed in … Continue reading

Posted in All, Configuration, Database Design, VBA Code MS Access | Comments Off on Scope of variables – And Getting Confused

Code for altering tables on the fly

Most of the time when you are wanting to enter information automatically in fields as a result of a user interaction it is easiest to use some kind of event trigger from the form. Regularly you want to close down … Continue reading

Posted in All, Database Design, MS Access, VBA Code MS Access | Comments Off on Code for altering tables on the fly