Option Group Label Color Change

This video demonstrates a code module which allows you to change the colour of the selected option group label, and optionally collect the caption of the selected label.

This code module is generic, it will work for any option group containing any number of labels (within MS access limits) and will change the colour of the selected option group label, you could also modify the code to change the other features of the label, like the border colour, border style, text style, font size, any attribute which is modifiable in VBA code.

The sample DB which contains the module HERE:

Excel in Access (Part 1)

Excel in Access (Part 2)
Excel in Access (Part 3)
Excel in Access (Parts 1, 2 and 3 as Video Instructions)
New! a follow on article
MS Access Your Data With Sub Forms

Problem for Excel Developers
One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets.

MS Access is NOT Excel
This similarity of the "look" in both programs, the layout of the data, leads to the Excel developer mistakenly thinking that a database works in a similar way to a spreadsheet.

Flat File Database
Spreadsheets are very sophisticated tools for manipulating figures, and they can also handle data very well particularly in the form of a "flat file database". You can also use the flat file approach in MS Access, however MS Access is primarily a relational database, meaning that it has the ability to relate tables of information together.

Relational
It is by taking advantage of this relational property of the database that you can really make significant improvements to data handling. So if you have a spreadsheet that requires upgrading to a database to take advantage of this, then this article is for you.

Typical Spreadsheet Layout
Below is an example which shows columns that you would typically find in a spreadsheet, first name, last name for example. Then a variety of subjects, with a check box against each indicating that the subject has been taken, passed or possibly even failed!

Click on Image for Fullscreen View

Flat File is OK
A direct transfer of this spreadsheet layout into an MS Access table would be usable and indeed many Access databases are constructed in this way, and some sophisticated applications costing many hundreds of pounds are based on a flat file system. However as mentioned earlier, MS Access is a "relational" database, meaning that it has the ability to relate your data together.

You're Not Relational????
So how and why should you apply a Relational structure? Well it solves a lot of problems, there are many but I have noted the main ones below.

Uncharted Territory
The first most common problem I have noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet, no doubt you will be able to come up with your own unique methods of extracting the data, however you will find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, "where no man has gone before".

Maintenance
Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let's say the school starts offering French lessons, in the spreadsheet all you would do is add a new column "French" and indeed in your MS Access database you could also add a new column "French", however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!

Column Limit
Many modern spreadsheet programs can handle thousands of columns; however MS Access has a 255 limit to the number of fields in any particular table. So in the student table example, if you were at the 255 limit and you needed to add another subject then you would find yourself in a difficult position.

Relational Solution
So what's the solution? This is the part that is sort of counter intuitive, you actually construct two tables from the original table and link them together. This is the "relational" aspect of the database coming into play.

How?
If you look at the original layout of the data above you can ask questions about it, is there any data in the original table that is related? Looking at it, I would suspect all of the boolean columns (the check box columns, yes/no data) they are all the same, so they are a likely candidate for a separate table. And indeed there is an obvious name for this new table, they are all "subjects" that the student is or could take.

The New Table
So now you have a name for the new table, "Subjects" and to link it to the data remaining in the original table, (first name,- last name) it will need to have a field which contains a match to the RecordUniqueID field. For this example let's call this "MatchingID" then you need a field to record the subject and another field to record whether it is true or false. For the purposes of this demonstration I have terms these "TransposedSubject" and "TransposedData" and you can see what this should look like below:



Free Normalization Tool --- Download it Here How to use the Normalization Tool

How do you get that new table you may ask? Well originally it was quite a tedious task, especially if you had many columns of data to move, you had to construct an append query and append each column. However I realized it may be a process that would lend itself to automation of some sort, and I came up with a form for handling this.

Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

Excel in Access (Part 2)
Excel in Access (Part 3)

Excel in Access (Part 4) --- Video Instructions

New! a follow on article
MS Access Your Data With Sub Forms

Excel in Access (Parts 1, 2 and 3 as Video Instructions)

Excel in Access (Part 1)
Excel in Access (Part 2)
Excel in Access (Part 3)


Normalization Instructions (I'm going to leave these on Ecademy for now, they are too wide for direct transfer to here....)

Download the database used in the following videos here: (This is the "empty" database before the following modifications are carried out)

The following five videos comprise a total viewing time of approximately 21 minutes in videos of length from three to six minutes.

The video's below demonstrate a slightly different use for the Tool previously explored in text and picture based threads. In these videos I demonstrate how to: Not only transfer the data from a spreadsheet table, but also the identity of the data picked up from the field name.

Convert contacts Spreadsheet to MS Access database 1 --- 6min
The video below explores a table which is in "flat file" format, suggests how you can choose which rows and columns would possibly benefit from being transposed into a new table, then demos how to do this with the tool provided (A Form)

 .
.
Convert contacts Spreadsheet to MS Access database 2 --- 3min
Explains how to remove redundant data from the table produced above, identifies a field that would benefit from being converted into a lookup table, and shows how to create a look up table from this field.

 .
.
Convert contacts Spreadsheet to MS Access database 3 --- 3min
using the above look up table demonstrates how to replace the "text data" with a number which links the former text (now in the look up table) with the original row/record.

 .
.
Convert contacts Spreadsheet to MS Access database 4 --- 5min
demonstrates how to create a form in datasheet view, and add a combo box to display the look up data

 .
Convert contacts Spreadsheet to MS Access database 5 --- 4min
and then display that form as a sub form showing the telephone number, communication media list, as related to the contact.

 .
The final database as completed in the above videos is downloadable from here: (this database contains all of the modifications as described in videos above)


Excel in Access (Part 1)
Excel in Access (Part 2)
Excel in Access (Part 3)
New! a follow on article
MS Access Your Data With Sub Forms

Excel Power Users Find MS Access Difficult

I've done a three part blog on why some Excel power users find moving to MS Access difficult.  The video below is a verbal explanation I did of the first part of the three blogs, you can find the text the video is providing an explanation for here:

Excel in Access (Part 1)


                                          YouTube Video HERE:

.....
....
...
..
.

Computerize your Business

Someone asked me how I go about compuerizing a business, this was my reply:

There are several ways; all have their pluses and minuses like everything. 

One way is to start looking at your business processes and tailor them to fit on an A4 sheet of paper. If a task is complicated involving several/many stages, then use a master A4 sheet to control the individual steps.

This method is very handy if you have several people working in different areas, as even if you do not computerize your business, you at least develop a formal business process for each area which is transferable to someone else.

For example:
a) in the event someone leaves the business
b) business increases and you need to train up other people to do similar tasks.
c) a turnkey business is much easier to sell, and in your case franchise.

The idea is that the A4 sheet will become the computer screen layout, giving the computer programmer a good idea of what you want, and giving your employees a familiar interface when you eventually move on to the computer.

Another way as I already mentioned is to find a package that does much of what you desire, you may need to use more than one package, however I think you can see that “off the shelf” will give you a good idea of what you don't want, and makes it much easier to communicate with a programmer.

One of my USP phrases is that I am primarily a businessman that can program; this gives me a distinct advantage in the design stage.

The most formal method is to draw up a specification, this method involves a considerable cost on just developing the specification; however you have the advantage of having a specification that you can offer around several companies and get competitive prices for the development.

My preferred method is to work alongside you, and, or, your staff “doing the job” where possible, as this is the best way to develop the computer system to work in the most natural way, the most supportive way for you and your staff.

As you can see, some of these methods you can tackle yourself, or appoint a member of staff to take responsibility for.

The most important thing is to start thinking about what’s involved, make notes where you can, just by making one or two short notes every day eventually builds up into a considerable document which will be very useful for you or anyone tasked to computerise your business.

Locking a Subform

I composed the following video to answer this question on access world forums here:
Locking a Subform

Instead of trying to lock the controls on the subform you can lock the "subform window" that the subform is displayed in.

LockUnlock SubForm Video on YouTube

Excel in Access (Part 3)

.
Excel in Access (Part 1)
Excel in Access (Part 2)
Excel in Access (Parts 1, 2 and 3 as Video Instructions)

How to use the Normalization Tool

Download a Demo Database and the Normalization Tool Here:

Using the normalization form we converted part of a spreadsheet type table into the beginnings of a relational table. From this table we derived a "look up table" now giving us a total of three tables, the remains of the first table, "tblStudent" (the student names), the next table "tblStudentSubject" stores the subject(s) related to each student, and finally a third table, "tblSubject" a "look-up table" to store the actual subject description.

From the table "tblStudentSubject" we created a form in datasheet view:

Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:

Using the wizard create a basic form from the student table and name it "frmStudent" arrange its size so it has some open space as shown.

Now open "frmStudent" in design view

And drag the subform "sfrmlStudentSubject" into the clear area on the student form "frmStudent"

You may wish to delete the text box, you don't have to but I usually find it looks better without it.

Size the form to suit

Now save the form and have a look, you will notice that it incorrectly shows all of the records in the subform,

Now the next bit is tricky, for two reasons, the form isn't really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.

Now open the "subform field linker" dialog box by clicking on the ellipsis (…)

Then select the fields which contain the data that link the two forms together, the master field will be the "Main Form" and the child field relates to the subform.

Notice how the master form ID and the subform ID(s) now match. You may wish to delete the ID field from the subform, it is not necessary for the maintenance of the link between the two forms.

You can download HERE, a database with the normalization form, --- --- and the tables and forms shown in this thread.

Excel in Access (Part 1)
Excel in Access (Part 2)
Excel in Access (Part 4)
Normalization Instructions

Download Demo db and Tool Here:

Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

Excel in Access (Part 2)

Excel in Access (Part 1)
Excel in Access (Part 3)
Excel in Access (Parts 1, 2 and 3 as Video Instructions)

In Excel in Access (Part 1) we went from this:

To this:

This was achieved with a form based tool available here.


See instructions on its use here:

However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.

The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there's no need to record that ID number "1" --- "Has Not" taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, "delete all the rows where the check boxes are false".

Once you have deleted all of those rows,

then it becomes obvious that the check boxes themselves which now "All" contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.

Using the "relational" properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.

First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the "subject"

This unique list should be called "tblSubject" this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.

Once you have completed the "look up table" you then need to replace the entries in the student subject table "tblStudentSubject" where it shows subject in text form with the number representing the link to the look up table. This is the query:

And here is the new column created:

This way your design changes to the table are making it much more efficient, holding the same information but with less data.

Next use the form wizard to create a simple form based on the student subject table "tblStudentSubject" the form should show two text boxes one for each of the columns in the table.

Open the form in design view and change the subject text box to a combo box:

This form is going to be displayed as a sub form on your main form in datasheet view so you need to go into the form properties and set its default view to "datasheet view"

While in design view select the combo box and access the combo box properties press the Ellipsis (…) in the "row source" property box to access the query builder.

In the query builder select the table "tblSubject"

Then drag both fields into the query builder grid

Click on yes to save these options

Set the combo box limit to list property to "Yes"

Still in the combo box properties, set the column count to "2"

and the column widths as shown.

Change the name of the combo box as shown or to your own particular naming convention.

Now close the form and reopen it; it should display in "datasheet view" and the numbers should be replaced by the text entries provided by the look up table.

In the original table; you can see it contains the now redundant check box fields

Open the table in design view and remove these redundant check box fields
Please note:

These “rows” actually represent the fields in the “Tables property window”, they control the display of the fields in the table.  On a re-reading
this article just now I realised there was a source of confusion.  I am not suggesting you delete any rows in
the table I am actually explaining how to delete “fields” from the table using
the term “rows” could be very confusing!

The original Table should now looks something like this:

Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

Excel in Access (Part 1)
Excel in Access (Part 3)
Excel in Access (Part 4)

Cheers Tony...

Lock UnLock Controls

A problem that crops up from time to time is the need to handle a group of controls differently from the rest of the controls of form.

Usually this is the need to lock the controls so that data cannot be changed, and conversely unlock them. However it may be the need to change the back color or some other attribute of the controls.

I was reminded of this need when I answered a question here:

And as I struggle to find suitable topics for Blogs, I realized this would make a good topic, particularity a as I have done most of the work already!

The attached Zip file contains a “*.mdb” database file with a working example of the code.

Video 1 --- Demonstration of a simple function that Locks or Unlocks controls on an MS Access Form, depending on whether you pass it a true or false boolean value.



Video 2 --- Demonstrates the method of naming the controls with a particular set of characters to identify them as belonging to a particular group.



Video 3 --- Demonstrates how to set particular controls values, (a group of similar controls) by looping through the collection of controls on an MS Access Form.




Video 4 --- Demonstrates how to replace two command buttons which switch between a lock and unlock state as in this case, and replace the two command buttons with a single command button and some code.

Video 5 --- Demonstrates how to setup command buttons to call a class module, the class module can then execute identical code for every button that calls it from your form.

Video 6 --- Demonstrates how to have your Command Buttons access the code in a Class Module through a function in the code behind the form.





Video 7 --- Overview of a Class that is used with an MS Access Form. This class enables multiple Command Buttons to perform basically the same action.

Open one Form from Another to a Specific Record

The video below demonstrates how to open one form from another using the command button wizard. The demonstration is set in the sample database provided by Microsoft, the "Northwind Database".

In the video I indicate another problem which can occur if you base one of your projects on the north wind sample database. You can view this problem and a solution here.

FULL SCREEN VIEW: (Back Button to Return)

FULL SCREEN VIEW: (Back Button to Return)

Handling Multiple Controls

Janz asked a question recently: I have to set 50 fields (text and color) How do I do this? See original Question and Answer here.

Handling Multiple Controls
Handling multiple controls in MS Access and many programming languages is counter intuitive. You have to think about the problem in a different way, and to help you do this I will give you a bit of background. Your Form is an object that can contain other objects, so your Form is also termed a “Container”, and the objects in the Form are called a "Collection" of objects.

Looping through the Collection
You can access the properties of these objects on your Form, (the objects in the collection) by looping through the collection and making a change to each object in the collection individually, (but the same change to every object)

Put them in a Sack
Imagine your keyboard represents the Form, and on this "keyboard form" there are keys, let's pretend the keys are actually text boxes with the name stamped on them --- A through Z so you have 26 named text boxes. If you collected all these keys together and put them in a sack, you could reach in and pull out a key, however you would never know which key you were getting, if you wanted to find a particular key (let's say "W") you would have to keep picking them out one at a time until you found the "W" key. You would pick the key up to look at it thinking what key is this? And there embossed on the key would be the letter "W" identifying the key. This is sort of how the collection works in MS Access, you have to look at each item (object) ask questions about it, (what are you) (what's your name) and then take action according to the answers you receive.

Typical example of the way you would do this:
Private Function fSetCap(strLblNumb As String, strLblCap As String)
'Name the labels
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acLabel Then
If Right(ctl.Name, 2) = strLblNumb Then ctl.Caption = strLblCap
End If
Next ctl
End Function 'fSetCap

How to Read the above Code:

Read this line of code: "For Each ctl In Me.Controls"

Like this:
For each control in me controls

In other words for each item, (key) in me (the form) controls, (the collection of controls)

The next line "If ctl.ControlType = acLabel Then"
interrogates the selected control asks are you a label? If it's not a label it skips around the loop and checks the next control.

If it is a label, then the code checks the last two digits in the controls name to see if they match the variable past to the code via "strLblNumb" if it finds a control with a match, then it changes the controls caption to the passed in string variable "strLblCap".

Works in many Languages
I know this seems a long winded way about going about it, but not only does it work within MS Access it works in many other programming languages, and it's a very powerful way of working because you can change all of the controls in one go, you can select groups of controls by various methods, by the contents of the tag property, by coding the control name as in the above example, you can change practically anything about the control, it's back color, you can enable or disable it, make it visible or invisible all sorts of things.

More information HERE from beginner level right up to some advanced stuff I hardly understand myself!

MS Access your Data with Sub-Forms

MS Access is such a brilliant tool, so easy to use, so powerful that I sometimes think people miss its obvious simplicity, the simple ability that makes it so powerful.

I’m not talking about Relationships

Many people get bogged down in the relationship issue, working out the relationships early on and imposing them on the system.  My advice is don’t; don’t use relationships, only use then when you can see the benefit, and if you do use them use them right at the end when you know how your database fits together.  Incorrectly formed relationships cause problems that are difficult to locate and can cause you headaches in the development process. And they’re not necessary; your database will function quite happily without relationships.

I’m talking about Sub Forms

Yes that’s right; I said “sub forms” Subforms are what makes MS Access so powerful and so easy to use.  Now you might be saying “how’s that then”?  Well,in my last article “Excel in Access”I wrote about a fundamental misunderstanding of how to structure your data in MS Access, once you understand the difference between “flat file” and “relational” see note (a) you will see that sub-forms used correctly offer a simple and easy way to display information from the extra tables you have created.  If you haven’t found it necessary to use Subforms then you probably have a very simple data structure or you have a problem!

Sub-Form Windows

When I said “sub forms are what makes MS Access so powerful” that’s not strictly true, I should have said it’s “sub-form windows” are what makes MS Access so powerful”,  I know it’s a moot point because they are one in the same, you can’t have one without the other.  When you drag a sub-form on to a main form a “sub-form window” is automatically created, what you actually see is a sub-form position on the main form, what you don’t see is the sub-form window surrounding the form; the subform window is the container containing the sub-form within the main form.  It may seem like I’m being pedantic about this minor difference, however it’s this intermediary control, this sub-form window that has all of the power.

Link Sub & Main

The sub-form window has some interesting properties that you can set, one of the most interesting properties is the ability to match the field on the sub-form within it to the matching field on the main form, by identifying these two fields, you in effect link them, causing the field on the contained sub-form to update automatically when a new record is added to the main form.  (This alludes to another frequent problem would be programmers have with MS Access, see note (b))

Fragment your Table

To use MS Access effectively (as covered in my article “Excel in Access”) particularly if you start off with data in a flat file format, then you need to modify this data structure by counter intuitively dividing it up into several smaller tables.  The problem becomes “how do you display this fractured structure? Before, you had a very nice flat file in which it was easy to see how the data related together.  Now you have all these extra tables which have to be combined to re-form the original data.  That’s where sub-forms come in, they very neatly and easily offer you a way to display the recombined data.

Why MS Access is so Powerful

Most people start off with data in a “flat file format” they then find themselves having to fragment this nice easy to understand flat file structure into several apparently disjointed tables.  This fragmentation creates the next problem, how to assemble this data into a presentable user interface?  The answer “sub-forms” it is this simple ability offered by sub-forms (sub-form windows) that makes MS Access so powerful.

Note (a)

I had, and still have the misconception that "Relational" refers to relating data from different Tables via a key value. The following paragraph exposes this misconception of mine... (It’s a useful misconception so I will keep it!)

Extract:

The relational model is thus named, not because you can relate tables to one another (a popular misconception), but as a reference to the relationships between the columns in a table. These are the relationships that give the model its name; in other words, relational means that if several values belong to the same row in a table, they are related. The way columns are related to each other defines a relation, and a relation is a table (more exactly, a table represents one relation).

From:

The Art of SQL --- Authors: Stephane Faroult & Peter Robson

1.1.  The Relational View of Data

Note (b)

I note from the forum where I answered questions(UKAWF) that a common problem people have is wanting to enter data into a sub-form before data is entered on the master form.  This won’t work because the subform window uses the unique reference number of the main form to give the subform record the linking information.

I’m not sure but when I get the time I’m going to explore this issue and see if there is a way round it.  My suspicion is it will be possible but difficult to implement in a “safe” way.  I’m pretty sure there’s something to be learnt about the fundamental operation of this subform master form relationship from attempting this “backward” operation and that’s a good enough incentive for me to undertake what will probably be a fruitless task.

Sending Google Mail (Gmail) from MS Access, VBA, Excel, Word...

Escape From Outlook
Microsoft tightly combine their applications together however sometimes it’s nice to escape from Microsoft Products and use others.  Here I demonstrate how you can use Google Mail to send out an e-mail from an Internet connected PC, and you can also do this covertly!

 This Example is From:

Windows® XP Under the Hood, Pg. 266 Chapter 6 Messaging and Faxing ObjectsExtract
 This basic example sends a simple, no-frills text message every time the script is run: Example File: script0603.vbs As you can see, the original file was a VBScript file; I have converted this to VBA.

Google e-mail Account
I have also set up a Gmail account to send the emails through.  You are welcome to use this account for testing, however I suggest you set up your own Gmail account and change the hard coded information where necessary if you intend using this code for your own purposes.

Gmail From MS Access (the Video)

Text from the above Video

This video is of a demo MS Access database which shows you how to send an e-mail from Google mail using
VBA.  Press the “Send a Test Email” button and wait and then you get this message saying that the message sent is "True". It doesn't necessarily mean the message has been sent, it just means the code operated successfully. It is not necessary to have that confirmation message appear, this can be hidden, hence you have the ability to send email from your application covertly!

Thank You

BTW there should be a Demo DB linked to from this Video.

 

I was not happy with the original file, so I have replaced it.  If you want the original file then it is available , you will find it at the bottom of my next post in this thread.  

 

Use this Updated Version HERE: UpdatedGoogleMail.mdb

 

Text Version of Code (VBA) for use in older versions of MS Access and other Microsoft Products like MS Excel, MS Word, etc... : GoogleMailFromMS_Access_VBA_CODE.txt