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