Combo Box DB population
Rick
currently i have juust produced a basic log in screen, however now.
I need three combo boxes.
The first should indicate the contents of the second, and the second should
indicate the contents of the third. And all the populating of the combo box
needs to come from my table in microsoft access 2000.
Im not sure where to start with programming this. any guidance would be
greatly appreciated.
Thanks in advance
Ralph
"Rick" <rick_w...@hotmail.com> wrote in message
news:ulgaP2Cj...@TK2MSFTNGP04.phx.gbl...
It isn't at all clear what you mean by "... indicate the contents of ..."
Are these boxes in some kind of parent/child relationship?
Such as ...
CBOne
Lists All Customers
CBTwo
List All Invoices from Customer selected by CBOne
CBThree
List All Items from Invoice selected in CBTwo.
There is a couple of zillion ways to go about this.
DataEnvironment
Bounded controls
DataRepeater
Shaped Recordsets
Pure ADO, ....
Also what ComboBox are you using?
This might get you started...
http://www.vbforums.com/showthread.php?t=380145
hth
-ralph
Rick
the forum you linked me to was great help, thanks alot. Its much
appreciated.
Rick
"Ralph" <nt_cons...@yahoo.com> wrote in message
news:eCEyHKEj...@TK2MSFTNGP02.phx.gbl...
I am having no problems with filling a combo box with data from a database,
however creating a
parent child relationship is out foxing me.
My current code is;
Private Sub form_load()
SQLcourse = "select course from menu"
rs.Open SQLcourse, CON, adOpenKeyset, adLockOptimistic
With cbocourse
.Clear
Do While Not rs.EOF
.AddItem rs.Fields("course").Value
rs.MoveNext
Loop
End With
rs.Close
End Sub
Private Sub cbocourse_Change()
SQLtype = "select ItemType from menu where course='" & _
cbocourse.Text & "'"
rs.Open SQLcourse, CON, adOpenKeyset, adLockOptimistic
With cbotype
.Clear
Do While Not rs.EOF
.AddItem rs.Fields("ItemType").Value
rs.MoveNext
Loop
End With
rs.Close
End Sub
Private Sub cbotype_Change()
SQLitem = "Select itemname from menu where itemtype='" & _
cbotype.Text & "'"
rs.Open SQLitem, CON, adOpenKeyset, adLockOptimistic
With combo1
.Clear
Do While Not rs.EOF
.AddItem rs.Fields("itemname").Value
rs.MoveNext
Loop
End With
rs.Close
End Sub
If anyone could help me with setting this up correctly. Thanks in advance.
Ralph
>
<snipped>
What you are trying to do is manage Hierarchical data or Data Shaping. This
is often easier to do with a Shape provider. However, your data doesn't
appear to be normalized.
Does it look like this?
One table "Menu" with fields "Course", "ItemType", "ItemName"
With records like this?
"Course1", "ItemType1", "ItemNameA"
"Course1", "ItemType1", "ItemNameB"
"Course1", "ItemType1", "ItemNameC"
"Course1", "ItemType2", "ItemNameD"
"Course1", "ItemType3", "ItemNameE"
"Course2", "ItemType1", "ItemNameF"
"Course2", "ItemType1", "ItemNameG"
"Course2", "ItemType1", "ItemNameH"
"Course2", "ItemType2", "ItemNameI"
"Course2", "ItemType2", "ItemNameJ"
If it does you might want to read up on this :
http://www.databasedev.co.uk/database_normalization_process.html
http://www.databasedev.co.uk/table_performance.html
How are you using your combo box?
Are you planning on allowing changes?
Difficult to provide a solution without knowing more about what you are
trying to do.
If not normalized then take a look at the ADO.Filter method.
If you normalize your data here some suggestions that you can ignore. <g>
What you are trying to do is manage Hierarchical data or Data Shaping. This
is often easier to do with a Shape provider. The returned Recordset already
'knows' a great deal about the relationships. Saves a lot of quering back
and forth. And a lot of code once you get the hang of it.
You might want to investigate using the DataEnvironment and Bound controls.
There is a ComboBox and a 'Databound ComboBox'. While the DE seems a bit
removed from straight ADO it does have the advantage of allowing you to
build and test Shape queries within it. Once you get a handle on managing
Hierarchical data you can branch out into doing it from scratch.
Also note that the sample uses a Grid to show the results. Their advantages
to using a Grid in place of a ComboBox for these kinds of data displays. 1)
You have more control over format. 2) Items of interest always seem to grow,
and a Grid with multiple fields is easier to manage. 3) You can do almost
anything with a Grid that you can with a ComboBox.
PRB: Binding Hierarchical Recordset in Data Environment
http://support.microsoft.com/kb/190605
-ralph
Ralph
Try reposting to the microsoft.public.vb.general.discussion newsgroup. More
people there.