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 = Forms!
returnAddress.county = Forms!frmPostCodeSearch.county
DoCmd.close acForm "frmPostCodeSearch",acSaveNo
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 *