Welcome

Hello everyone,

2nd edition for 2011. Ok a bit behind my monthly promised schedule. For new members, I sent my last edition out in May, so not too bad, just lost June somewhere. Does anyone know where it went to?

It is winter here in Australia and where I am it is cold and wet (for us anyway). Went up to the ski slopes last weekend. I did not ski; as last time I did (2 years ago) I dislocated my elbow, so not that keen to repeat that experience. I went up to keep my daughter company who is a keen skier. We camped out, but had access to a hiking hut which had an open fire. We have all the right equipment so were quite warm and confortable. It was great to get back to the basics for a few days.

Ok onto where I am up to with my site, etc.

VBA Lesson Price Reduction

Click here to order your VBA lessons

I am still updating my VBA lessons to Microsoft Access 2007/2010. Will let you know when I have completed this.

Microsoft Access Tips

1. Exporting data from Access to Word

Answer You can do this in at least two ways - bookmarks or tables. I will go with the table option for this tip.

To set this up:

Create the word document, with the predetermined text, header and footer etc, then add a table where you want the data to go, just one row, but as many columns as you have fields in your output. Save the file as a document template.

Once you have done that use the following code:

Public Sub sWordTables()

Dim appWord As Object 'Word.Application
Dim WordDoc As Object 'Word.Document
Dim db As Object
Dim rst As Object
Dim strPath As String
Dim intholder As Integer

strPath = "C:\Users\Julie\Documents\Simply Access\Allexperts\AllExperts\" ' change to the path where document template is

Set db = CurrentDb

Set rst = db.OpenRecordset("tblCustomer1") 'Change to the name of table/query you wish to ouput

'Opens word with the appropriate template

Set appWord = CreateObject("Word.Application")

appWord.Visible = True

Set WordDoc = appWord.Documents.Add(strPath & "doc1.dot") ' change doc1 to the name or your template

intholder = 1

rst.MoveFirst

'Populate the table

Do Until rst.EOF

appWord.ActiveDocument.Tables(1).Cell(intholder, 1).Range.Text = Nz(rst!BusinessName, "") 'change the names of the fields to suit.

appWord.ActiveDocument.Tables(1).Cell(intholder, 2).Range.Text = Nz(rst!FirstName, "")

appWord.ActiveDocument.Tables(1).Cell(intholder, 3).Range.Text = Nz(rst!Surname, "")

appWord.ActiveDocument.Tables(1).Cell(intholder, 4).Range.Text = Nz(rst!Suburb, "")

'You may need to add in more depending on how many columns you have

appWord.ActiveDocument.Tables(1).Rows.Add

intholder = intholder + 1

rst.MoveNext

Loop

appWord.ActiveDocument.Tables(1).Rows(intholder).Delete

Set appWord = Nothing

Exit Sub

Errorhandler:
If Err.Number = 0 Then
'Do this
Else
MsgBox Err.Number & ": " & Err.Description, , "Error Message"
End If

End Sub

Note: If you are not sure where to put this code, then the VBA lessons (above) maybe for you.

2. Compacting and repairing your data base

As we change things in a database, especially doing design changes, the size of your database can grow very large. Therefore it is important to 'Compact and Repair' your database as you build it. Do this routinely. How often is up to you, but probably a good idea to get into the habit of doing it when you have finished with the database for the day, or if you are having a break.

To 'Compact and Repair' the database:

Microsoft Access 2000 - 2003

On the toolbar choose Tools > Database Utilities > Compact and Repair

Microsoft Access 2007 - 2010

Click on the 'Ball' in the upper left hand corner > Manage > Compact and Repair.

That is it for this month. Catch up with you all in August.