Populate tables from listboxes 1

[Domino2]

I have a form with 4 listboxes on. Listbox1 is filled with directory names. Listbox1b is filled with word document names found in the direcory selected in Listbox1. Listbox2 is filled with subdirectory names found under the directory selected in Listbox1. Listbox2b is filled with word document names found in the subdirectory of the Listbox2 being selected.

I am struggling to cycle sequentially through the listboxes to enter the listbox values into a sensible table structure. Before I start slogging it out, has anyone any suggestions on where to start. Thanks

 

[fneily]

What's your "sensible" table structure look like?
A normalized table structure would look like:
tblDirectory
DirID
DirName

tblSubDir
SubID
DirID
SubName

One-to-many relationship between tblDirectory and tblSubDir.

tblDocs
DocID
WordDocName

Connecting tblDocs to the directories:
tblPathDoc
PathID
DirectID
DocID

where DirectID is either DirID or SubID.
Note: don't use autonumbers.

 

[Domino2]

Many thanks fneily, I will sketch that out to see what it looks like and work out a procedure to fill the tables. I think when the table joins are in it will be clearer, thanks again I'll let you know how it goes

 

[fneily]

Also what you could do, in your tblSubDir, by using a Union query, add the word None. Then in the tblPathDoc, instead of DirectID, just have SubID. So the relationships would look like:
tblDirectory ---> tblSubDir ---> tblPathDoc <---- tblDocs

So tblPathDoc is your junction table. Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

So tblPathDoc could look like:
PDA MainDir1 SubDir2 WordA
PDB MainDir1 SubDir3 WordB
PDC MainDir1 None WordA

Sometimes I have two Word Docs named the same, but in different directories. As you can see, the structure can handle it.

 

[Domino2]

Many thanks, I will go along with that, thanks again.