Access MVP (2010-2015)


Use Replace() to Create a Bullet List

Use the Replace() function to turn your comma delimited values into a bullet list.  So…

Specification ID, Alloy, Temper, Gauge, Lot Number

…turns into…

· Specification ID
· Alloy
· Temper
· Gauge
· Lot Number

strFields: Chr(183) & " " & Replace([sdFields],", ",Chr(13) & Chr(10) & Chr(183) & " ")

Note, sdFields is the Field in my table that has the comma delimited values. You will will need to change the name to match the field in your table.

68 total views, no views today

Unit Conversion

***10.14.2018 tlkpUnitConversions has new entries and now includes Categories and Temperature conversions!

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.

654 total views, 1 views today

Maintain Record Position After Update

When updating a record on a Continuous Form it has a tendency to scroll back up to the top.  For End Users this can be problematic as they have now lost their place and have to scroll down to find it.  Here’s two slightly different Function to prevent that…

This one will cause the record to float to the top of the Form but the scrollbar maintains its position…

Function fRequeryPlus(frm As Form)
'To use fRequeryPlus Me
    Dim intRecordID As Integer

    intRecordID = frm.CurrentRecord
    DoCmd.GoToRecord , , acGoTo, intRecordID

End Function


This one will keep record and scrollbar position intact…

Function fRefreshPlus(frm As Form)
'To use fRefreshPlus Me
    Dim intRecordID As Integer

    intRecordID = frm.CurrentRecord
    DoCmd.GoToRecord , , acGoTo, intRecordID

End Function

Note, if you are deleting a record then only fRequeryPlus() will work UNLESS you only mark the record for deletion and delete upon closing the Form, in that case you can use fRefreshPlus(). Other than that you will see #DELETED in the controls for that record.

145 total views, no views today

What happened to my Form Icon?

So, you checked Use as Form and Report Icon under Options > Current Database  for your customized Icon to show on your Forms and Reports but when you opened your Form (or Report) there’s no icon…

Well, here’s the thing, if you choose No in the Properties window for the Form (or Report) under the Control Box then your Icon will not show:

Once you flip that to Yes, it shows.  Yep, apparently that property is basically applied to the entire menu bar on the Form.  So, if you REALLY want your Icon to show you need to set the Control Box to Yes and the Close Button to No.  While it leaves the Close button exposed, clicking on it does nothing.

145 total views, no views today

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!

4,391 total views, 7 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.

325 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!

509 total views, 1 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.

1,787 total views, 3 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.

4,663 total views, 7 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).

449 total views, no views today