Filtering

[Bob Matthews]

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the available
records - based on surname.
If the number of records is still too large for several letters of the
alphabet, what is the easiest way to filter further - say on 2 or 3 letters
of a surname?

Bob

 

[Jeanette Cunningham]

You might be interested in this for Combos with 10's of thousands of records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Bob Matthews]

Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I get
the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

 

[Bob Matthews]

Hi again

the error message is 'ms Office Access can't find the object 'Call
ReloadSuburb(Nz(Me.'

the actual call is as follows:-

Call ReloadSuburb(Nz(Me.Suburb, ""))

Bob

 

[Jeanette Cunningham]

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user chooses a
suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your form?s
module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Bob Matthews]

Thanks Jeanette

But I am initially making sure that the example works and so I have a table
set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user chooses
a suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your form?s
module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I get
the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

 

[Jeanette Cunningham]

OK
The function that your code can't find is this function
Function ReloadSuburb(sSuburb As String)

Did you put the code for that function in the General Declarations section
of the form's module?
If you have done that, try this.

Open the find and replace dialog in the code window
Type
Function ReloadSuburb
into the find part, set it to search the current module and click search.

Did you find that function in the general declarations section of the code?

Maybe you have a simple spelling mistake and that is confusing access?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Jeanette

But I am initially making sure that the example works and so I have a
table set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user chooses
a suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your form?s
module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I
get the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

You might be interested in this for Combos with 10's of thousands of
records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the available
records - based on surname.
If the number of records is still too large for several letters of the
alphabet, what is the easiest way to filter further - say on 2 or 3
letters of a surname?

Bob

 

[Bob Matthews]

Hi again

Yes I believe that the code is in the correct place
Did the find exercise and yes it found it ????

Not sure what I have done wrong...

Bob

OK
The function that your code can't find is this function
Function ReloadSuburb(sSuburb As String)

Did you put the code for that function in the General Declarations section
of the form's module?
If you have done that, try this.

Open the find and replace dialog in the code window
Type
Function ReloadSuburb
into the find part, set it to search the current module and click search.

Did you find that function in the general declarations section of the
code?

Maybe you have a simple spelling mistake and that is confusing access?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Jeanette

But I am initially making sure that the example works and so I have a
table set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user
chooses a suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something
like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your form?s
module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I
get the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

You might be interested in this for Combos with 10's of thousands of
records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the
available records - based on surname.
If the number of records is still too large for several letters of
the alphabet, what is the easiest way to filter further - say on 2 or
3 letters of a surname?

Bob

 

[Bob Matthews]

What may seem a silly question...............

How do you add a module to a form exactly?

Bob

Hi again

Yes I believe that the code is in the correct place
Did the find exercise and yes it found it ????

Not sure what I have done wrong...

Bob

OK
The function that your code can't find is this function
Function ReloadSuburb(sSuburb As String)

Did you put the code for that function in the General Declarations
section of the form's module?
If you have done that, try this.

Open the find and replace dialog in the code window
Type
Function ReloadSuburb
into the find part, set it to search the current module and click search.

Did you find that function in the general declarations section of the
code?

Maybe you have a simple spelling mistake and that is confusing access?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Jeanette

But I am initially making sure that the example works and so I have a
table set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user
chooses a suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something
like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your form?s
module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I
get the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

You might be interested in this for Combos with 10's of thousands of
records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the
available records - based on surname.
If the number of records is still too large for several letters of
the alphabet, what is the easiest way to filter further - say on 2
or 3 letters of a surname?

Bob

 

[Jeanette Cunningham]

Please copy and paste the code that you have in the current event and post
back here.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi again

Yes I believe that the code is in the correct place
Did the find exercise and yes it found it ????

Not sure what I have done wrong...

Bob

OK
The function that your code can't find is this function
Function ReloadSuburb(sSuburb As String)

Did you put the code for that function in the General Declarations
section of the form's module?
If you have done that, try this.

Open the find and replace dialog in the code window
Type
Function ReloadSuburb
into the find part, set it to search the current module and click search.

Did you find that function in the general declarations section of the
code?

Maybe you have a simple spelling mistake and that is confusing access?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Jeanette

But I am initially making sure that the example works and so I have a
table set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user
chooses a suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something
like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your form?s
module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I
get the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

You might be interested in this for Combos with 10's of thousands of
records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the
available records - based on surname.
If the number of records is still too large for several letters of
the alphabet, what is the easiest way to filter further - say on 2
or 3 letters of a surname?

Bob

 

[Bob Matthews]

Here is the code :-

Call ReloadSuburb(Nz(Me.Suburb, ""))

Bob

Please copy and paste the code that you have in the current event and post
back here.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi again

Yes I believe that the code is in the correct place
Did the find exercise and yes it found it ????

Not sure what I have done wrong...

Bob

OK
The function that your code can't find is this function
Function ReloadSuburb(sSuburb As String)

Did you put the code for that function in the General Declarations
section of the form's module?
If you have done that, try this.

Open the find and replace dialog in the code window
Type
Function ReloadSuburb
into the find part, set it to search the current module and click
search.

Did you find that function in the general declarations section of the
code?

Maybe you have a simple spelling mistake and that is confusing access?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Thanks Jeanette

But I am initially making sure that the example works and so I have a
table set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user
chooses a suburb (city).
The code below goes into the General declarations section of your form
module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's
names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something
like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your
form?s module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form I
get the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

message You might be interested in this for Combos with 10's of thousands of
records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the
available records - based on surname.
If the number of records is still too large for several letters of
the alphabet, what is the easiest way to filter further - say on 2
or 3 letters of a surname?

Bob

 

[Jeanette Cunningham]

How to add code to a form's module?
In design view of the form, go Alt+V+C
to open the code window for that form.


To try to find out what is going wrong:
On the current event, add this line to the code

Debug.Print "Me.Suburb", Me.Suburb
Call ReloadSuburb(Nz(Me.Suburb, ""))

Put a break point on the line Debug. ....
by clicking in the far left margin and you will see that line colored in
red.

Now close the code window and open your form in normal view.
Try to choose a suburb from the drop down.
Access will open the code window when the code gets to that break point.

Post back what access prints in the code window.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Here is the code :-

Call ReloadSuburb(Nz(Me.Suburb, ""))

Bob

Please copy and paste the code that you have in the current event and
post back here.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi again

Yes I believe that the code is in the correct place
Did the find exercise and yes it found it ????

Not sure what I have done wrong...

Bob

OK
The function that your code can't find is this function
Function ReloadSuburb(sSuburb As String)

Did you put the code for that function in the General Declarations
section of the form's module?
If you have done that, try this.

Open the find and replace dialog in the code window
Type
Function ReloadSuburb
into the find part, set it to search the current module and click
search.

Did you find that function in the general declarations section of the
code?

Maybe you have a simple spelling mistake and that is confusing access?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Thanks Jeanette

But I am initially making sure that the example works and so I have a
table set up as instructed..............
but I get the error message??

Bob

Here is the code for ReloadSuburb.
You will see that it sets the row source for the combo where user
chooses a suburb (city).
The code below goes into the General declarations section of your
form module.
That means it goes at the top of the code page, just under
Option Compare Database
Option Explicit.

However you are not dealing with suburbs, you are using people's
names.
You need to make some changes to the code for ReloadSuburb so that
it loads your combo with people's name.
Perhaps you have already done this and called the function something
like
ReloadNames (sName As String)?

If so, in the current event for your form, instead of code which says
Call ReloadSuburb(Nz(Me.Suburb, ""))
You would put

Call ReloadNames(Nz(Me.[NameOfYourCombo], ""))


the code from Allen's website-------------
Step 1: Paste this into the General Declarations section of your
form?s module:

Dim sSuburbStub As String
Const conSuburbMin = 3


Function ReloadSuburb(sSuburb As String)
Dim sNewStub As String ' First chars of Suburb.Text

sNewStub = Nz(Left(sSuburb, conSuburbMin),"")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.Suburb.RowSource = "SELECT Suburb, State, Postcode FROM
Postcodes WHERE (Suburb Like """ & _
sNewStub & "*"") ORDER BY Suburb, State, Postcode;"
sSuburbStub = sNewStub
End If
End If
End Function
end Allen's code -----------



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi Jeanette

Thank you for pointing me in this direction.....looks idea

I have followed the instructions I believe but when I open the form
I get the following message

'ms Office Access can't find the object 'Call ReloadSuburb)Nz(Me.'

Any help in understanding this error message????

Bob

message You might be interested in this for Combos with 10's of thousands
of records

http://www.allenbrowne.com//ser-32.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi

Using ms Access 2007
I have alpha buttons on the bottom of the form to filter the
available records - based on surname.
If the number of records is still too large for several letters of
the alphabet, what is the easiest way to filter further - say on 2
or 3 letters of a surname?

Bob

 

[John W. Vinson]

What may seem a silly question...............

How do you add a module to a form exactly?

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

What you need to do is open the VBA editor for the form's Module. To do so,
you can either open the form in design view and click the "Code" button on the
toolbar, or (much more commonly) add code to a specific event on the form or
on a form control, by viewing the form's Properties; select the control to
which you want to add code, find the appropriate event on the Events tab of
its properties, click the ... icon by it, and select "Code Builder".

I suspect you're trying to type the code directly into the property. That
won't work! The property value in form design should show [Event Procedure].

 

[Bob Matthews]

Hi there

Adding code - yes that is OK................

On the current event, add this line...........................

I am off-course here!
to get to this I open the form in design view and then click on property
sheet icon (of the form)

Then under the tab "Event" the first line is "On Current" an this is where I
add the required line or lines.
Having added the debug line I do not see how to add a break point
I am in expression builder by this stage?

Bob

 

[Bob Matthews]

Hi John

Thank you for pointing out the error of my ways.........
I appreciate your patience
Also thank you to Jeanette

Bob M

What may seem a silly question...............

How do you add a module to a form exactly?

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

What you need to do is open the VBA editor for the form's Module. To do
so,
you can either open the form in design view and click the "Code" button on
the
toolbar, or (much more commonly) add code to a specific event on the form
or
on a form control, by viewing the form's Properties; select the control to
which you want to add code, find the appropriate event on the Events tab
of
its properties, click the ... icon by it, and select "Code Builder".

I suspect you're trying to type the code directly into the property. That
won't work! The property value in form design should show [Event
Procedure].

 

[Bob Matthews]

Hi John

When I enter the first letter in the combo box and drop down - there is one
entry that starts "SELECT SUBURB...."

Jeanette stated that putting some code into the General Declarations section
of the form module means
it goes at the top of the lage directly under
Option Compare Database
Option Explicit

My code is simply at the top of the module - i.e. I do not have the above
two lines??

Bob

What may seem a silly question...............

How do you add a module to a form exactly?

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

What you need to do is open the VBA editor for the form's Module. To do
so,
you can either open the form in design view and click the "Code" button on
the
toolbar, or (much more commonly) add code to a specific event on the form
or
on a form control, by viewing the form's Properties; select the control to
which you want to add code, find the appropriate event on the Events tab
of
its properties, click the ... icon by it, and select "Code Builder".

I suspect you're trying to type the code directly into the property. That
won't work! The property value in form design should show [Event
Procedure].

 

[Jeanette Cunningham]

You do need the above 2 lines at the top of every code module in your
database.
That means every code module behind every form that has a code module and
any stand alone code modules.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi John

When I enter the first letter in the combo box and drop down - there is
one entry that starts "SELECT SUBURB...."

Jeanette stated that putting some code into the General Declarations
section of the form module means
it goes at the top of the lage directly under
Option Compare Database
Option Explicit

My code is simply at the top of the module - i.e. I do not have the above
two lines??

Bob

What may seem a silly question...............

How do you add a module to a form exactly?

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

What you need to do is open the VBA editor for the form's Module. To do
so,
you can either open the form in design view and click the "Code" button
on the
toolbar, or (much more commonly) add code to a specific event on the form
or
on a form control, by viewing the form's Properties; select the control
to
which you want to add code, find the appropriate event on the Events tab
of
its properties, click the ... icon by it, and select "Code Builder".

I suspect you're trying to type the code directly into the property. That
won't work! The property value in form design should show [Event
Procedure].

 

[John W. Vinson]

When I enter the first letter in the combo box and drop down - there is one
entry that starts "SELECT SUBURB...."

Jeanette stated that putting some code into the General Declarations section
of the form module means
it goes at the top of the lage directly under
Option Compare Database
Option Explicit

My code is simply at the top of the module - i.e. I do not have the above
two lines??

Access doesn't automatically put those two lines in... but it really should;
they're essential for reliable code.

Just type them in above the first existing line in the module.

 

[Bob Matthews]

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

Bob 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?