Use List Boxes as a Navigation Tool

[peterpil19]

Hi, I'm designing a database for my personal use, and am not a programmer unfortunately. I would like to be able to navigate other than using record numbers or searching/queries.

I know that it is possible to use a List Boxes for this purpose. I would like to have three columns or four columns, the first containing a general category (a field common to many records) then selecting categories from each column until left with unique selections upon clicking on which takes me to that record.

Would anyone be able to help me in this respect, or direct me to where I could find sample code for this that wouldn't be hard to alter for a non-programmer?

Thanks!

--Peter

 

[gol4]

see the faq section

this link refers to combos but code would be similar

faq702-4398

 

[peterpil19]

Hi again, I already know how to use a list box as a navigation tool, the part I don't know is how to select different fields from the box.

Suppose I have the following fields: Suburb, Street, house no:

Record 1: Woodside, Lehmann, 3
Record 2: Woodside, Lehmann, 4
Record 3: Woodside, Thomas, 5
Record 4, Whyalla, Harvey, 3
Record 4, Whyalla, Harvey, 4

I want to be able to select asuburb from the first column of the list box, then choose a street from column 2, then choose a street no to take me to that particular record.

It would be good if the columns were grouped so that e.g. Woodside only appeared once, not 3 times, and Whyalla only appears once not twice (as per the records above).

Is this possible? It seems to be a logical way of navigating and many web pages for example use this technique.

Conversely, I would be interested in any way to create a category tree so I can drill down to particular records.

Thanks,

Peter

 

[gol4]

see this faq on using multiple combo boxes
faq702-4289

or you could keep requering the same list box by changing its rowsource thus changing the selections

example
under form current event
strsql = "select distinct suburb from addresstbl"
me.list1.rowsource= strsql
list1.tag = "step1"

list1.onclick or after update event
if list1.tag= "step1" then
strsql = "select distinct street from addresstbl where suburb ='" & me.list1 & "'"
me.list1.rowsource= strsql
list1.tag = step2
else
strsql = "select Houseno from addresstbl where street='" & me.list1 & "'"
me.list1.rowsource= strsql
me.list1.tag=""
end if

or you could look at a treeview control and load the nodes. The treeview is what is used to navigate folders in windows explorer. Has a learning curve but once you master it is a very useful tool

 

[gol4]

realized I forgot to answer your primary question. sorry

select different fields from the box

me.list1.column(index,row)
is how you select different itmes in different rows. it is ) based so first column is zero

me.list1.column(2) would return "Lehmann" if you had the first item selected

 

[peterpil19]

Thanks for you help guys. I've been doing a lot of reading on the TREEVIEW Control.

I'm trying to implement a hierarchal treeview into my database (for navigation) but am having trouble because EVERY example I've found involves more than one table, and each table has its own unique Primary key?

I only have use for One table in my database and ONLY one Primary key. The other two tables are merely to enter data as the respective fields are looked up in the main table.

How would start about doing this? I tried using Microsoft's example (Q209891), but it required a field that refers to a table with another primary key?

Thanks again,

peter

 

[formerTexan]

Peter,

From the perspective of a treeview control it doesn't matter whether the heirarchial data is a singletable or multi table and the Microsoft example applies to either.

A single table heirarchy will have to follow a definite structure as follows and I will outline it in terms of the Microsoft example.

EmployeeID LastName ReportsTo

1 Smith 2
2 Nguyen 6
3 Gutierrez 4
4 Doe 4
5 Wong 2
6 Ndlovu

The above example provides a three level heirarchy. Every employee is listed regardless of their position in the heirarchy. The heirarchy relationships are then maintained by the ReportsTo column with this column merely retaining the EmployeeID of the senior employee.

I hope this clears up a little bit of the haze.

Cheers,
Bill

 

[peterpil19]

Thanks for the info Bill, it has cleared the Microsoft example up quite a bit for me My problem is that I still can't reconcile the Microsoft example with my own database as I don't have a "reports to" type field, i.e. a field that points to a definite primary key.

If you could entertain this totally made-up example of mine it would be highly appreciated.

EXAMPLE: Say I have a table called "Stamp Collection" (I don't actually collect stamps). EVERY record is of a different stamp, that's all. The primary key is just some autonumber and not really relevant. There are 3 fields I want to put into treeview:

1. Country (that stamp's from)
2. Size of stamp
3. Date of stamp

Now, as each country DOESN'T have a primary key necause it is part of the same table and selected for each record by a drop down lookup list, how would I get Size of stamp and date of stamp to point towards the Country field in some sort of hierarchy.

I actually got as far as one level of nodes the other nite, but as soon as I tried to add another, I would get error message's galore, so I aborted.

Thanks again for any tips/recommendations/advice.

--Peter

 

[gol4]

If Indeed your stamp table looked like you say you would have some design issues as you would want some type of lookup table that lists countries and then store the country code in the stamp table as well
Fields of table
Pkautonumber countrycode stampsizecode stampdate and probably several others
Let us assume your tables really look like you describe
PK country stampsize stampdate

I will describe the ugly way to load this tree because it is easier to follow but you would probably want to look at some form of recursion or loading on click
Dim rst as recordset
Dim strsql as string
Strsql = “Select distinct country from stampTBL order by country”
Set rst = currentdb.openrecordset(strsql)
‘We now have countries ready to load into the tree view
do until rst.eof
me.tree1.nodes.add ,,rst.country,rst.country ‘this loads the node with key = to country this is why lookup would be better but??
.loop
‘tree now loaded with countries now for the sizes
rst.close
Strsql = “Select Distinct country, stampsize from stampTBL order by country,stampsize”
Set rst = currentdb.openrecordset(strsql)
‘We now have sizes ready to load into the tree view
do until rst.eof
me.tree1.nodes.add rst.country,tvwchild, rst.country&rst.stampsize,rst.stampsize ‘our stampsize node key is county and stampsize combined. The select distinct ensures it will be unique.
.loop
sizes have been loaded under countries now for the stampdate
rst.close
Strsql = “Select pk,country, stampsize ,stampdate from stampTBL order by country, stampsize,stampdate”
Set rst = currentdb.openrecordset(strsql)
‘We now have dates ready to load into the tree view
do until rst.eof
me.tree1.nodes.add rst.country & rst.stampsize,tvwchild,”D”& rst.pk, stampdate 'pk is unique here so will use it plus link back to record
loop
rst.close
set rst= nothing
the thing about loading the tree is that each KEY must be unique because of poor design we are forced to create unique keys by combining fields.
Look at the add method and what is required.
.Add unique parent key or blank ,tvwchild if a child node or blank,unique key for node being added, nodes text

hope this clears treeview up a bit more for you
also note this is freehanded so probably several errors in the code

 

[peterpil19]

Thanks for your help guys! It has illustrated that in respect of using the Treeview control, my database has design issues. I'm going to have to create tables for each field I want represented by a node and each table will have a primary key which is referenced in a main table. That way, I can probably copy the microsft code and it will work...

Otherwise, I will have to stick with my listbox. It's good in that it goes straight to the correct record, and is ordered (as I've based it on a query), but is not as neat because other than the headings, it is not categorised.