Blog

Aug 24
Auto Expand / Search in an Access listbox

Access comboxes have a very useful ability to search for items as you type in data, e.g. if a combobox has the following items:


Cook
Clay
Crisp
Richardson


and you type in "cr" then "Crisp" will be selected. In order for this to happen you just need to set the combobox's Auto Expand property to True (and sort the combobox's contents in order). However, there is no equivalent property for Access listboxes.

There is, however, a work around. If you create a Class called, say, "ListboxSearch" and enter the following code into it and follow the instructions in the comments in its header, you will find that if your listbox has the same items as the combobox above, typing in "cr" will take you to "Crisp" rather than to "Richardson" as would normally happen :
​​

Option Compare Database
Option Explicit
'====================================================================
'NOTE Need the following code in the form's header

'Dim mlb As ListboxSearch

'and the following in the form's Load event:

'Set mlb = New ListboxSearch
'mlb.Setup SearchForm:=Me, SearchListbox:=(listbox to search), FieldToSearchOn: = (search-field name)

'and the following in its Form_Unload

'Set mlb = Nothing
'====================================================================

'Further enhancements?
'Could return the value of mstrKeysEntered to the form to show in a label or textbox?


Private mstrKeysEntered As String
Private mListbox As Listbox
Private WithEvents mForm As Form
Private mstrFieldToSearchOn As String
Private Const mconQuotes As String = """"
Private mstrAppName As String
Dim mrst As DAO.Recordset

Public Function Setup(ByRef SearchForm As Form, _
    ByRef SearchListbox As Listbox, _
    FieldToSearchOn As String, _
    Optional AppName As String = "")
    
    Dim db As DAO.Database
    
    Set mForm = SearchForm
    mForm.KeyPreview = True
      
    Set mListbox = SearchListbox
    If mForm.OnKeyPress = "" Then
        mForm.OnKeyPress = "[Event Procedure]"
    End If
    
    mstrFieldToSearchOn = FieldToSearchOn
    
    Set db = CurrentDb
    Set mrst = db.OpenRecordset(mListbox.RowSource).OpenRecordset(dbOpenSnapshot)
    
    mstrAppName = AppName
    
    On Error Resume Next
    Set db = Nothing
    
End Function

Private Sub Class_Terminate()
    On Error Resume Next
    mrst.Close
    Set mrst = Nothing
End Sub

Private Sub mForm_KeyPress(KeyAscii As Integer)

    Select Case KeyAscii
        Case 8
            'Backspace
            If Len(mstrKeysEntered) > 0 Then
                mstrKeysEntered = Left$(mstrKeysEntered, Len(mstrKeysEntered) - 1)
            End If
        
        Case 27
            'Esc
            mstrKeysEntered = ""

        Case Else
            mstrKeysEntered = mstrKeysEntered & Chr$(KeyAscii)
            
            mrst.FindFirst mstrFieldToSearchOn & " Like " _
                & mconQuotes & mstrKeysEntered & "*" & mconQuotes
            If Not mrst.NoMatch Then
                With mListbox
                    'Allow for whether Column Headers are in use.
                    .Selected(mrst.AbsolutePosition _
                        + Abs(.ColumnHeads = True)) = True
                    .Value = .ItemData(mrst.AbsolutePosition _
                        + Abs(.ColumnHeads = True))
                End With
            Else
                MsgBox "No such value found. Please start again", , mstrAppName
                mstrKeysEntered = ""
            End If
            
    End Select
    
    Debug.Print KeyAscii, mstrKeysEntered
    
    KeyAscii = 0
    
End Sub​





Aug 19
Using Unicode characters to supply glyphs (icons) for buttons in Access web apps

It is possible to add glyphs to buttons and hyperlink controls in Access web apps subject to a couple of constraints:

1) The character is added as a Unicode character and the users' browsers must be able to show them.

2) You need to find a suitable Unicode character!

There are a number of sites on the Web where you can find Unicode characters displayed. One such excellent (sub)site is http://unicode-table.

My method of adding such Unicode characters to my AWAs is to find the appropriate Unicode character, e.g. 1F4BE for a "Save" glyph, type 1F4BE into a Word document, press Alt/X to convert 1F4BE into the glyph and then paste it into the Caption of the AWA button or into the Control Source of the hyperlink control. The result is as below:

SaveGlyph.png 

Note that you can even get colour this way!

 

Note that it is may possible to copy characters such as Wingdings font characters and paste them into your command button or hyperlink control. AWAs seem to keep note of the font used.​ I've not been able to do this consistently though.

Aug 19
Calling e-mail client from Access Web App

Access web apps (AWAs) have a limited ability to send e-mails using the SendEmail data macro action, but currently (19th August 2015) it sometimes sends to the wrong e-mail address. I have a service request open with the Office 365 people about SendEmail sending e-mails to alan.cossey@matvchurch.uk instead of alan@pdtl.co.uk (yes, I am putting the correct address in the macro). Sometimes it will send to address outside the particular tenant and sometimes not and it also cannot provide details of who the e-mail is sent from, preferring instead to show it as coming from no-reply@sharepointonline.com.

However, it is possible to get AWAs to send e-mails via your installed e-mail client, e.g. Outlook 2013, to anyone you like. The method involved makes use of hyperlinks starting "mailto:".

Below is a hyperlink control where I have set the control source (using the Expression Builder) to e-mail to the e-mail address held in a nearby textbox. The control source is:

"#mailto:"+[txtOffice365ID]"

Note the glyph of an envelope, but it could be normal text, e.g. "E-mail"​. This will be the subject of my next post., but for now just be aware that this will produce a hyperlink which shows no text, just the envelope symbol. The glyph above is the result of copying and pasting a Wingdings character, but Unicode characters can also be used.​

When the user clicks on the hyperlink control in the browser, the e-mail client (for me Outlook 2013) opens with the e-mail address from txtOffice365ID in the To box.

This works neatly with Microsoft Edge, but with Google Chrome a stray new browser page also opens up by default. However, if you set the "Open in" parameter for the hyperlink control via the Formatting charm to "Same Window" rather than the default "New Window/Tab", no stray new browser page opens up in Google Chrome and Microsoft Edge continues to work OK.

Jan 10
Smart Indenter installation problem (resolved)

​There are a number of add-ins available for Microsoft Access, which make it much more productive. Two of my favourites are Smart Indenter and MZ Tools, though the latter seemed to be causing me some problems a while back, so much so that I uninstalled it. I was finding that Access crashed when exiting a database, something possibly due to my PC being 6+ years old and me having had various versions of Office installed on it and then uninstalled and stuff. Smart Indenter is indispensible for me as it makes my VBA code much more easily readable. So, when I bought myself a shiny new PC, installed Office 2013 and then found I couldn't install Smart Indenter, I was very disappointed indeed. It seemed to install OK, but did not show up in the VB Editor. I eventually came across a post (http://tinyurl.com/q8owkdb​) which told of problems caused by a Visual Studio file, which broke MZ Tools and Smart Indenter. Since Smart Indenter has not been updated for years (no need to under normal circumatances), I tried installing MZ Tools and then Smart Indenter. It seems MZ Tools installed the necessary new version of the offending file and I now have both MZ Tools and Smart Indenter working again.


Hooray!

Dec 04
The new Download in Excel functionality on Access web app datasheets - Part 2

​The new functionality is very welcome, but his has some limitations and I am going to make some suggestions on how to work around a couple of them.

Firstly, if you have any dates in your datasheet, they will be downloaded in m/d/yy format, the US format used by... the US, but not the majority of people on the planet. You can format the resulting column in the downloaded spreadsheet, but the next time you do a download you will have to do this again. This could be a real pain.

One way of getting around this is to use your downloaded spreadsheet as a "back end file", a concept very familiar with "normal" Access developers. Here you don't use the downloaded spreadsheet for displaying and manipulating data, but instead create a "front end" spreadsheet which links all its cells to cells in the "back end". Thus you can format the equivalent "front end" date column and it won't be affected each time you download the data. You can also set up calculated columns and stuff like that.

Secondly, you can use this front end / back end concept to show column titles. The download from Access into Excel does not include the column titles, so you could set these up in your front end spreadsheet.

Another way of getting the ​​​headers into the downloaded spreadsheet is to add them as the first field of your query. However, this is rather complicated and another story for another time.

Dec 04
The new Download in Excel functionality on Access web app datasheets - Part 1

There is now (4th December 2014) some welcome new functionality in ​Access web app datasheets. Datasheets now have a new button, which, when clicked, will allow the user to download the data shown in the datasheet into an Excel file. This is great.

This functionality is, however, very much "version 1.0" and fairly basic. To repeat though, it is very welcome.

In my first plays with it I came across some problems. Firstly, I received an error message saying, "View in Excel - One or more columns have custom expressions or lookups that (sic?) can't be shown in Excel." It then proceeded to download perfectly OK!

Though the datasheet was not displaying the field, there was a calculated field in the underlying query which was a concatenation involving a calculated field in a table:

​StatusAndPerson: Concat([ProblemStatus],": ",[FirstNameSurname])

where FirstNameSurname is that calculated field in a table. The reason for having the StatusAndPerson field in the underlying query is that I was using that query for the equivalent List View and using that field as the secondary field in the List control.

I tried using a new query without that field as the source for my datasheet, but still got the error, so I started taking things out of the query. The error only disappeared when I removed the entire table (containing the StatusAndPerson field)! Thinking I had solved the riddle, I thought I would start putting the fields back into the query. Lo and behold, no error message. Eventually, I had my entire original query back and was not generating any error messages. I was even able to add the StatusAndPerson field into the datasheet without causing an error message.

So I'm confused. Did I actually do something different to what I thought I did as outlined above? Was there some sort of caching problem? Were Microsoft still doing things behind the scenes? I don't know.

Aug 15
Problems with Access web app subviews when the main record has not yet been saved

​When using a list view incorporating a linked subview a user can hit trouble if they enter the data into a new record on the main view (the "one" part of the "one-to-many" relationship) and then try to enter data into the subview (the "many" part) before they have saved that new record. This is because the subview needs to know what the main record's primary key is. Standard Access databases (using Access tables) do not have this problem as with standard Access databases the primary key is generated as soon as you start to enter data into the main record. With Access web apps it is necessary save the new main record that prmiary key is generated and thus you cannot start to enter data successfully into the subview until the subview has learnt of that main record's primary key. To overcome this problem it might be possible to set up some events so that, say, the On Current of the main record checks to see if you are on a new record and, if you are, disable the subview then if you click on the Save button the subview could be enabled. However, to do this you can't use the built-in Save button in the action bar but would need to create your own. If you do that you then need to recreate the other built-in buttons to ensure the action bar buttons are all enabled and disabled at the correct time. This is too much hassle!

A simpler, yet not entirely user-proof, course of action is to use the Current event of the subview. Here we can check to see if the subview has a value in the control holding the foreign key. If it doesn't, it means that the user has not saved the main record. If so, we can warn the user that they need to do this and then reject the data they have attempted to enter into the subview, thereby giving them the opportunity to save the main record before coming back to the subview. An example of the macro code we might use is below. The example is from a keys database where we want to enter details of a keyholder (the main part of the view) and also details of multiple keys (in the subview).

1) Ensure you have a control, perhaps hidden, in the subview which holds the value for the foreign key. In our example below it is an AutoComplete control called acKeyHolderID.

In the subview, create an On Current macro.

2) Use an If clause to see whether acKeyHolderID contains a value. An easy way to do this is to check whether Coalesce([acKeyHolderID],0)=0.

3) If true, popup a message box telling the user they first need to save the main record (the Key-Holder's details).

4) Reject the attempt to enter data into the subview by running the UndoRecord macro action.


If Coalesce([acKeyHolderID],0)=0 Then

   MessageBox

     Message Please save the key-holder details before adding keys by clicking the Save button at the top of the form.

    UndoRecord

End If 

​As long as the user does as they are told all will be well. However (and this is where it is not "user-proof") if they ignore the message and continue to try to enter data into the subview without first saving the main record, the app will get stuck, requiring the user to either refresh the screen or go to another view and then back to this view if they want to continue to enter data here. Also if they click on Cancel at the top of the main record and then repeat their original error of entering data in the main record then attempting to enter data into the subview, the On Current macro will whinge at them that the UndoRecord action is not available at that point.

However, for all practical purposes, the use of this On Current macro seems to be the best way of handling the need to first save a new main record before entering data into the subview.

Note that no similar problem occurs with existing main records as the subview is already aware of the main record's primary key.



Jul 28
Access 2013 web app issues

​Access 2013 web apps are an exciting development, extending Access' reach to the web in a much better manner than Access 2010 web databases. What's more Microsoft seem to be putting some real effort into making them better, including between editions of Microsoft Office. Thus there have been some enhancements to these web apps since the release of Access 2013 itself.

However, there are some issues for developers. Hopefully Microsoft will address these. In the meantime, I am keeping a public list of these issues as and when I come across them. This database can be accessed read-only from elsewhere on this site or by going directly to here​​.

Sep 03
Problems with parameter queries in web databases

Unlike parameter queries in standard Access databases, where it is not always required, it is necessary to explicity declare parameters in ​web queries in Access 2013. If you fail to do so, you'll get an error message something like the following:

Query Parameter Problem.gif

Here is where you need to set them.

Query Parameter Declaration.gif


Aug 30
Access 2013 stealth tables (aka bug)

If you create a table whose name starts with "USys", you will find that it is not visible from the Access 2013 web app either in Access 2013 itself (where it should appear in the Navigation Pane) or, because it isn't visible in the Nav Pane and no view (List or Datasheet) gets automatically as with other tables, from the web UI either. It is, however, visible if you link to it using standard linked tables or using SQL Server Management Studio.​

In a standard Access database, USys... tables are treated as user-defined system tables (as opposed to Access-defined system tables which are MSys....). As such, in a standard Access database, you can change whether to show the table in the Nav Pane or not, but in Access 2013 they appear to hidden forever.

​Not good.

1 - 10Next