Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    56

    Unanswered: Another Postcode problem (combobox cant handle all of them)

    Ok, so i have about 135,000 postcodes in my database now, in the PostcodesTBL table,

    the reason its in the table and not on a datalink from excel is because when trying the link to excel it instantly made access so slow, and it crashed.. but with the postcodes in the table in access, it seems to handle it fine.. however,

    on my form, i have the combobox that gets the data from postcodesTBL, and stores it in StockTrackerTBL when the record is complete and saved, however, when you click the dropdown box, it can only handle a certain amount in the list, when i type in "SE18" and look for it in the list, it isnt there, and when i enter a full postcode like SE18 7NN, it says its not in the list and can't store that value..

    is there a workaround for this that i'm not thinking about? like having a text box that will relate to the PostcodesTBL and just typing the postcode in the Text box will recognise that SE18 is in the Massive list?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Change the ComboPostcodes_KeyPress procedure formerly provided, like this:
    Code:
    Private Sub ComboPostcodes_KeyPress(KeyAscii As Integer)
    
        Const c_SQL = "SELECT PostCodesTBL.PostCode, PostCodesTBL.Town, PostCodesTBL.Borough " & _
                      "FROM PostCodesTBL WHERE PostCodesTBL.PostCode LIKE '@*' " & _
                      "ORDER BY PostCodesTBL.PostCode; "
        Const c_KeyPlus As Integer = 43 ' KeyAscii 43 = '+'
        
        If Me.ComboPostcodes.Text = "" Then
            Me.ComboPostcodes.RowSource = Replace(c_SQL, "@", Chr(KeyAscii))
            Me.ComboPostcodes.Dropdown
        Else
            If KeyAscii <> c_KeyPlus Then Exit Sub
            If Me.ComboPostcodes.SelStart > 0 Then
                Me.ComboPostcodes.Text = Left(Me.ComboPostcodes.Text, Me.ComboPostcodes.SelStart)
                SendKeys "{ESC}"
            End If
        End If
        
    End Sub
    When the TextBox part of the Combo is empty, typing a letter into it will limit the contents of the list to the codes beginnning with the typed letter, hence shortening the number of rows it contains.
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    56

    :-)

    i love you more than i love porn..

    bloody works a treat! thanks ever so much, however, im trying to interpret the code so that i can understand it? for future reference,

    Private Sub ComboPostcodes_KeyPress(KeyAscii As Integer)

    Code:
    Private Sub ComboPostcodes_KeyPress(KeyAscii As Integer)
    
        Const c_SQL = "SELECT PostCodesTBL.PostCode, PostCodesTBL.Town, PostCodesTBL.Borough " & _
                      "FROM PostCodesTBL WHERE PostCodesTBL.PostCode LIKE '@*' " & _
                      "ORDER BY PostCodesTBL.PostCode; "
        Const c_KeyPlus As Integer = 43 ' KeyAscii 43 = '+'
        
        If Me.ComboPostcodes.Text = "" Then
            Me.ComboPostcodes.RowSource = Replace(c_SQL, "@", Chr(KeyAscii))
            Me.ComboPostcodes.Dropdown
        Else
            If KeyAscii <> c_KeyPlus Then Exit Sub
            If Me.ComboPostcodes.SelStart > 0 Then
                Me.ComboPostcodes.Text = Left(Me.ComboPostcodes.Text, Me.ComboPostcodes.SelStart)
                SendKeys "{ESC}"
            End If
        End If
        
    End Sub
    my questions are:

    what does " _ & mean and do?
    how does LIKE '@*' work and whats it mean?
    "IF Me.ComboPostcodes = "" Then (confuses me, is that saying, if the text = nothing Then...?
    Selstart > 0 whats that mean? lol you know any sites that would help me understand things a bit better?

    thanks man, cheers for everythin

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by jackjsmith88 View Post
    what does " _ & mean and do?
    the underscore ("_") is a continuation character. It indicates that the current statement continues on the next line. You can write:
    Code:
    "SELECT PostCodesTBL.PostCode, PostCodesTBL.Town, PostCodesTBL.Borough FROM PostCodesTBL WHERE CodesTBL.PostCode LIKE '@*' ORDER BY PostCodesTBL.PostCode;"
    on a single line, but it's less readable.

    Quote Originally Posted by jackjsmith88 View Post
    how does LIKE '@*' work and whats it mean?
    The ampersand ("@") is used as a placeholder (it could be any character but I selected one that should never appear in a Postcode). It is replaced by the actual character that was typed in the expression:
    Code:
    Replace(c_SQL, "@", Chr(KeyAscii))
    KeyAscii being the numeric code of the character that was typed, the Chr() function is used to get its corresponding character (string).

    Quote Originally Posted by jackjsmith88 View Post
    "IF Me.ComboPostcodes = "" Then (confuses me, is that saying, if the text = nothing Then...?
    You only want to change the RowSource property of the combo (i.e. what determines what the list contains) when the first character is keyed in. And when the first character is keyed in, the Textbox part of the combo is empty, i.e. it contains a zero-length string --> "", hence the test. I could have written:
    Code:
    If Len(Me.ComboPostcodes.Text) = 0
    Quote Originally Posted by jackjsmith88 View Post
    Selstart > 0 whats that mean? lol you know any sites that would help me understand things a bit better?
    Simply select the word ("SelStart") or double-click on it and press the F1 key. This will open the Access Help system on the page explaining what the word means and how it is used. This works for almost any reserved word in Access.

    Quote Originally Posted by jackjsmith88 View Post
    thanks man, cheers for everythin
    You're welcome !
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by Sinndho View Post
    The ampersand ("@")
    Sorry to butt in, but I thought I'd just point out, that's the 'ampersat' (or commonly called the 'at sign'), not an 'ampersand'... An ampersand is the character abbreviating the word 'and', i.e. '&'.

    Sorry, I'm one of those people when it comes to English.


    Source: Ampersand - Wikipedia, the free encyclopedia
    Looking for the perfect beer...

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're right, of course: it's the arobas.
    Have a nice day!

  7. #7
    Join Date
    Oct 2011
    Posts
    56
    lol..

    we have a genius and now we have a perfectionist too XD you 2 would make a great team,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •