Access MVP (2010-2015)


Create a Splash Screen with Progress Bar

Taking the Splash Screen up a notch…

Sometimes you want to load some data before your database opens?  You don’t want your Users wondering what’s happening, so here’s a way to keep them advised of the progress.  (To download this example click here.)
Progress Bar

To use in your database, import the basProgressBar and frmSplash into your database.  Then make design changes to frmSplash to coordinate with your database.  (Note, do not remove or change the name of the Objects on frmSplash without changing the names (or making adjustments) throughout the code.)  Enjoy!

1,745 total views, 1 views today

Not in List event

There are many examples of how to handle new entries to Combo Boxes, a.k.a. Not_In_List events.  However, the one that is not so easily found is how to handle entries that include more than just what’s in the Combo Box.  For example, you’re entering an Order from a new Client and you need more than just the Client’s name… you want to enter their address, phone number, etc.   To do that we just need to add/open a Form and add our information…

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboClientID_NotInList

        Dim intAnswer As Integer

        intAnswer = MsgBox("Would you like to add this Client to the list?", vbYesNo + vbQuestion, "Not in List")

        If intAnswer = vbYes Then
            DoCmd.RunCommand acCmdUndo
            DoCmd.OpenForm "sfrClientProfile", , , , acFormAdd, acDialog
            Response = acDataErrAdded
            DoCmd.RunCommand acCmdUndo
            Response = acDataErrContinue
        End If

        Exit Sub

        MsgBox Err.Description
        Resume Exit_cboClientID_NotInList

End Sub

Note: Copy/paste everything between the Private Sub and End Sub and don’t forget to change the Form name to match your own.

185 total views, no views today

Unit Conversion

While I was working on my Grocery List to Recipe database (don’t ask, only been working on it for a year) I realized there was going to be a need for conversions and why not share?  It’s pretty basic and does not include ALL conversions but just open tlkpUnitConversions and add new ones there.  To download the sample click here.


299 total views, no views today

Appointment Collision Check

You’ve created a database for scheduling appointments, one little problem when entering these appointments you can’t tell if it clashes with another appointment (especially, if you are not the only one entering).  You could use a query but I always thought this would be handled better in a Module besides, I had an old database from 2002 (sorry, site no longer up so I can’t post a link) that was already part of the way there.  So, this looked like a good time to *make it work*!  Let’s get started…

My parameters:

(In this example I am using the below table, you will need to change the names here and in the Module to match yours.)

aActivityID (PK, Autonumber)
aAssociateID (FK related to tblAssociateProfile, Number, Long Integer)
aDate (Date/Time, Short Date [ No need to use Input Mask at Table Level ])
aStartTime (Date/Time, Medium Time [ Input Mask: 99:00\ >LL;0;_ ])
aEndTime (Date/Time, Medium Time [ Input Mask: 99:00\ >LL;0;_ ])

Module (Named: modCollisionCheck)…

You can incorporate into an already existing Module but for ease you can copy/paste the below into a new Module.

Option Compare Database
Option Explicit

Public pubActivityID As Long

Public Function getActivityID()
    getActivityID = pubActivityID
End Function

Function fCollisionCheck(dteDate As Date, stStartTime As Date, etEndTime As Date, lngAssociateID As Long)
'7.11.2016 rtw modified
    Dim dbDuration As Double
    Dim se As Date 'Starting time of appointments already made (in table)
    Dim ee As Date 'Ending time of appointments already made (in table)
    Dim blCollision As Boolean
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim frm As Form
    Set frm = Screen.ActiveForm
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblActivities", dbOpenDynaset)
    blCollision = False
    frm.lblCollisionCheck.Caption = " "
    dbDuration = etEndTime - stStartTime
        With rs
          Do Until rs.EOF
          'First check if the appointment date on the form is the same as the record in the table.
          'The recordset could be filtered at this point thus avoiding
          'to have to loop through all the records in the Appointment table.
          If !aDate = dteDate And !aAssociateID = lngAssociateID Then
              se = !aStartTime
              ee = !aStartTime + dbDuration
            'Lembit Soobik's nifty formula for checking collisions
            If stStartTime < ee And etEndTime > se And pubActivityID <> !aActivityID Then
                blCollision = True
                frm.lblCollisionCheck.Caption = "Collision"
                'MsgBox "Collision with Appointment ID: " & !aActivityID & vbCrLf _
                & "Starting at: " & se & vbCrLf _
                & "Ending at:  " & ee, vbExclamation, "Collision Check"
            End If
          End If
            Set rs = Nothing
        End With
    Set db = Nothing
        If Not blCollision Then
          frm.lblCollisionCheck.Caption = " "
          'MsgBox "No collisions encountered", vbInformation, "You made it"
        End If

End Function

Place Label, lblCollisionCheck, on your Form, in this example it’s above the Command Buttons.  You will need to place one on your Form use that potion of the code.  However, if you like you can comment out those lines and use the lines below that are pop-up messages.  (I personally like the Label as no extra clicking required by the End User.)

To use the Module above the Form must be set to Pop-Up = No. If you want the Form to be a Pop-Up in the Module above you must change this…

Set frm = Screen.ActiveForm


Set frm = Forms![YOUR FORM NAME]

Side Note: If you want to use a Main Form\Subform set up then the lblCollisionCheck must be on the Main Form, all other Controls can be on the Subform.  Since we are reading the Recordsource of the Subform no need to *worry about* the other Controls.

In the After_Update event of txtStartTime place (note the name of the Controls):

    If Not IsNull(Me.txtEndTime) Then
            Call fCollisionCheck(Me.txtDate, Me.txtStartTime, Me.txtEndTime, Me.cboAssociateID)
    End If

In the After_Update event of txtEndTime place (note the name of the Controls):

    pubActivityID = Me.txtActivityID
    Call fCollisionCheck(Me.txtDate, Me.txtStartTime, Me.txtEndTime, Me.cboAssociateID)

In the Lost_Focus event of both the txtStartTime and txtEndTime place (I like the extra Save.):

DoCmd.RunCommand acCmdSaveRecord

In the Forms On_Close event (We want to remember to drop the Public Variable when closing the Form in case we want to use it elsewhere.):

pubActivityID = Empty


So, if you enter an appointment that conflicts with another one (See the message?)…

Now, just change the time and message, Collision, will go away provided there still isn’t one!

283 total views, no views today

Create a Splash Screen (Simple)


To create a Splash Screen using a Form…

  1. Create a new Form
  2. In the Properties Sheet set…
    • Set the Border to None
    • Set Auto Center to Yes
    • Set Pop Up to Yes
    • Set Moveable to No (optional)
    • Set the Timer Interval to the amount of time (in milliseconds) you want the Splash Screen to stay open, i.e. 5000 = 5 seconds
    • In the On Timer Event Procedure place DoCmd.Close
  3. Save, close and double-click to reopen and test.  Click here to download a sample.

P.S. Splash Screens are especially handy if you want to preload some data before the database actually opens.

389 total views, no views today

Adding a custom image to a Command Button

A little tired of the stock images that come with Access?  You can add your own…

Step 1 – In Design Mode of your Form (if the Property Sheet is not open do so now) click on the Command Button.  Then in the Property Sheet select the Ellipse button, the one with the three dots, on the Picture line…


…and the Picture Builder window will open…


Step 2 – From the Picture Builder window select Browse… and the Insert Picture will open.  In the lower right hand corner you will see…

BitmapWhere is says Bitmaps (*.bmp) select the drop down and change to All Files (*.*)

AllFilesStep 3 – Navigate to the folder where you have the image you want to display on your Command Button and select the image…


…and select Open from the lower right hand corner.  Then select OK


And, WA-LAH, your image is on your Command Button!




In your Model Database create a Form that has all your *mostly used* Command Buttons.  Then when creating a new database you can easily copy/paste your Command Buttons from it to your Forms without going thru the steps above for each Command Button AND you have the added plus of setting up a Button Legend for the End Users.

2,204 total views, no views today

Where to copy/paste Code

You found a piece of code, it says to copy/paste into the Event Procedure of your Control, great!  Hmm, now where exactly is that?

First, open you Form in Design Mode making sure the Property Sheet is open.  Select your Control and on the Property Sheet select the Event tab.



Go copy the code you want to paste.  Then click on the line of the event you want to add the code to, i.e. the On Current.  From here you can click the Ellipse button, the one with the three dots, or you can first select [Event Procedure] from the drop down and then click the Ellipse button, pictured below.



Once the Ellipse button is clicked the VB Editor will open…

Then paste (Ctrl+V) your code between the Private Sub… and End Sub.  To be sure it pasted correctly, at the top from the menu select Debug then Compile.  If there are any errors you can fix and then save and close (or close and save).

203 total views, no views today

Tools, Utilities and more…

Recently, I watched Free Tools for Microsoft Access recorded by Crystal Long and Adrian Bell (Microsoft Access MVP’s) on Channel 9. Upon looking at comments below it dawned on me there is no central location for all those great free tools! How is anyone to find them? After all, you may not know they even exist let alone how helpful they can be to you in your development. So… I have added a new page just for them. (Okay, not all of them are free but most are.)

If you know of a free tool for Microsoft Access send me an eMail.

181 total views, no views today

Importing into a *clean* Database

1. Create a new database naming it differently then the original.  (No worries you can rename it later.)

2. Uncheck Track name AutoCorrect info under File > Options > Current Database, as well as, any other options you don’t want.

3. Go to the External Data tab and select Access under Import.

4. In the Get External Data – Access Database window select Browse and navigate to the old file.

5. Then on the Table tab select only the tables that do not start with MSys*, if showing.  (If not showing on the right click Select All.)  Then select the balance of the tabs and where you find objects in the left pane click the Select All button on the right.

6. Then select OK and wait until done.

You now have your new database.  You will need to set up your Start Up Options again, if you have any because those do no come over to the new file.  Confirm that your new database works as expected and then you can save the old one as a backup or delete it.  Finally, rename your file.

189 total views, no views today

Left-Handed Combo Boxes

I sometimes use Combo Boxes as *labels*.  This comes in especially handy in the communications section of the Contacts Form where any given Contact can have more than one eMail or more than one phone.

Left-handed Combo BoxTo move the drop down to the left… while the Form is in Design Mode click on the Combo Box.  (If not already open bring up the Property Sheet.)  Then on the Format tab of the Property Sheet scroll down to the bottom till you see Scroll Bar Align and change to Left… all done.

119 total views, no views today