Move Selections Up/Down in a List Box…

Moving selections up and down a List Box, sounds simple right?  Well, not so much… unless your name is Dale Fry.  I wanted a way for Users to be able to reorder items in a List Box, i.e. tasks, to do lists, activities and documents.  With this you can move items up or down on the list in order of importance.

Download the cool demo here.

0 total views, 0 views today

Delete a line from exported .RTF document…

Recently, I exported an Access Report  to Word (.RTF).  Nope, did not use Albert’s Super Easy Word Merge because the nature of the report with its many Subreports and Sub-Subreports did not lend itself to a mail merge.  Only after I exported it I needed to first copy it in its entirety to another document and then remove a line from the original document.

Function strDeleteLine(strPath As String)
Dim objWord As Object
Dim objDoc As Object
Dim objSelection As Object

Set objWord = CreateObject(“Word.Application”)
Set objDoc = objWord.Documents.Open(strPath)

objWord.Selection.Find.Text = “Beginning text of the line you want to delete

Do While objWord.Selection.Find.Execute(FindText:=”Beginning text of the line you want to delete“, Forward:=True, Format:=True) = True
With objWord.Selection
‘wdLine = 5
.Expand Unit:=5
End With

End Function

3 total views, no views today

Exporting clickable Hyperlinks…

Generally, I store the path to my file and the name of the File in two separate fields. I do this because if there is a likelihood that the database will be upsized to SQL Server the Hyperlink data type in Access is not supported and I’d have to do it anyway.  Not a big deal as in Access as I can use…

Application.FollowHyperlink  or…

Allen Browne’s GoHyperlink()

However, it is a problem if I then want to export then to Adobe, Word or Excel and keep them clickable.  To do that requires a little trickery (been wanting to use that word!).

For Adobe (.PDF) and Word (.DOC or .DOCX)…

  • In your Recordsource (should be a Query) for your Report create a field:

theDocumentLink: IIf([FieldThatHoldsThePath]<>””,[FieldThatHoldsNameOfDocument] & “#” & [FieldThatHoldsThePath] & “#”,””)

  • On the Report add theDocumentLink Control
  • In the Property Sheet for the Control in the Format tab…

Is Hyperlink to Yes
Display As Hyperlink to If Hyperlink

Now while in Preview of the Report you can select PDF or XPS or Word from the Data tab and your links will be clickable and display whatever text you put in [FieldThatHoldsNameOfDocument].


For Excel (.XLS or .XLSX)…

I tend to use Excel Automation because I can control a lot more. I also use Templates to I don’t have to worry about Formatting.  In this Template I have two hidden fields that hold the Path (column B) and the Name to Display (column C), column F will display the Hyperlink.

The first thing to is tell it how many rows are in your Recordset. Then I need to add which Row I am starting on.  In my case I am starting at Row 5 so I need to add 4 to my Recordset.

  • Add to your declarations

Dim lngLastRow As Long

  • Then under the CopyFromRecordset line add, this is so we can get the row count to apply our formula to

‘Add the number of rows from the top the Recordset will be copied from
lngLastRow = rst.RecordCount + 4

  • Now, for the Formula which you want to be sure to add after your data has pasted, so don’t add before the CopyFromRecordset…

‘Column F to display the Hyperlink, column B the Path and column C the Text I want to Display
With xlWSh
For i = 5 To lngLastRow
xlWSh.Cells(.Rows.Count, 6).End(xlUp).Offset(1, 0).Formula = “=HYPERLINK(B” & CStr(i) & “, C” & CStr(i) & “)”
Next I
End With

When you open the Excel spreadsheet your links will be clickable.

19 total views, 2 views today

Microsoft opens a User Voice site for Access

Yep, you read that right… Microsoft has opened up a UserVoice site where YOU can suggest features or improvements you’d like to see in Microsoft Access Desktop and Web Apps, as well as, vote on suggestions made by others. This is a great opportunity for YOU to pass on your ideas directly to the Product Team.  Have a look…  Welcome to Access Suggestion Box!

124 total views, 2 views today

Road Courtesies…

Where I live driving is a neccessity, however, it does not mean you own the road!  So, here’s my *gripe* about those folks that make me believe you really can get your license in the mail!


  1. Your Turn Signal was not an option, every vehicle has one, use it!  You may know where you are going but I don’t and..
  2. …for those of you who do use your Turn Signal it does not give you permission to just move over.  I’m on the road also and I like my car!
  3. Driving around with one (or more) head/rear lights out is a hazard.  Not just for you but for everyone else, we don’t know if that’s a car or a motorcycle we are passing.  Replace the bulb!
  4. The Speed Limit is not a suggestion, it’s the law, so, let’s stop running those who obey the posted limit off the road.
  5. A Muffler making that much noise on any vehicle is not cool at 1:00 AM or any other time.
  6. Your Car Radio/CD/DVD Player is for your enjoyment, turn it down.
  7. Throwing trash out the window is unacceptable.  Put a trash bag in your car or wait till you get home.
  8. Bluetooth, folks, bluetooth!  (In some states it’s a $500.00 fine to be using anything but Bluetooth.  That should be the fine in every state as far as I’m concerned!)
  9. Apparently, the *Don’t Text and Drive* hasn’t gotten thru to everyone!  Pull over to the side of the road if you must but, honestly, it can wait till you get someplace where you are not driving.  (Oh, and this also goes for you folks who think using their tablet/iPad/laptop isn’t as dangerous!)
  10. And finally, need I say more?

Well, those are my pet peeves, I’ll be stepping off my soap box now.

15 total views, no views today