Welcome

Hi,

Welcome to the second edition of 2012. I am not sure where this year is disappearing to, I cannot believe it is nearly already June and this is only my second Newsletter for the year. You definitely cannot complain that I am serial e-mailer.

I hope all is well with you.

I have completed lessons 6-10 of my Microsoft Access 2007 VBA Lessons. I am bundling them in groups of 5. (Note: There are 56 in the complete set)

If you are interested in purchasing these lessons, then they are available below:

Microsoft Access 2007 VBA Lessons 1 - 5

just $2.95 (AUD) to buy click on the button below:



Lessons 1-5 covers:

• Introduction into VBA
• How to convert Macros to VBA code
• The difference between Modules, Subprocedures and Functions
• Overview of DIM
• Overview of DoCmd

Microsoft Access 2007 VBA Lessons 6 - 10

just $2.95 (AUD) to buy click on the button below:

Lessons 6 - 10 includes:

• An overview of Constants
• A look at Collections, Properties, Methods and Events

It also includes some very useful code to manage two frequently occurring scenarios.

• Adding new items, to your drop-down boxes automatically
• Cancelling a report if there is no data, and letting the user know.

Note: I am using a new delivery system, it still uses paypal, but now the lessons will be available for immediate download. So if you have any trouble PLEASE PLEASE let me know by emailing me at julie@simplyaccess.com

The price of these lessons will remain at $2.95, until I have completed them all, then I will be reviewing the price.

If you use an earlier version of Microsoft Access, then do not despair, my original 56 lessons for Microsoft Access 2003 and earlier, is still available for purchase for just $7.95 (AUD)

One more plug, before I start on the tips. If you want a great product to convert your Microsoft Access database to SQL, Click Here I have used this product from MUST on some of my databases and found it works...which is the main thing, but it is also incredibly easy to follow the steps required.

OK enough of the product talk, on to the tips. This week I have I will continue with my normal format of three tips. I am first going to share these with you here, then I am going to load them up on to my website as pages.

Most of my tips are either items I am dealing with in the current databases I am designing or are questions asked at All Experts

Microsoft Access Tips

Tip One: Adding records to a table in Microsoft Word

Question:

I'd like to know how to send data rows from MS Access to Word programmatically, and at the same time during the procedure, determine if a new page needs to be inserted based upon the number of rows has reached the end of a page. The Word document would already have a header, footer and several paragraphs of pre-determined text; a template of sorts. The concept would be to provide a couple of line feeds prior to inserting the data rows from Access.

Answer:

First of all there are many ways to achieve this, but I find the easiest way is to use tables in Microsoft Word.

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.dotx") ' 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 'Adds a new row to the table, ready for the next record

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

Tip Two: Opening a second form and link that to the record on the first form

Question

I have a form that I want to add a command button to, this command button would open up another form. That form needs to automatically use the existing ID# of the previous form, automatically entering any related fields to that record in. In other words, I need the new form that would open to already have the ID# number in it, with all related fields automatically entered because of the link created when the form was created.

Answer

To do this:

Make a query based on the table that the original form is based on. Using the fields required for the new form. Add any other tables also needed. Join the tables in the query using your primary keys and foreign keys.

Base your second form on this query.

On the first form, add a command button, follow the wizard choosing to link the two forms, i.e. choose to display specific records.

This works OK for records you have already created, but you will need to do an extra step to ensure the forms are linked for new records. To do this:

Open the second form form in design view. Right click on the foreign key field on the form > Properties > Data > Default value

(foreign key, is the key used to link the first form to the second form)

In here type:

forms!NameOfFirstForm!PrimaryKey

Change NameOfFirstForm to the name of your first form and PrimaryKey to the name of the primary key field on the first form (i.e. once again the one used to link to the second form).

This will automatically link the new record on the second form to the first form.

Test it out.

Tip Three: Grouping by Time and Date

QUESTION

How can I group time and date in a query?

Answer

You can do this by formatting your dates and times differently. i.e. to group by the hour.

Build a query using the table with the dates/times

The following example, will group the records by day and hour.

Add the date field to your first column. If the time and date are in the one field, then add the following to the first row in the first column:

Date Field:Format([ApptDateTime],"dd/mm/yy")

For the time field;

In the next column, first row type the following: TimeByHour: Format([ApptDateTime],"hh")

Change ApptDateTime to the name of your time field.

Then use the grouping options as you would for other grouping options. i.e. clicking on the 'Totals' button on the toolbar/ribbon.

Add the other fields you require.

Then to display this in a better format, build a report following the wizard and choose to group on the above fields.

Do the same for the dates. Not sure exactly the format you are seeking, but hopefull this will point you in the right direction.

Cheers