Welcome

Hi,

Welcome to the fourth edition for 2013.

Lots of things happening on the home front:

My daughter's little baby girl is now 4 weeks old and settling into a routine. I babysat her for the first time, which meant a couple of trips to the engagement party so my daughter could breast feed her. We only live a few minutes away, so all quite handy and easy.

It is Autumn here in Australia and in Bendigo where we live we are having beautiful warm autumn days.

I only have one tip for you this week, it was something I wanted to achieve in the database I was working on. What I wanted to do, was to highlight an entire row when that row had the focus, in a continuous form. I have done this previously, but not for a while so I had to rethink the process. Hope this tip is of a benefit for your database building.

Microsoft Access Tips

Tip One: Highlighting the current row in a continuous form

The problem I had was that when a field had focus, I wanted all the fields for that current record (in a continuous form) to also change colour. Changing the current field was easy. I just right clicked on the form, chose ‘Conditional Formatting’, then chose ‘Field has focus’ and set the formatting to how I wanted it.

But I wanted to change the other fields also in the same record/row. This caused a bit of a dilemma. I tried a few different things that did not work, but then decided to look a bit broader than just using conditional formatting and I came up with a work around.

This is what I did.

I added an unbound text field and called it ‘Holder’ I made sure, the Primary Key field for the record was also on the form. As the visibility of the primary key field was not required, I made it invisible:

To do this:

Right click on the Primary Key field > Properties > Format > Visible = No

Then what I did:

For each of the fields ‘GotFocus’ event I add the following code:

Me!Holder = Me!PrimaryKeyField

Change PrimaryKeyField to the name of your Primary Key Field

Not sure where to add the code? Here are some instructions

• Right click on the field > Properties > Event >

• Place your cursor next to ‘On Got Focus’

• Click on the button with the 3 dots

• Select ‘Code Builder’ > OK

Code similar to the following will appear the ‘Microsoft Visual Basic for Applications’ (VBA) editing window.

Private Sub NameOfField_GotFocus()

End Sub

Enter your code between the two lines of code above. Then close the VBA editing window by clicking on the most outer, upper right hand cross.

Repeat this for each of the fields.

What and Why: Now each time a field has the focus, i.e. the user clicks on the field an event is run known and the ‘GotFocus’ event.

The code we added to this event, is to assign the value in the Primary Key Field to the new text box we have just created.

The reason for this is to use this value later in the conditional formatting where we compare it to the Primary Key Field, and as we have purposely made it the same value, when the two fields are compared the expression will return a true value, thus the conditional formatting will be invoked.

Next I added the conditional formatting as per my instructions to you below:

• Right click on your first field > Conditional Formatting

The conditional formatting window will open.

Depending on what version of Microsoft Access you have but for Microsoft Access 2007 or later

• Click on the ‘New Rule’ button

• Choose ‘Expression Is’ from the drop down list

• Then in the blank box type the following:

PrimaryKeyField = Holder

Change ‘PrimaryKeyField’ to the name of your actual Primary Key Field

Microsoft Access 2003 will be similar to the Microsoft Access 2007 instructions above, but not exactly the same.

As mentioned earlier as these two fields have the same value, the conditional formatting will be invoked.

This is a great idea to implement to help the users of your database easily identify which row they are on.

Hope you find this tip of some benefit.

Cheers