Combo Box Help 2

[gregmosu]

Hello,

I have a form with 3 combo boxes, and how I want this to work is... the user has to select a value from the first combo box, and based on that value, a db query is run and the second box is populated... and so on until the third box is populated. Basically the value from the preceeding combo box will depermine the values of the following boxes.

If I could do this on the web, I would have no problem. I could use a drop down menu...

<select name=&quot;whatever&quot;>
<option value=2>Mike Jones
</select>

where 2 is the primary key.. or something like that.

I know how to put values in the combo box, but is there a way to bind a value to it... such as that 2?

Sorry if I didnt explain this very well.. I've been thrown into this project with very little VB experience.

Thanks,
Greg

 

[johnwm]

The Combobox control has an ItemData property associated with each ListItem which can store a Long. It's often used to store the PK of the recordset displayed in the Combo. The following gives you an idea (it's from an old function to fill a generic Combo from it's corresponding table)

strSQL = &quot;Select &quot; & myTable & &quot;Name, &quot; & myTable & &quot;ID &quot; & strCrit & &quot; from tbl&quot; & myTable & _
myCriteria & &quot; ORDER by &quot; & myTable & &quot;Name&quot;
Set rst = cn.Execute(strSQL)
With rst
Do While Not .EOF
myCount = myCount + 1
myCombo.AddItem .Fields(0)
myCombo.ItemData(myCount) = .Fields(1)
.MoveNext
Loop
End With


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[gregmosu]

Thats exactly what I was looking for. Thanks John!

 

[johnwm]

You're welcome and Thank You

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[rcollier5]

You are doing exactly what I am trying to do...

How can I use from what is selected from combo box 1 to populate combo box 2?

I tried using SQL in the rowsource box in access but it does not work...can someone post some code?

here is my problem specificly:
*I have a form that inserts all info into one row of a table
*Combo box 1 lists facilties based on which ones are already found in the table (this works fine)
*now based on what facility is in combo box 1, I want to populate names already in the table associated with that facility

Thanks!

 

[johnwm]

Just pick up the combo1.ItemData(combo1.ListIndex) to use as your SQL parameter for combo2.

BTW you mention Access - this solution is for VB - I don't do much in Access VBA

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[rcollier5]

thanks for the response!

I fixed my porblem using an SQL statement and a hidden text box

I have another question....when I select a Last Name from a combo box, it populates the First Name Combo Box, If the combo box will only contain 1 First Name for that last name how can I make it automatically come up? as in come up as the default value or something

Thanks

Ryan

 

[johnvai]

If one value is in the query and you want to select it automatically then you have to write:
combo1.listindex=0

 

[VBVines]

This is the basic structure of what I do with an fname and an lname field in access or sql server.
=======================
Private Sub Form_Load()

Dim conn As ADODB.Connection
Dim rs As Recordset
Set conn = New ADODB.Connection
With conn
.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;Data Source=c:\finalproj\localold.mdb"
.Open
If .State = adStateOpen Then
Set rs = conn.Execute("Select distinct StaffID, Fname & "" "" & Lname as Anames From Staff WHERE ((Not (Staff.StaffID)=21))")


Do Until rs.EOF
Combo1.AddItem rs("anames")
Combo1.ItemData(Combo1.NewIndex) = rs("staffID")
rs.MoveNext
Loop


Else
MsgBox "No Connection"
End If


rs.Close
conn.Close
Set conn = Nothing
End With
End Sub


aspvbwannab