Quickaddress with Microsoft Access

Using Quickaddress with Microsoft Access

QuickAddress http://www.qas.com/uk/ allows you to capture accurate name and address details from a postcode, partial address or grid refence. The package 'Locator' is a set of functions to convert grid references to Postcodes. Examples are only given in Visual Basic but not for use from Microsoft Access so if anyone is trying to work it out then the following may help because the manuals are not clear.

Installation

Make sure that the file QALCTEB.DLL is in the Windows system32 directory
The configuration file qaddress.ini is in the project directory and contains the line DataDir=c:\qaddress (the name of the folder where the grid data is stored) and Loctype=10m (resolution of grid references)
The module QALCTEB.BAS may be pasted as a new module but it uses Visual Basic functions to display form objects so can be confusing
Assume that a table exists with long integer fields Northings, Eastings, autocounter ID and blank text field postcode
Since you need to identify the data file, it is not possible to use the functions directly in a query
Create a form showing all the fields and two buttons:
Search_button search and modify a singe postcode depending on changed values of grid references. This calls function QALoc_Startup, QALocatorToScreen (my subroutine), QAErrorMessage, and QALoc_Shutdown
Updatebutton update all unknown postcodes. This calls function QALoc_Startup, QALocatorUpdate (my subroutine), QAErrorMessage and QALoc_Shutdown
QALocatorUpdate and QALocatorUpdate call QALoc_SearchRadius

Note that the sql statements are on one line

 
Option Compare Database
'**** Visual Basic (32 bit) header file for QALCTEB.DLL ****
Function StringCToBas(sInput As String) As String
  Dim iPos As Integer
  iPos = InStr(sInput, Chr$(0))
  If (iPos = 0) Then
    StringCToBas = Trim(sInput)
  Else
    StringCToBas = Trim(Left$(sInput, iPos - 1))
  End If
End Function

Private Sub ExitButton_Click()
Dim formname As TextBox
formname = "grid refs and postcodes"
  Unload formname
End Sub

Private Sub SearchButton_Click()
  Dim lEasting As Long
  Dim lNorthing As Long
  Dim lRadius As Long
  Dim status As Long
  Dim sMsg As String * 100
  Dim inifile As String
  Dim section As String
  inifile = "c:\quickaddress\qaddress"
  section = "qadefault"
  lEasting = Val(Eastingtextbox)
  lNorthing = Val(Northingtextbox)
  lRadius = Val(radiustextbox)
  status = QALoc_Startup(inifile, section)
  
  If (status <> 0) Then
    Call QAErrorMessage(status, sMsg, 100)
    MsgBox "Error calling QALoc_Startup - " & sMsg
    MsgBox status
  Else
    Call QALocatorToScreen(lEasting, lNorthing, lRadius)
    status = QALoc_Shutdown()
    If (status <> 0) Then
      Call QAErrorMessage(status, sMsg, 100)
      MsgBox "Error calling QALoc_Shutdown - " & sMsg
    End If
  End If
  searchbutton.Enabled = True
End Sub

Sub QALocatorToScreen(lEasting As Long, lNorthing As Long, lRadius As Long)
  Dim iStatus As Long
  Dim lLocCount As Long
  Dim lCounter As Long
  Dim sMsg As String * 100
  Dim sPostcode As String * 10
  Dim iDPP As Long
  Dim lDistance As Long
  Dim display As String
  Dim sql As String
  display = Str$(lEasting) & "," & Str$(lNorthing) & "," & Str$(lRadius)
  MsgBox display, vbOKOnly, "easting,northing,radius"
  iStatus = QALoc_SearchRadius(lEasting, lNorthing, lRadius)

  If (iStatus = 0) Then
    display = "SEARCH : 0 : Exact matches were found"
    MsgBox display, vbOKOnly, "search 0"
  ElseIf (iStatus = 1) Then
    display = "SEARCH : 1 : No exact matches were found"
    MsgBox display, vbOKOnly, "search 1"
  ElseIf (iStatus = 2) Then
 Display= "SEARCH : 2 : Nearest matches are outside the radius specified"
  MsgBox display, vbOKOnly, "search 2"
  Else
    Call QAErrorMessage(iStatus, sMsg, 100)
    display = "SEARCH : " & Str$(iStatus) & " : Error - " & sMsg
  MsgBox display, vbOKOnly, "error"
  End If
  If (iStatus >= 0) Then
     lLocCount = QALoc_MatchCount
     If (lLocCount < 0) Then
        iStatus = lLocCount
        Call QAErrorMessage(iStatus, sMsg, 100)
        display = "COUNT : " & Str$(iStatus) & " : Error - " & sMsg
     MsgBox display, vbOKOnly, "error"
     Else
        display = "COUNT : " & Str$(lLocCount)
        MsgBox display, vbOKOnly, "progress"
'show the nearest matches
        For lCounter = 0 To (lLocCount - 1)
          iStatus = QALoc_Get(lCounter, sPostcode, iDPP, lDistance)
          'Need to convert C string to Visual Basic String
          display = Str$(lCounter) & ", " & StringCToBas(sPostcode) & ", " & Str$(iDPP) & ", " & Str$(lDistance)
       MsgBox display, vbOKOnly, "count,   postcode, DPP, distxit_quickchkbutton_Click:
      If icounter = 0 Then
        sql = "update [grid refs and postcodes] set postcode=""" & StringCToBas(sPostcode) & """ where eastings=" & Str$(lEasting) / 10 & " and northings=" & Str$(lNorthing) / 10
        DoCmd.RunSQL sql, -1
End If
        Next lCounter
     End If
   End If
End Sub

Private Sub QALocatorupdate()
 Dim iStatus As Long
  Dim lLocCount As Long
  Dim lCounter As Long
  Dim sMsg As String * 100
  Dim sPostcode As St QALoc_SearchRadius(Eastings, Northings, radius)
      iStatus = QALoc_Get(0, sPostcode, iDPP, lDistance)
'check update is doing what it should be 
 MsgBox Northings, vbOKOnly, "northings update"
               MsgBox Eastings, vbOKOnly, "eastings update"
                      MsgBox sPostcode, vbOKOnly, "postcode update"
  MsgBox StringCToBas(sPostcode), vbOKOnly, "stringpostcode update"
  sql = "update [grid refs and postcodes] set postcode=""" 
   & StringCToBas(sPostcode) & """ where eastings=" & Eastings / 10 
   & " and northings=" & Northings / 10 & " and postcode is null"
   MsgBox sql, vbkonly, "sql  "
        DoCmd.RunSQL sql, -1
        
Do Until mygrid.EOF
DoCmd.SetWarnings False
ID = mygrid!ID
Northings = mygrid!Northings
Eastings = mygrid!Eastings
Northings = Northings * 10
Eastings = Eastings * 10
radius = 0
 display = Str$(lEasting) & "," & Str$(lNorthing) & "," & Str$(lRadius)
  iStatus = QALoc_SearchRadius(Eastings, Northings, radius)
  If (iStatus >= 0) Then
     lLocCount = QALoc_MatchCount
 If (iStatus >= 0) Then
     lLocCount = QALoc_MatchCount
            iStatus = QALoc_Get(0, sPostcode, iDPP, lDistance)
          'Need to convert C string to Visual Basic String
          sql = "update [grid refs and postcodes] set postcode=""" 
          & StringCToBas(sPostcode) & """ where eastings=" & Eastings / 10 
          & " and northings=" & Northings / 10 & " and postcode is null"
   DoCmd.RunSQL sql, -1
End If
 mygrid.MoveNext
Loop
mygrid.Close
On Error GoTo Err_update_command_Click
Exit_update_command_Click:
    Exit Sub
Err_update_command_Click:
    Resume Exit_update_command_Click
End Sub

Public Sub updatebutton_click()
  Dim lEasting As Long
  Dim lNorthing As Long
  Dim lRadius As Long
  Dim status As Long
  Dim sMsg As String * 100
  Dim inifile As String
  Dim section As String
  inifile = "c:\quickaddress\qaddress"
  section = "qadefault"
  lEasting = Val(Eastingtextbox)
  lNorthing = Val(Northingtextbox)
  lRadius = Val(radiustextbox)
  status = QALoc_Startup(inifile, section)
  If (status <> 0) Then
    Call QAErrorMessage(status, sMsg, 100)
    MsgBox "Error calling QALoc_Startup - " & sMsg
    MsgBox status
  Else
    Call QALocatorupdate
    status = QALoc_Shutdown()
    If (status <> 0) Then
      Call QAErrorMessage(status, sMsg, 100)
      MsgBox "Error calling QALoc_Shutdown - " & sMsg
    End If
  End If
  searchbutton.Enabled = True
End Sub