Do you need specialised Access database consulting or an Access database solution? Our software is dedicated to providing assistance toward increasing efficiency and productivity of the small or medium sized business.
If you do not have the resources or time to develop software for your business, you may find something of interest here.
Archive for the ‘Microsoft Access How To’ Category
We can learn Microsoft Access from many resources. One great way is to use a Microsoft Access ebook.
An ebook can be read on your PC or laptop and is easier to transport than a traditional printed book.
I have written two great Microsoft Access e-books which you will find on this site.
If you want to know about the MS Access subform or the MS Access macro then you have come to the right place as there is a resource for both those subjects.
You will find my Microsoft Access ebook publications will enhance and take forward your understanding of Access. Struggle no more as I break the subject down in plain English. There is also a video tutorial series to accompany each Microsoft Access ebook.
Add to your library of MS Access books today and add another skill to your toolkit.
MS Access 2007 is the version of Access that changed everything. The interface is like no previous version and takes some getting used to. Instead of the classic toolbars and menus we now have what is known as ‘The Ribbon.
It is easier to now create basic forms and reports. Unfortunately there is not a lot of MS Access 2007 help available at this stage and it is important to get the correct training as soon as possible.
How much MS Access 2007 help needed will vary from person to person. Some need more training than others. We all learn differently and there are aspects of MS Access which are complicated.
Take for example database relationships – these are the backbone of any good MS Access database and it is useful to have a good understanding.
On this site I have created a great MS Access 2007 help product. Students have got great value from it and now it is available online or on DVD.
When Access loads we tend to see the database window. We can hide this and show
our own start up screen. This is known as a Microsoft Access Splash Screen.
Let’s get started.
Create a brand new form.
Drag a label onto it and add a caption.
Set any background or fore color properties you need.
Great so far.
Now bring up the form properties.
Set the following properties:
• Scroll Bars – neither
• Record Selectors – no
• Navigation Buttons – no
• Dividing Lines – no
• Auto Center – yes
• Control Box – no
• Min Max Buttons – none
Now switch to event properties.
Look for the ‘On Timer’ event.
Set the Timer Interval to 10000.
Click the small button on the side of the ‘On Timer’ event to bring up the code
window.
Between the Private Sub Form_Timer() and the End Sub, add the following code.
Private Sub Form_Timer()
DoCmd.Close acForm, “frmStartUp”
DoCmd.OpenForm “Main Switchboard”
End Sub
In the second line change “Main Switchboard” to whatever form you want to load
after the MS Access splash screen closes.
Now save the code and close the code window.
Save the form as ‘frmStartUp’.
From the ‘Tools’ menu select the ‘Startup’ option.
In the ‘Display Form/Page’ box select ‘frmStartUp’.
Uncheck the ‘Display Database Window’ box.
Click OK.
Close your database.
Now re open the database.
Your start up form – MS Access splash screen – will load and after ten seconds will close.
Your next form will then load.
Microsoft Access splash screens offer a more professional start up look to our applications. They are
not essential, but if we need them they are easy to create in Access.
I am going to talk about Microsoft Access calculated query fields.
What is a calculated query? If you have two or more value fields in your query you
can calculate a third field based on the other fields. This field is known as an MS Access calculated field.
For example if you wanted to find out the value of a customer who had bought
goods. It certainly is not practical to go in and manually calculate each line.
You would have the unit price and quantity, but would like to find out the total value.
So how can we do this?
I am going to use the sample database that comes with Access for this tutorial. Load
up the Northwind database. Usually the file will be nwind.mdb
Open the query designer.
Select the following tables
• Customers
• Orders
• Order Details
Access will link them in the query designer.
Double click or drag the following fields to the query grid.
From the Customers table add in CompanyName.
From the Orders table add in OrderDate.
From the Order Details table add in UnitPrice and Quantity.
In the query designer go to a blank column and on the field line type in the following:
When you click away from the grid notice how Access adds the square brackets
around the UnitPrice and Quantity fields. This is ok.
At this point you should make sure you have put a tick in the ‘Show’ box
You have now created an MS Access calculated field.
Now run the query.
Access has now totalled the column called ‘TotalValue’ a result of multiplying the
Quantity by the Unit Price.
Because I am in the UK my settings are set to the UK currency of pounds which is
why there is a dollar symbol in UnitPrice and a pound symbol in TotalValue. Don’t
worry about this, it can easily be changed. – the point here is the calculation.
If you have hundreds or thousands of records an MS Access calculated field is a great timesaver. Imagine
having to manually go in and calculate each line?
You could now base your form or report around this query.
I hope this has given you an insight into the power of MS Access calculated queries. It can really
help to automate calculations within a system.
An MS Access combobox is a very useful feature of Access and will enhance the look and feel of your forms. It will also keep your data consistent in your tables.
Let’s take an example of countries.
Your form may require name, address and country details. If for example you wanted
to record the United Kingdom. The United Kingdom can be known by a few other
names. For example UK, England, Great Britain and Britain. Now if you entered a
different country name each time you wanted to use the United Kingdom this can be
problematic for a number of reasons.
If you wanted to search on how many customers you have in the UK you would get a
misleading result. Your query may bring up all customers in the UK if you enter this
as criteria for searching, but what about records that have been entered as
‘England’?
The same problem would occur with the USA, US, America, United States of
America.
There is also the possibility of spelling a country incorrectly. If you have one record
with Brazil and another with Brasil then when you come to search you will get the
wrong results.
So how can we get around this problem?
The answer is to use a dropdown box or what is known in Microsoft Access as an
MS Access Combobox. This will also speed up data entry because you will be selecting an item
from a list and not typing it in.
An MS Access Combobox can use a table as it’s data source, but I prefer to use a query. The
reason for this is you can filter out the items you need in a query. Supposing you just
want a list of European countries on your form. With a query you could set criteria for
this and limit to the ones you need. Also there will be a performance improvement as
queries are faster than tables.
You can use any type of data for this lesson, but I will work on using a table of
country names.
1) Set up a table of countries and have a primary key ID field.
Your table should contain
Field Name: ID (with a data type of Autonumber. Make this field a primary key field).
2) Save the table with a name of ‘tblCountry’
Now create a query based on the table you just created.
1) Select both fields in the table and add to the query grid
2) Save the query with a name of qryCountry.
Create a new form or open and existing one.
I find it just as easy to create the combo box by using the Access Wizard.
Click on the controls tool box and make sure the control wizards button is depressed.
You can move your mouse over each control in the toolbox to find it’s name shown in
a small yellow box.
Click the control called ‘Combo box’ and keeping your mouse button depressed, drag
it onto the form. Click on the form at the position you wish to place it.
Access will bring up a combo box Wizard form.
As we are getting the information for our combo box from a query we created earlier
you should select the first option ‘that says ‘I want the combo box to look up the
values in a table or query”.
Click the ‘Next’ button.
Now the Wizard is asking you where your combo box information is coming from. For
instance is it coming from a table or a query and if so which one? As we created a
query earlier I am going to select the qryCountry query.
Access will now ask which fields you want to show in your combo box. It is always a
good idea to show the ID field so I am going to select that as well as the country field.
I essentially select both fields on this occasion.
Click the ‘Next’ button to continue.
Access will now show you how your combo box data is likely to look.
You can now adjust the width of the columns in your combo box. To do this move
your mouse pointer over the edge of the column headings until the mouse pointer
becomes a cross arrow shape. Now drag the column to suit your preference.
Also it is good practice to hide the ID field so drag the edge of the ID column to the
left until it cannot be seen.
Now you will be left with a single column of data for your combo box.
Click the ‘Next’ button to continue.
This next part is rather tricky.
Access is asking if you want to store the value you select from your MS Access Combobox or
use the value later. As we are not dealing with relating tables at this stage I will just
leave this step and click the ‘Next’ button to continue.
(NOTE: Normally the table that sits behind our form would contain a Country ID field
with a data type of ‘Long Integer’. We would then store the ID field value from our
combo box into the Country ID field in the table that sits behind our form)
We are at the final step and Access now asks what label name we want to give to our
combo box.
Type something descriptive in here and then click the ‘Finish’ button.
That’s it, we are done
Now run the form
As you can see there is no danger of typing in the wrong information when you have
a combo box. Ever record will always have the same values to select from so you
cannot again type for example ‘England, Britain, UK’ etc. Just select UK from the MS Access combobox
or US and your form and data will now become more consistent.
The table is the container for your data in your Access database. Any information you enter on your screen will be saved back to a table. The table will have columns…for example first name, surname, address, phone.
Access is a relational database. Data is related via separate tables, linked by columns with a primary and foreign key. Each table should represent a single fact.
When entering data one can occasionally misspell a word or two. We are all used to having a spell checker at hand when typing a Word document for instance.
Why should a spell check be limited to Word documents? Surely it would be of benefit if we could incorporate something similar in an Access database.
Well, I have some good news. We can do exactly that!
Create a form with a text box or memo field.
Enter some text into either of these controls.
Create a command button on your form.
Go into the properties of the button and set the caption to be ‘Spell Check’.
Right click the command button and select ‘Build event’.
Between the Private Sub and the End Sub enter the following code
You can copy and paste it if you like
With Me![My control name]
If Len(.Value) > 0 Then
DoCmd.SetWarnings False
.SetFocus
.SelStart = 1
.SelLength = Len(.Value)
DoCmd.RunCommand acCmdSpelling
.SelLength = 0
DoCmd.SetWarnings True
End If
End With
Replace the words inside the square brackets [My control name] with the name of your own control that you want to spell check.
Save the code and close the code window.
Now run your form and click the button.
Enter some misspelled text in your control and then click the spell check button.
You will find you get a similar spelling suggestion box that you have seen many times before when writing Word documents.
Let’s look at what’s actually happening in this code.
Don’t worry too much about understanding it – I will cover programming in further tutorials. This will give you a general idea though.
I want you to spell check this control
With Me![My control name]
If there is something in that control then go ahead and spell check it
If Len(.Value) > 0 Then
Turn all warning messages off for now
DoCmd.SetWarnings False
Focus to the control I want to spell check
.SetFocus
Start at the first character
.SelStart = 1
And continue to the last character of the word
.SelLength = Len(.Value)
Now run the spell check
DoCmd.RunCommand acCmdSpelling
Clear the variable used for the length of the word ready for the next spell check
