Category Archives: VBA Code MS Access

MS Access VBA Function – create UID starting at prescribed number

Useful function if you are taking two tables with overlapping identity key to be placed in a table that will have a further child record and you wish to separate the new keys. Public Function WriteUID(LCounter As Long) As Long … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – create UID starting at prescribed number

MS Access VBA Function – Loop through Query Objects and write SQL to Table

Continuing the theme of tools that assist the use of MS Access as a platform for transferring data between systems. Here is a small function that will allow you to write the pure SQL syntax of all queries in a … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Loop through Query Objects and write SQL to Table

MS Access VBA Function – Automated Multiple Query Object Creation from previously created table of SQL

This completes the task of taking automatically generated SQL previously placed in a table and writes the SQL therein to Query Objects naming them automatically. This has several advantages to cut and paste – 1) Its Lightning Quick 2) Completely … Continue reading

Posted in All, MS Access, Productivity, Programming, VBA Code MS Access | Comments Off on MS Access VBA Function – Automated Multiple Query Object Creation from previously created table of SQL

MS Access VBA Function – Create MS Access Query Object – more automation :)

In line with my general theme of automation here’s the framework of a tiny but potentially very useful function to create queries in the current database, linking this in with the loop queries and say something like the nested IF … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Create MS Access Query Object – more automation :)

Compact Database automatically using this MS Access Function and VB Script

If like me you sometimes need to run multiple SQL statements in MS Access on a regular basis maybe at a particular time some of which are deletes, you will need to find a way to automate regular compacts. Here’s … Continue reading

Posted in All, Configuration, VBA Code MS Access, VBS Scripts | Comments Off on Compact Database automatically using this MS Access Function and VB Script

MS Access VBA Function – Generate Multiple Nested IIF SQL statements into table with parameter to set Number of Nestings

So in my previous post I had looked at generating single SQL Nested IF statements using MS Access but had highlighted that Access will error out indicating the SQL is too complex should there be more than 13 nested IIFs … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Generate Multiple Nested IIF SQL statements into table with parameter to set Number of Nestings

Generate Nested IIF SQL using VBA code

I came across a situation at work where we were needing to alter a large number of values in particular fields from one value to another. Here is a function I put together to assist in this. After some thought … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Generate Nested IIF SQL using VBA code

Manipulating MS Word Documents from MS Access 2003

The following code generates separate word documents for each parent record in a table called T001ParentRecords and places the children records relating to the parent record in a word document. It then goes on to format that word document before … Continue reading

Posted in All, VBA Code MS Access | Comments Off on Manipulating MS Word Documents from MS Access 2003

Links to VBA Functions

Allen Browne Functions Index Technet list of functions

Posted in All, MS Access, VBA Code MS Access | Comments Off on Links to VBA Functions

What’s the difference between Sub Routines and Functions

I was curious Sub Routines and Functions appear to perform almost the same thing what is the difference and what are their relative advantages? Functions return a value that is stored whereas subs don’t. The main difference is not only … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on What’s the difference between Sub Routines and Functions

Typical While Loop VBA

Function TypicalWhileLoop() ‘This performs the same as next loop but uses the while loop Dim LCounter As Integer LCounter = 1 While LCounter < 10 MsgBox (LCounter) LCounter = LCounter + 1 Wend End Function

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

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