Filtering

[Bob Matthews]

Hi John

Step 3: In the combo's Change event procedure, you could also use a single
line. The code below illustrates how to do a little more, blocking initial
spaces, and forcing "Mt " to "Mount ":
Dim cbo As ComboBox ' Suburb combo.
Dim sText As String ' Text property of combo.

Set cbo = Me.Suburb
sText = cbo.Text
Select Case sText
Case " " ' Remove initial space
cbo = Null
Case "MT " ' Change "Mt " to "Mount ".
cbo = "MOUNT "
cbo.SelStart = 6
Call ReloadSuburb(sText)
Case Else ' Reload RowSource data.
Call ReloadSuburb(sText)
End Select
Set cbo = Nothing

Step 4: To assign the State and Postcode, add this code to the combo's
AfterUpdate event procedure:

Dim cbo As ComboBox
Set cbo = Me.Suburb
If Not IsNull(cbo.Value) Then
If cbo.Value = cbo.Column(0) Then
If Len(cbo.Column(1)) > 0 Then
Me.State = cbo.Column(1)
End If
If Len(cbo.Column(2)) > 0 Then
Me.Postcode = cbo.Column(2)
End If
Else
Me.Postcode = Null
End If
End If
Set cbo = NothingBob M"John W. Vinson"

Thanks John

I have added those two lines [I did think that they appeared
automatically,
which led me to believe I was doing something wrong]

Current state of Affairs:

I have loaded up the Australian Postcode Table
I open the Form
I enter "Ni" and the dropdown box shows one line which is from the code in
Step 3
viz. SELECT Suburb, State, Postcode FROM Postcodes WHERE (False)

I enter a further letter "Nig" and an additional line appears
State Postcode

Rather than my digging through multiple posts and hundreds of lines of
(possibly long obsolete) code, could you post the actual code for the
combo's
event?

 

[David W. Fenton]

The form already has a Module; you don't need to "add a module".

Hmm. If I create a new form and don't attempt to view its code or
add any code to its events, the HasModule property remains NO, which
means it *doesn't* have a module when it's created. It seems to me
that you have to click the VIEW CODE toolbar button (which opens the
form's code module, and after that the HasModule property is true),
or create an event procedure for one of the form's events (or for a
control on the form).

Basically, if you need a module, it will be created for you when you
attempt to do something that requires a module, so you don't
actually ever create the form's class module yourself.

 

[David W. Fenton]

Access doesn't automatically put those two lines in... but it
really should;

What version of Access? Every version I've ever used has put Option
Compare Database at the top of every module, and only A2000 and
A2002 don't insert Option Explicit in the default configuration. I
certainly change any installation of either of those two versions of
Access to insert Option Explicit by default.

 

[David W. Fenton]

I have added those two lines [I did think that they appeared
automatically, which led me to believe I was doing something
wrong]

If they aren't in all newly created modules automatically, then
you've got your installation of Access misconfigured.

 

[Bob Matthews]

Hi David

I am using Access 2007

Bob

What version of Access? Every version I've ever used has put Option
Compare Database at the top of every module, and only A2000 and
A2002 don't insert Option Explicit in the default configuration. I
certainly change any installation of either of those two versions of
Access to insert Option Explicit by default.

 

[David W. Fenton]

I am using Access 2007

My installation of A2007 has the default settings and it inserts
both settings at the top of all modules.