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

[vb]
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
[/vb]

We then have a function to do the lookup

[vb]
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
[/vb]

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

[vb]
Private Sub Abandon_Click()
DoCmd.Close acForm,"frmPostCodeSearch",acSaveNo
End Sub
[/vb]

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

[vb]
Private Sub hseNoStName_DblClick()
Me.Dirty = false ‘ensures record is not locked
Me.Visible = false ’causes dialog to return to calling function
End Sub
[/vb]

Author: Alan

I am Alan Chandler.

Leave a Reply

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