Calling a Form with Return Values.

I recently learnt a neat trick for calling a form in Microsoft Access and retrieving values from it. It is really nice to encase the whole thing in a function which retrieves a return value.

Let me show you with a simple example – I want to return an address based on a post code lookup.

First, we need to define an new Type to hold addresses. All address formats are different, but this is mine

Public Type Address
    valid As Boolean 'true of remainder of record holds a valid address, false otherwise
    hseNoStName As String
    address1 As String
    town As String
    county As String
End Type

We then have a function to do the lookup

Public Function getAddress(postcode As String) As Address
    Dim returnAddress As Address
    DoCmd.OpenForm "frmPostCodeSearch",,,,,acDialog,postcode   'call form as dialog,with postcode as openArgs
    If CurrentProject.AllForms("frmPostCodeSearch").isLoaded Then
    ' We found an address, so return it
        returnAddress.valid = true
        returnAddress.hseNoStName = Forms!frmPostCodeSearch.hseNoStName
        returnAddress.address1 = Forms!frmPostCodeSearch.address1
        returnAddress.town = Forms!frmPostCodeSearch.town
        returnAddress.county = Forms!frmPostCodeSearch.county
        DoCmd.close acForm "frmPostCodeSearch",acSaveNo
    Else
        returnAddress.valid = False
    End If
    getAddress = returnAddress
End Sub

I won’t go into the full detail of the post code search as its quite hard. The important bit is what to do if no address matches – So there is a click on an “Abandon” button

Private Sub Abandon_Click()
    DoCmd.Close acForm,"frmPostCodeSearch",acSaveNo
End Sub

And what happens when the user double clicks on field in the record selected

Private Sub hseNoStName_DblClick()
    Me.Dirty = false 'ensures record is not locked
    Me.Visible = false 'causes dialog to return to calling function
End Sub

Author: Alan

I am Alan Chandler.

Leave a Reply

Your email address will not be published. Required fields are marked *