Category Archives: VBA Code MS Access

Typical For Next Loop

Function TypicalForNextExample() ‘This performs the same as the while loop but uses for next Dim i As Integer For i = 1 To 9 MsgBox (i) Next i End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical For Next Loop

Typical DAO.Recordset VBA for looping through and altering

Function TypicalDAOrecordset() ‘Make sure the name of the recordset is unambiguous ‘Good practice to reference the actual library Dim rs As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rs = db.OpenRecordset(“SELECT * FROM T001Main where T001Main.ValueNumber = … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical DAO.Recordset VBA for looping through and altering

MS Access VBA Function – Count Numbers of Records in Tables and list.

Not quite finished yet but place here for later correction. Public Function CountAllTablesRows() Dim rs As New ADODB.Recordset Dim rsRC As New ADODB.Recordset Dim strTbName As String Dim lngRowCount As Long Dim tbl As TableDef CurrentProject.Connection.Execute “Delete from TABLE_INFO” rs.Open … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Count Numbers of Records in Tables and list.

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.