Link to home
Create AccountLog in
Avatar of Annu
AnnuFlag for United Kingdom of Great Britain and Northern Ireland

asked on

List Box control: works in Access 2003, but not in Access 2000

I am trying to get Postcode Anywhere (www.postcodeanywhere.co.uk) address lookup working on my Access 2000 Form.

They have supplied a sample Form which has a some address fields, a find button and a List Box. You type the postcode in the Postcode text box and click Find. The code then populates the List Box, from which you can double click any item to fill in yoour own Form's address fields.

All the VBA code behind the sample Form is below.

When I use this code on my development machine which has Access 2003, everything works fine. But on my clients machine which has Access 2000, I get this error for RemoveItem (0):

line 13: lstWorker.RemoveItem (0)

Compile error: Method or data member not found

I suspect that I may get the same error for lstWorker.AddItem, etc.

Can you help me solve this problem? Thanks.
Option Compare Database
 
Dim rstList As Object
 
Const ACCOUNT_CODE As String = "usernamexxx"
Const LICENSE_KEY As String = "Dxxx-xxxx-xxxx-xxx3"
 
Private Sub btnFind_Click()
    
    'First clear the current list
    numItems = lstWorker.ListCount - 1
    For i = 0 To numItems
        lstWorker.RemoveItem (0)
    Next
    
    'Start the query
    Set rstList = CreateObject("ADODB.Recordset")
    rstList.Open "http://services.postcodeanywhere.co.uk/recordset.aspx?account_code=" & ACCOUNT_CODE & "&license_key=" & LICENSE_KEY & "&action=lookup&type=by_postcode&postcode=" & Postcode
    
    'Check for an error
    If rstList.Fields.Count = 2 Then
        MsgBox rstList.Fields(1)
        Exit Sub
    End If
    
    'Copy the data into the list box
    While Not rstList.EOF
        lstWorker.AddItem (rstList.Fields("description"))
        rstList.MoveNext
    Wend
 
End Sub
 
Private Sub lstWorker_DblClick2(Cancel As Integer)
 
    'Drill down / get the address
    'DoSelect
    
End Sub
 
Public Sub DoSelect()
 
    Dim strUrl As String
    Dim i As Integer
    Dim numItems As Integer
 
    'numItems = lstItems.ListCount - 1
    numItems = lstWorker.ListCount - 1
    For i = 0 To numItems
        'lstItems.RemoveItem (0)
        lstWorker.RemoveItem (0)
    Next
    
    'Build the URL
    strUrl = "http://services.postcodeanywhere.co.uk/recordset.aspx?"
    strUrl = strUrl & "account_code=" & ACCOUNT_CODE
    strUrl = strUrl & "&license_code=" & LICENSE_KEY
    strUrl = strUrl & "&action=lookup&type=by_postcode"
    strUrl = strUrl & "&postcode=" & Postcode
 
    'Get the data from Postcode Anywhere
    Set rstList = CreateObject("adodb.recordset")
    rstList.Open strUrl
    
    'Check for an error
    If rstList.Fields.Count = 2 Then
        MsgBox rstList.Fields(1)
        Exit Sub
    End If
    
    'Copy the data into the list box
    While Not rstList.EOF
        'lstItems.AddItem (rstList.Fields("description"))
        lstWorker.AddItem (rstList.Fields("description"))
        rstList.MoveNext
    Wend
End Sub
 
Private Sub lstWorker_DblClick(Cancel As Integer)
On Error GoTo err:
 
    'Dim rst As Object
    Dim strId As String
    Dim strUrl As String
    
    'Get the id of the item
    rstList.MoveFirst
    rstList.Move lstWorker.ListIndex
    strId = rstList.Fields("id")
    
    'Build the URL
    strUrl = "http://services.postcodeanywhere.co.uk/recordset.aspx?"
    strUrl = strUrl & "account_code=" & ACCOUNT_CODE
    strUrl = strUrl & "&license_code=" & LICENSE_KEY
    strUrl = strUrl & "&action=fetch"
    strUrl = strUrl & "&id=" & strId
    
    'Get the data from postcode anywhere
    Dim rstFetch As Object
    Set rstFetch = CreateObject("adodb.recordset")
    rstFetch.Open strUrl
    
    'Check for an error
    If rstFetch.Fields.Count = 2 Then
        MsgBox rstFetch.Fields(1)
        Exit Sub
    End If
    
    'Copy the data into the list box
    If Not rstFetch.EOF Then
        Me.Company = rstFetch.Fields("organisation_name")
        Me.Address1 = rstFetch.Fields("line1")
        Me.Address2 = rstFetch.Fields("line2")
        Me.Address3 = rstFetch.Fields("line3")
        'Me.Address4 = rstFetch.Fields("line4")
        Me.Town = rstFetch.Fields("post_town")
        Me.County = rstFetch.Fields("county")
        Me.Postcode = rstFetch.Fields("postcode")
    End If
    
    Exit Sub
    
err:
    
    Exit Sub
 
End Sub
 
 
 
Private Sub DrawList()
 
    If rstList.Fields.Count = 2 Then
        MsgBox rstList.Fields(1)
        Exit Sub
    End If
    
    'Update the control
    'Set Me.lstWorker.Recordset = rstList
    'Me.lstWorker.ColumnCount = 3
    'Me.lstWorker.ColumnWidths = "0;0;"
    'Me.lstWorker.BoundColumn = 1
    'Me.lstWorker.Requery
    
End Sub

Open in new window

Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Neither method is in Access 2000, so I think that the software hasn't been verified for that version.

Avatar of Annu

ASKER

Thanks GrahamSkan.

Is there no way to get the same outcome using a differnt way?
As far as I remember you had to fill the list manually at design time or bind it to a query or table, so it might mean creating an extra table in the database.
I'll have a go and see what needs to be done.
Avatar of Annu

ASKER

I might not be licenced to store this data in a table!

Is there any other way?

Thanks.
I seems to be only field names which probably don't change very often.

I haven't got it working yet, but it might be a good idea if you make a note of all the names under 2003,  recreate the listbox in the 2000 system, type the names in at that point and remove the code which tries to change them.

The site home page says that it has free support. Have you contacted them?
Sorry. That I now have it working in 2003, so I see what is supposed to happen.

I think you need to use the RowSource property.

Set the  RowSourceType = "Value List"

and concatenate the data into a comma-separated string
    'First clear the current list
    lstWorker.RowSourceType = "Value List"
    lstWorker.RowSource = ""
 
'...
 
    While Not rstList.EOF
        lstWorker.RowSource = lstWorker.RowSource & "," & rstList.Fields("description")
        rstList.MoveNext
    Wend

Open in new window

Avatar of Annu

ASKER

Yes, I have contacted their support and they couldn't figure out the problem.

Anyway, I'm trying out your solution now...

Thanks.
Avatar of Annu

ASKER

Sorry, Graham. I'm a bit lost. Can you please tell me which bits of code to put where... I am not very good at this.

Thanks.
Avatar of Annu

ASKER

Please ignore that last comment, Graham.

It looks like it's working! Thanks!

I'll get back ...
I don't have the form, so I can't test it all, but this is the code for the button click event
Private Sub btnFind_Click()
    'First clear the current list
    lstWorker.RowSourceType = "Value List"
    lstWorker.RowSource = ""
    
    'Start the query
    Set rstList = CreateObject("ADODB.Recordset")
    rstList.Open "http://services.postcodeanywhere.co.uk/recordset.aspx?account_code=" & ACCOUNT_CODE & "&license_key=" & LICENSE_KEY & "&action=lookup&type=by_postcode&postcode=" & POSTCODE
    
    'Check for an error
    If rstList.Fields.Count = 2 Then
        MsgBox rstList.Fields(1)
        Exit Sub
    End If
    
    'Copy the data into the list box
    Do Until rstList.EOF
        If lstWorker.RowSource = "" Then 'first item needs no comma
            rstList.Fields ("description")
        Else
            lstWorker.RowSource = lstWorker.RowSource & "," & rstList.Fields("description")
        End If
        rstList.MoveNext
    Loop
End Sub

Open in new window

You will be getting a blank row at the beginning of the list. Apart from some changes of code style, the last version also prevents that by avoiding a comma at the start of the string.
Avatar of Annu

ASKER

When I used this code I got this problem:

Code:
While Not rstList.EOF
        lstWorker.RowSource = lstWorker.RowSource & "," & rstList.Fields("description")
        rstList.MoveNext
    Wend

Problem: not all the addresses were appearing in the list. Some house numbers that I know should have been in the list did not appear. And I when I double clicked an item in the list, a different item appeared in my Form text boxes.

When I used this code I got this problem:

Code:
Do Until rstList.EOF
        If lstWorker.RowSource = "" Then 'first item needs no comma
            rstList.Fields ("description")
        Else
            lstWorker.RowSource = lstWorker.RowSource & "," & rstList.Fields("description")
        End If
        rstList.MoveNext
    Loop

Problem: Error 450: wrong number of arguments or invalid properry assignement
for this line: rstList.Fields ("description")

Can you help? Thanks.
Sorry for the delay, and sorry for the coding error. I edited the code after testing, and before posting.

I have corrected that code problem (Error 450).

If you are still getting problems with the missing house numbers, give an instance of the post code.
Private Sub btnFind_Click()
    'First clear the current list
    lstWorker.RowSourceType = "Value List"
    lstWorker.RowSource = ""
    
    'Start the query
    Set rstList = CreateObject("ADODB.Recordset")
    rstList.Open "http://services.postcodeanywhere.co.uk/recordset.aspx?account_code=" & ACCOUNT_CODE & "&license_key=" & LICENSE_KEY & "&action=lookup&type=by_postcode&postcode=" & PostCode
    
    'Check for an error
    If rstList.Fields.Count = 2 Then
        MsgBox rstList.Fields(1)
        Exit Sub
    End If
    
    'Copy the data into the list box
    Do Until rstList.EOF
        If lstWorker.RowSource = "" Then 'first item needs no comma
            lstWorker.RowSource = rstList.Fields("description")
        Else
            lstWorker.RowSource = lstWorker.RowSource & "," & rstList.Fields("description")
        End If
        rstList.MoveNext
    Loop
End Sub

Open in new window

Incidentally, I asked a moderator to obscure the license information in your original code.
Avatar of Annu

ASKER

Thank Graham. Yes, now the error 450 is solved, but now I have the other problem:

Problem: not all the addresses were appearing in the list. Some house numbers that I know should have been in the list did not appear. And I when I double clicked an item in the list, a different item appeared in my Form text boxes.

Thanks for getting the license key obscured. You were right to do that.

In the meantime, PostcodeAnywhere have got back to me (I gave them your first solution to check out). Their code is below. Can you please tell me what's the difference between yours and theirs? Thanks.
Private Sub btnFind_Click()
 
    Dim IsFirst As Boolean
 
    'First clear the current list
    lstWorker.RowSourceType = "Value List"
    lstWorker.RowSource = ""
 
    'Start the query
    Set rstList = CreateObject("ADODB.Recordset")
    rstList.Open "http://services.postcodeanywhere.co.uk/recordset.aspx?account_code=" & ACCOUNT_CODE & "&license_key=" & LICENSE_KEY & "&action=lookup&type=by_postcode&postcode=" & Postcode
 
    'Check for an error
    If rstList.Fields.Count = 2 Then
        MsgBox rstList.Fields(1)
        Exit Sub
    End If
 
    'Copy the data into the list box
    IsFirst = True
    While Not rstList.EOF
        If IsFirst Then
            lstWorker.RowSource = rstList.Fields("description")
            IsFirst = False
        Else
            lstWorker.RowSource = lstWorker.RowSource & "," & rstList.Fields("description")
        End If
        rstList.MoveNext
    Wend
 
End Sub

Open in new window

It is almost identical. The only difference is the way that they decide if it is the first house to be added, and doesn't need a comma in front of it.

They have a boolean flag that is set to True at the start of the process of adding the houses to the list, and set false.

My code check if the string is empty and if so simply appends the first house instead of a comma and then the house.

As it stands, I can only check for missing houses. I don't know is supposed to happen after that. However tell me one of the failing postcodes and I will try to see what the problem is. The answer may fix both symptoms.
Avatar of Annu

ASKER

OK, thanks Graham.

Right, still getting that same problem with both codes (yours and Postcode Anywhere's).

Below is the code they have given me for double-clicking an address in the list to move it to my own text boxes.

Can you spot the error? Thanks.
Private Sub lstWorker_DblClick(Cancel As Integer)
 
    On Error GoTo err:
 
    'Dim rst As Object
    Dim strId As String
    Dim strUrl As String
    
    'Get the id of the item
    rstList.MoveFirst
    rstList.Move lstWorker.ListIndex
    strId = rstList.Fields("id")
    
    'Build the URL
    strUrl = "http://services.postcodeanywhere.co.uk/recordset.aspx?"
    strUrl = strUrl & "account_code=" & ACCOUNT_CODE
    strUrl = strUrl & "&license_code=" & LICENSE_KEY
    strUrl = strUrl & "&action=fetch"
    strUrl = strUrl & "&id=" & strId
    
    'Get the data from postcode anywhere
    Dim rstFetch As Object
    Set rstFetch = CreateObject("adodb.recordset")
    rstFetch.Open strUrl
    
    'Check for an error
    If rstFetch.Fields.Count = 2 Then
        MsgBox rstFetch.Fields(1)
        Exit Sub
    End If
    
    'Copy the data into the list box
    If Not rstFetch.EOF Then
        Me.txtNameOrgNameChange = rstFetch.Fields("organisation_name")
        Me.txtAddrLine1Change = rstFetch.Fields("line1")
        Me.txtAddrLine2Change = rstFetch.Fields("line2")
        Me.txtAddrLine3Change = rstFetch.Fields("line3")
        Me.txtTownCityChange = rstFetch.Fields("post_town")
        Me.txtCountyStateChange = rstFetch.Fields("county")
        Me.txtPostcodeChange = rstFetch.Fields("postcode")
    End If
    
    Exit Sub
    
err:
    
    Exit Sub
    
End Sub

Open in new window

I can't reproduce the problem.
Can you cite an instance of a post-code that fails and what is missing from it?
Avatar of Annu

ASKER

I tried E9 5AE, and saw that 155A Wick Road is missing (amongst others).

Also, when I double-clicked on 155 Wick Road, 125A Wick Road came up on my Form.

Thanks.
Sorry. I can't reproduce that symptom.

I have added seven text boxes to the form and tried it under Access 2003 and Access 2000. In both versions, 155A appeared properly.

Perhaps I should check with the same form that you are using instead of trying to build it myself.
Could you attach it here? I don't know how the site sent it to you, but I suggest exporting it to an otherwise empty database and attaching that.
Avatar of Annu

ASKER

Thanks Graham.

I've attached a new mdb with the Form that I am using. The Form is for testing, I actually plan to make the list box appear in a popup Form and make the user choose an address from there. It then populates the address boxes on this Form attached.
EE-Graham.pdf
Avatar of Annu

ASKER

BTW, the file is a zip file. Pls change the extension.
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Annu

ASKER

Spot on, Graham!

Thanks!