Creating Cascading ListBoxes with MS Access and ADO Control

[intercepter]

Hi Everyone,

Was wondering if I could get some help here.

I have a multi table database.

I would like to populate 3 listboxes with data from 2 of the tables and then move the ADO control depending on where the user selects items in the listboxes.

I can get the first listbox to populate just fine from the first table called "tblBOOK"
It has 2 fields, Book_ID and Book_Title. The first field has 66 entries and the 2nd field has unique names for each of those id numbers.

The 2nd table I am concerned about is called tblQUOTE and has Book_ID, Chapter, Verse, Quote.

The problem is, that Book_ID in tblQUOTE has multipe 1's 2's and 3's etc depending on its corrisponding Book_ID field in tblBOOK.

tblQUOTE is grouped with all the book titles that go with the number 1 in Book_ID would be considered the Book_Title in tblBOOK.

So lets say tblBOOK has a Book_ID of 1 and a Book_Title of Genesis.

so in tblQuote I would like all the 1's in tblQUOTE.Book_ID to relate to Genesis which is in tblBOOK.

The next listbox then would be set to use the Chapter field in tblQUOTE and be set to the number 1 grouped by the Book_ID field and the 3rd list box would use the Verse field and also be set to the number 1 grouped by the Chapter field.

What I dont want to see in the 2nd and 3rd listboxes are multiple 1's, 2's and 3's etc.

It would need to group the 2nd list box with only 1 representation of each chapter in the book Genesis. And the 3rd listbox would be only 1 representation of each verse in chapter 1 of book Genesis.

I then want the ado control to move to the correct area and put in a text box from the proper location in the database the contese of field Quote from tblQUOTE.

So, what I am looking for is subset's I guess but taking out the repeats.

Any ideas if this is even possible? And if so, any code suggestions.

What I have tried so far is using SQL queries but I cant seem to get the 2nd and 3rd boxes to populate without seeing multiple 1's, 2's etc.

If you havent guessed so far, yes its a bible reading program with a access database.

Any assistance would be appricated.

[Pinky98]

This the whole point of a relational data-base!

In your SQL query... you would want to have something like:

SELECT   *
FROM      tblQUOTE
WHERE   (Book_ID = 1)

This would give you a recordset of everything in tblQuote to do with the book which has an ID = 1. (-Or- you could also use an ADO Recordsets filter).

Then obviously you would replace "1" with whatever the required book is.

[intercepter]

Thanks for the reply.

Yes and that works quite well setting the listbox controls.

I guess my real problem now is searching through them to get to the right record.

I had a couple ideas for a search function and I wanted to see if there were any suggestions out there as to make this work. It obviously doesnt work now.

Here is what I have so far:



<VB>Private Sub cmdSearch_Click()
Dim Verse As String
Dim s As String
Dim a() As String
Dim b() As String
Dim c() As String
Dim d() As String

Verse = InputBox("Enter The Verse Your Looking For", "Bible Reader")

s = Verse
a = Split(s, " ")
Debug.Print a(0)
Debug.Print a(1)

c = Split(a(1), ":")
Debug.Print c(0)
Debug.Print c(1)



datPrimaryRS.Recordset.Find "tblBOOK.Book_Title = " & a(0) & " ", 0, earchForward
datPrimaryRS.Recordset.Find "Chapter = " & c(0) & " ", 0, adSearchForward
datPrimaryRS.Recordset.Find "Verse = " & c(1) & " ", 0, adSearchForward



End Sub
</VB>


I left the print functions in there to make sure the search string is spiltting up correctly.

Also, I think its a rather clumsy way to go through the rows but, again, I cant think of another option at the moment.

I have an open ado control in my project, with an open recordset, here is the SQL statement.

Select tblBOOK.Book_ID, tblBOOK.Book_Title, tblQUOTE.Book_ID, tblQUOTE.Chapter, tblQUOTE.Verse, tblQUOTE.Quote From tblBOOK,tblQUOTE Where tblBOOK.Book_ID=tblQUOTE.Book_ID Order By tblQUOTE.Book_ID ASC,Chapter,Verse

(Its wordwrapped of course)

What I cant figure out, is how to translate the name of the book that a user enters from tblBOOK.Book_Title to its tblBOOK.Book_ID row that is joined with tblQUOTE.Book_ID to move the recordset to the right row.

Any ideas?

Intercepter

[Pinky98]

I would think of using a drop down or list box to select the book...

There the best way to do that is to read in from your first table (tblBOOK) a recordset of the books and their associated IDs. Then, create the listbox with displays the books so that the selected item can be correlated to the index required.

To do this you would make Genesis list item 1 and in the tblBOOK table Genesis would be index 1. You could also make use of the ItemData field in the list box... Although I prefer to use the ListView control. It takes a bit of getting used to how it works but well worth the effort. Each item in the list has a KEY field which is a unique identifier for the item in the list. There is also a TAG field which you can use to store any user information you like. So, one of these can be used to store the index whilst the name is store in the TEXT field of the item.

Then, when the selection changes, simple find the selected item's index and use it to do a filter of the recordset containing the verses.

If you want to use text.... then you need to use a mapping function to try and work out what the user has selected. For example:

The user could enter "Genesis 1:1" now... that is pretty easy, it is a simple look up to the number... but what about "GENESIS" or "genesis"... plus then there are the shorten names i.e. "Gen". To get around the caps is easy... just use:

Verse = UCase$( InputBox("Enter The Verse Your Looking For", "Bible Reader") )

The shortened names are a bit harder. To get round this... I might look at having another table (lets call it tblALTERNATENAMES) with all possible shortened names (and for ease of searching have the long name too) for the books as a list and their associated index in the tblBOOK table. So, something like:

GENESIS, 1
GEN, 1
GENS, 1
EXODUS, 2
EXO, 2
EXD, 2
EXDS, 2
EXS, 2
LEVITICUS, 3
LEV, 3
:
:

and so on. Then do a look up on this table for the required shortname. And retreive the index from there. Then use that index to do a look up on tblQUOTE.

Also, consider the chapter and verse input. The user might enter them as 1:1 (like you have chosen... but they might also choose to use a "v" not a ":" or even a dot ie. "1v1" or "1.1". So I wouldn't use the SPLIT function. I would rather search for any one of a number of characters.... (Also split with spaces is not friendly when using multiple spaces ie. gen__1:1)

'I always UCASE and TRIM all user inputs to avoid unexpected errors creeping in
UserSelectedVerse = UCase$(Trim$(InputBox("Enter The Verse Your Looking For", "Bible Reader")))

'Get the first space
SpacePos = Instr(1, UserSelectedVerse, " ", vbBinaryCompare)
If (SpacePos = 0) Then Exit Sub

'Get the book name
Book = Trim$(Left$(UserSelectedVerse, SpacePos-1))

'Get the chapter and verse
ChapterAndVerse = Trim$(Mid$(UserSelectedVerse, SpacePos+1))

'Split the chapter and verse
For i = 1 To Len(ChapterVerseSeperators)
    BreakPos = InStr(1, ChapterAndVerse, Mid$(ChapterVerseSeperators, i, 1))
    If BreakPos <> 0 Then Exit For
Next i
If (BreakPos = 0) Then Exit Sub

Chapter = Left$(ChapterAndVerse, BreakPos - 1)
Verse = Mid$(ChapterAndVerse, BreakPos + 1)

ChapterVerseSeperators must be defined at the top of your code using a CONST statement... i.e.
Const ChapterVerseSeperators = ":.V"

At the end of this code block you should Book, Chapter and Verse.

(This application doesn't need high speed look ups... so staggering looks-ups like I have done above is not a problem... But, you might also want to think of combining them into a single SQL call to the db. Mainly for future practice at using dbs.)

[intercepter]

Hey Pinky,

I hope your still around.

Your search sub works great. I am sorry I didn't thank you before.
So I say now THANK YOU!!!

I was thinking, what about multiple verse choice?

like john 3:16-17

I tried following your thought process and did this:

 Const ChapterVerseSeperators = ":.V-"

However, I tried several ideas and they didnt seem to work.

Do you have any ideas on adding on to your search function to see if - is not there then move on, or if it is there, seperate the numbers and use them in a Lbound and Ubound for a

datPrimaryRS.Recordset.Find "Verse = " & Verse & " ", 0, adSearchForward

Maybe do a loop with Lbound and Ubound?

Not sure where to go from here.

Intercepter

[Pinky98]

Sure, glad it helped.

Mmmm, multiple verses. I see this starting to get hard...

but first thing to do is test if a dash occurs in the verse if so, then seperate the verse into a lower and higher verse, then do a "select" using "where (lower <= verse) AND (verse <= higher)"

I wouldn't use a lbound, ubound loop for this, because you are working with a recordset. Rather, retrieve what you need from the database using SQL. Don't know if you are aware, but to move through a recordset, you use .Next.

[intercepter]

I see what your getting at.

So, do something like this?

If verse like "#-#" then 
some sql statement.
else
datprimaryrs.recordset.findfirst
end if

[Pinky98]

Mmmm, yeah that may work too.