ADO & Musical Combos

On questions:
1. For fill combo box 1 use RecordSet with source "Select distinct state from locations"
2. Use
private Sub Combo1_Click()
rs.Find "State = '" & Replace(Combo1.Text, "'", "''") & "' and City=" & Replace(Combo2.Text, "'", "''") & "'", , adSearchForward
If Not rs.EOF then
Label1.Text = rs!PhoneNumber
End If
End Sub
3. Replace function
private Sub PopulateCity()
Combo2.Clear
Do Until rs.EOF
Combo2.AddItem
rs!City & " - " & rs!NetworkID
rs.MoveNext
Loop
End Sub

I don't think that you can use 2 conditions with the 'FIND' method.

rs.Find "State = '" & Replace(Combo1.Text, "'", "''") & "'", , adSearchForward,adbookmarkfirst

I don't think that this is what you want though. Find sets the current row of the recordset to the row that had been picked. You want to limit the recordset to only the cities that are associated with the state that was selected. I beleive you need to 'Filter' these rows out as there will be more than one row.

rs.Filter = "State = '" & Replace(Combo1.Text, "'", "''")

when using sql statements
All strings must be delimited with the tick mark.
numbers are not delimited with anything
dates are delimited with the pound sign '#'

I think that this is more what your after. Please correct me if it is not.

Option Explicit
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Combo1_Click()
rs.movefirst
rs.Filter "State = '" & Replace(Combo1.Text, "'", "''")
rs.movefirst
PopulateCity
End Sub

Private Sub Combo2_Click()
rs.movefirst
rs.Find "City = '" & Trim(Replace(Left$(Combo2.Text, InStr(Combo2, "-") - 2), "'", "''")) & "'", , adsearchforward, adbookmarkfirst
'That part is confusing. There is easier ways to single out the record.
'May need to modify you table to include an autonumber field if you don't
'have one
If Not rs.EOF Then
label1 = rs!Phonenumber
Else
MsgBox "Not Found"
End If
End Sub

Private Sub form_load()
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
con.open "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = c:\temp\dialup\locations.mdb;" & _
"Persist Security Info = false"

rs.open "select distinct state from locations", con, adOpenDynamic, adLockOptimisticm, adcmdtext
PopulateStateCombo
rs.open "Select * from locations", con, adOpenDynamic, adLockOptimistic, adcmdtext
End Sub

Private Sub PopulateCity()
Combo2.Clear
rs.movefirst
Do Until rs.EOF
Combo2.AddItem rs!City & " - " & rs!Network
rs.MoveNext
Loop
End Sub

Private Sub PopulateStateCombo()
Do Until rs.EOF
Combo1.AddItem rs!State
rs.MoveNext
Loop
End Sub

David Paulson

I put in 2 queries because if you use the distint keyword you will return recordset containing rows of states with no duplicates (needed only to populate the combo box). This is what you wanted, however there are also rows in the table that will have the same state, but different cities, so you would still need those rows also. I suppose you could have just filled the recordset once and filled the combo box with the duplicate states, and created some sort of algorithm to delete the duplicate entries. This was just one way to acheive that. As for the rs.Filter "State = '" & Replace(Combo1.Text, "'", "''") it looks like i forgot an = sign. Try this instead.
rs.Filter = "State = '" & Replace(Combo1.Text, "'", "''")

I hope you don't have many more problems as I never tested the code I gave you as I would have needed to create the database to text. As far as your first problem, maybe you'll need to close the recordset, set it to nothing, and reinitialize it again , to open it again.

rs.Open "select distinct state from locations", con, adOpenDynamic, adLockOptimistic, adCmdText
PopulateStateCombo
rs.close
set rs = nothing
set rs = new adodb.recordset
rs.Open "select * from locations", con, adOpenDynamic, adLockOptimistic, adCmdText
End Sub

David Paulson