Category Archives: MS Access

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

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

Forcing or Limiting Columns Using Cross Tab Queries

MS Access has a version of SQL that can be edited to enforce specific columns being shown in queries. Within the query editor navigate to the SQL View Normally when a cross-tab query is selected MS Access will generate columns … Continue reading

Posted in All, Database Design, MS Access, SQL MS Access | Comments Off on Forcing or Limiting Columns Using Cross Tab Queries

Access Connection Strings – Link

Some notes on using SQL Server connection strings both using the default ODBC client drivers and also the native SQL Server driver. Juan Soto of Access Experts explains connection strings from MS Access to SQL Server Additionally I am hearing … Continue reading

Posted in All, Configuration, MS Access, SQL Server | Comments Off on Access Connection Strings – Link

OBJECT NAMING CONVENTIONS – My UI Design Patterns

I like to do several things. Name my tables T001TextVariableInCamelCase incrementing the number Name my queries Q001TextVariableInCamelCase incrementing the number Name my forms F001TextVariableInCamelCase incrementing the number This works well for me – some of my first applications only had … Continue reading

Posted in All, Database Design, MS Access, My UI Design Patterns | Comments Off on OBJECT NAMING CONVENTIONS – My UI Design Patterns

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

COLOUR – My UI Design Patterns

Use it – for those things just built for me I have arguable gone a bit mental before. Conditional formatting is particularly good for dates and allowing people to quickly see what is coming up and what is past. I … Continue reading

Posted in All, Database Design, MS Access, My UI Design Patterns | Comments Off on COLOUR – My UI Design Patterns

A detailed explanation of the Algebraic modeling using SQL for a Race Timing System

I know I’ve given an overview of what I did to create the timing system but here is the kernel explanation of the algebra modeled in SQL that does the real heavy lifting in my system and probably most other … Continue reading

Posted in All, Applied Mathematics, MS Access, SQL MS Access | Comments Off on A detailed explanation of the Algebraic modeling using SQL for a Race Timing System

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