Calling a query out of 26 using a search on a form

[hrg]

I have a problem in access2000 with queries.

I have a table with farm addresses. The farms are sorted into counties. I also have about 26 queries which say that if i serach for a county (e.g. Warwickshire) it will give me the nearest counties around it with farm addresses (so if i searched for warwickshire it would show Leicestershire, Nottinghamshire, Gloucestershire etc). Now i want to make a search in a form to say that if i enter a county name it will call that corresponding query (i.e. enter county :- Kent. results would call the kent query and show all the nearest counties and the farms in that query i.e. sussex, essex). So it is a bit like calling docmd.openform.....etc but instead call a query results and show them in a list box.

So my question is that does anyone know how to call a query using a txt box search.

Also the results will be shown in a list box (so how would i do this). Do i have to make another query or can i just code a button to call the right query once the county has been entered into a text box.

Thank you for your time.

 

[Craig0201]

HRG,

Nasty!!!

OK, i'd do the following......

Set up a list box populated with the names of the counties.

On a command button, set the row source of farm address list box using a Select Case statement.

Requery the list box.

Should work but I won't guarantee!!!

HTH

Craig

 

[Skeptik]

hello!! the 26 query's are one for each county, right? and what we enter in the listbox would be the name of the query, right? if i get this right this would do the trick:

Dim str As String

str = Me.Text4.Value

Me.List6.ControlSource = str
Me.List6.RowSource = str
Me.List6.Requery



'list6=listbox
'text4=txtbox

this will define the control and row source of the listbox opening the query you want to, and displays the results in the listbox! i hope it works!

hope it works and remember... "it's the man... not the machine"

Skep

 

[MichaelRed]

W/o going 'daft', I couldn't do 26 (or probably even 5) different queries. Especially when it appears that the 'need' for this is a poorly structured db to begin with.

Although I (obviously) do not know the structure, it seem slike you need at least two tables, one of them is probably your current table, with the FarmName; FarmAddr; ... ; County. Another table should (probably) just have the FarmCounties: and look like (from your example)

FarmCounties NeighborCounties

warwickshire Leicestershire
warwickshire Nottinghamshire
warwickshire Gloucestershire

With this structure, a (relatively) simple sub query should yield the entire results.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over