Filter rowsource results by variable. (1 Viewer)

[Dranoweb]

Hi,

Currently I have a form that displays details of person, and location.

among these fields I have a town name and a postcode field.

I have a post code table, and a qurey of that populating the rowsource of the drop down box, for post codes.

I would like to be able to take the town name (say as a variable) and filter the rowsource results to display only corresponding postcodes.

To explain in more detail the postcode query (Pc-book_Query) has two columns "PCode" (name of the town) and "ID" (the post code)

odd way to do things i know, but this is as supplied to me.

I am hoping to be able to have column "PCode" filter to the value of a control named "Town" and display the records of column "ID" in the rowsource property of "pcbox"
Is this possible or is there a better way to go?

 

[AccessBlaster]

If I understand, you want to filter your form based on "PCode" which is the name of some town correct?
You need.

A new button to filter the form.
A new unbound textbox for selection.
A new button to unfilter the form.

put the below code behind a new button's (On Click) event

 ' Find the record that matches the control.
   Dim stFilter As String
   Me.FilterOn = False
 
   stFilter = "[[COLOR=red]PCode[/COLOR]] Like '" & Me.[[COLOR=red]new unbound textbox[/COLOR]] & "*'"
 
   Me.Filter = stFilter
   Me.FilterOn = True

Then unfilter
Put the below code behind a second new button's (On Click) event

Me.FilterOn = False
Me.[COLOR=#ff0000]new unbound textbox[/COLOR] = ""

 

[Dranoweb]

If I understand, you want to filter your form based on "PCode" which is the name of some town correct?

Not exactly.

there are Two tables involved here.

Table 1: contains various personal details including a town and post code.

postcode is displayed in a drop down.

currently, drop down is populated by a second table (or query of that table)

I would like to filter the drop down contents to post codes that exist in that town name.

 

[vbaInet]

What you're after is called cascading. Have a look at this tutorial from pbaldy:

http://www.baldyweb.com/CascadingCombo.htm

 

[Dranoweb]

What you're after is called cascading. Have a look at this tutorial from pbaldy:

http://www.baldyweb.com/CascadingCombo.htm

Not really, I only have one combo box.

I have found a temporary solution to this for the time being.

I will try to explain myself a little more clearly.


I have a textbox and a drop down.

the textbox contains a town name.

the dropdown contains a post code.

to populate the dropdown, I used another postcode tabe as the rowsource.

I would like to be able to filter the drop down results based on the town name on the current record.

 

[Dranoweb]

A little update that may assist in explaining what I want to do:

I am half way there. I currently have the drop down box displaying post codes for ONLY town names that exist in the main table.

I used the following SQL in the ROWSOURCE property of my dropdown box:

SELECT [Pc-book_Query].ID, [Pc-book_Query].Pcode FROM [Pc-book_Query] WHERE ((([Pc-book_Query].Pcode) In (SELECT [Town] FROM [PRESENTERS])));

To explain further;

Pc-book_Query
contains post codes, ID is the post code - and pcode is the town name (this is just how the data was arranged when i received it)

PRESENTERS
Is the main table that contains the Town name I wish to use to filter my rowsource results. (field name "Town")

 

[vbaInet]

It isn't a continuous form is it?

 

[Dranoweb]

It isn't a continuous form is it?

It is, and hence why I have not added a hidden subform and grabbed the values from that.

At present I have just added a small "suggest" button beside the box, that launches a new form briefly, figures out the post code, and then dumps it back to the original control using a global variable.

 

[vbaInet]

In that case you will need something like this:

http://www.databasedevelopment.co.uk/examples.htm

Download the Continuous Form Combo from LPurvis' site.

 

[Dranoweb]

Hmm, well that does achieve my end objective, but not quite how I had imagined.

The solution has actually added a trimmed down listbox next to a textbox to give the appearance of a combo box.

I had considered a listbox, but not quote in the arrangement. I'll give it a go and see if it will do what I require, and then I'll post back with results.

 

[vbaInet]

There's no other way of achieving your objective. This is what you're limited to doing in Continuous view.

 

[Dranoweb]

There is always a way, it's just a matter of ingenuity and a lot of code.

Anyway I have come to a more viable solution anyway.

I DID manage to make things work as I had planned - thanks for all the suggestions, the last one was the trick.

However in my quest for temporary solutions, I made a little popup form.

This form would check the post code against the town, filter it by state, and copy that to the postcode field, move to the next record and repeat until last record.

So, in effect, it ended up filling in every postcode automatically.

A couple of error handlers for blanks or not founds and it works a treat.
Does a days work in seconds.

So I kind of programmed my way out of even needing this.

However it is still valuable learning as I have much left to do on continuous forms.

Thanks everyone! - much appreciated.

 

[vbaInet]

Yep, I know the link provided was the way to go. This is the dilemma one faces in Continuous View or Datasheet. The controls built up on either views are instances of the original control, hence, any filtering has to be done on the Control Source or Row Source level.

Although what you've done works for you, but I wouldn't go down that route. In effect everytime you run the pop-up a complete update of the records is performed, bouncing back and forth to the Jet engine - i.e. updating and requerying. With LPurvis' method all we're doing is requerying a small subset of records and any changes made affects only the current record. Much more optimised.

However, I'm glad you came to your own resolve.

 

[Dranoweb]

In the end the client decided they liked the popup method better, as it updates the every record in one quick run,

as opposed to the original idea in which the client had to select each drop down manually.

The comment from the client was "OMG you have just saved me 3 days work"

So for that reaction, I feel I should just leave things how they are now.

It also means that when the postcode list is updated from Australia post, I can just import that and update all records, with little effort.

 

[vbaInet]

The comment from the client was "OMG you have just saved me 3 days work"

Client says: "I will pay you more than what you've quoted me"

 

[Dranoweb]

Client says: "I will pay you more than what you've quoted me"

Yeah too bad this is a charity job. Have a family member in the Fire authority here, and the Government want some pretty hairy statistics.

I made the mistake of saying "That's easy"

Long story short, instead of a weeks work for a fortnightly report, they do it at the click of a button now.

Not to mention a whole host of trends and stats they couldn't do before.

Of course, now the government is asking for more stats based on this database.

But this is just for one office, the statewide system is a SAP based one and it's really, really horrible from an end user POV.

 

[vbaInet]

Ah, at least now they are the ones saying, "that's easy".

The government loves freebies

Good luck with the rest of the project.