If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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?

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.

Originally Posted by jackjsmith88

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).

Originally Posted by jackjsmith88

"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

Originally Posted by jackjsmith88

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.