Access MVP (2010-2015)


Quotes within Quotes (Apostrophes also)

You want to use TEXT as the Control Source of a Control on your Form but you keep getting an error OR it doesn’t show as expected because your text contains an apostrophe or quotation mark(s). For help see chart below…

Control Source Property Result Explanation
=”This is literal text.” This is literal text. Literal text goes in quotes.
=”Here is a “word” in quotes” Access thinks the quote finishes before word, and does not know what to do with the remaining characters.
=”Here is a “”word”” in quotes” Here is a “word” in quotes You must double-up the quote character inside quotes.
=”Here is a “”word””” Here is a “word” The doubled-up quotes after word plus the closing quote gives you 3 in a row.

When quotes or apostrophes appear in TEXT use:

"field =""" & Me.field & """"

 221 total views

Show the Cents Symbol

To use the cents symbol on your report or in a TEXT field use the extended ASCII 162, so…

Me.YourTextBox & Chr(162)

 212 total views

Show *Enter Password* in a Control

When you have a Log On Form you can indicate what is to be entered by using the Format Property of the Control.  By doing it this way the only value stored in the Table is what is entered, Enter Password is only for display.

In the Format Property of the Text or Memo field:
;@;”Enter Password” or @;”Enter Password”

If you want it to be in red use:
@;[Red]”Enter Password”

For Combo Boxes use:
@;\”Enter Password”

For additional information see…
Text and Memo data types (Format Property)

 239 total views

Print Constant Number of Lines (or Add a Constant Number of Lines)

This topic comes up from time to time in Forums and the answer usually involves a Temp Table or adding blank records or digging up that Microsoft® Report Utilities, How to Print a Constant Number of Lines per Group, sample which involved adding the controls you wanted to *hide* to the Function.  I thought why not make something a little more flexible and without the need for Temp Tables, adding a bunch of blank records or adjusting the code each time. To read more click here.

 253 total views

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.

 298 total views

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.

 1,307 total views

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.

 444 total views

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.

Tip: If you want the Icon to show but don’t really want any wording you can just put a space in the Caption Property.

 371 total views,  1 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!

 7,033 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.

 538 total views