Polpulating and selecting from combo box 1

[tag141]

I now have my project up and running and just need to fine tune a few things. My search from txtText3 is OK and produces the results I require but, there can be multiple choices for a paricular search. If the user searches for ABC there can be ABC1, ABC2, ABC3 etc and the form will only return the first field it finds. So I have attempted to add a combo box which will show a list of ABC1, ABC2, ABC3 etc and allow them to chose. How is it possible to populate the box and then select the result they want, which will in turn populate the other boxes?

Private Sub command1_click()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = c:\db1.mdb"
conn.Open

Set rs = conn.Execute("SELECT TEST_NAME,TEST_CODE, AKA, ENTRY_CODE, SENT_TO, SAMPLE, MIN_VOL, HANDLE, TAT FROM TESTGUIDE WHERE TEST_NAME= '" & txtText3 & "' or TEST_CODE= '" & txtText3 & "' or AKA like '%" & txtText3 & "%'")

If Not rs Is Nothing Then
If Not rs.EOF Then
txtText1.Text = rs.Fields("TEST_NAME")
txtText2.Text = rs.Fields("TEST_CODE")
txtText4.Text = rs.Fields("AKA")
txtText5.Text = rs.Fields("ENTRY_CODE")
txtText6.Text = rs.Fields("SENT_TO")
txtText7.Text = rs.Fields("SAMPLE")
txtText8.Text = rs.Fields("MIN_VOL")
txtText9.Text = rs.Fields("HANDLE")
txtText10.Text = rs.Fields("TAT")
cmbCombo1.Text = rs.Fields("TEST_NAME")
End If
End If

Set rs = Nothing
Set conn = Nothing

End Sub

TIA

 

[zemp]

To populate a combo box use the .additem method.

Combo1.Additem "<whatever as string>"

Use the click event to get the selected value and use it where you need.

Value = Combo1.list(combo1.ListIndex)

The .List property is the array of values, that the user sees, in the Combo added by the .additem method. The .listindex property is the element number of the selected value in the list property array.

zemp

 

[cbsm]

Just be careful (as far as I know) - the on Click event does not fire when you change the value with the keyboard (instead of the mouse) - you can use the Change event (wich doesn't fire when you use the mouse - so you have to use both events !).

 

[tag141]

OK, I've changed it to this..

If Not rs Is Nothing Then
If Not rs.EOF Then
txtText1.Text = rs.Fields("TEST_NAME")
txtText2.Text = rs.Fields("TEST_CODE")
txtText4.Text = rs.Fields("AKA")
txtText5.Text = rs.Fields("ENTRY_CODE")
txtText6.Text = rs.Fields("SENT_TO")
txtText7.Text = rs.Fields("SAMPLE")
txtText8.Text = rs.Fields("MIN_VOL")
txtText9.Text = rs.Fields("HANDLE")
txtText10.Text = rs.Fields("TAT")
cmbCombo1.AddItem rs.Fields("TEST_NAME")
Value = cmbCombo1.List(cmbCombo1.ListIndex)
End If

but all this seems to do is add the last item in the txtText3 box. I would like it to list everything with ABC in it when the user searches for ABC. Perhaps the additem is clue?

 

[tag141]

Private Sub Command1_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim i As Integer
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = c:\db1.mdb"
conn.Open

Set rs = conn.Execute("SELECT TEST_NAME, TEST_CODE, AKA, ENTRY_CODE, SENT_TO, SAMPLE, MIN_VOL, HANDLE, TAT FROM TESTGUIDE WHERE TEST_NAME = '" & txtText3 & "' or ENTRY_CODE = '" & txtText3 & "' or TEST_CODE = '" & txtText3 & "' or AKA like '%" & txtText3 & "%'")

If Not rs Is Nothing Then
If Not rs.EOF Then
txtText1.Text = rs.Fields("TEST_NAME")
txtText2.Text = rs.Fields("TEST_CODE")
txtText4.Text = rs.Fields("AKA")
txtText5.Text = rs.Fields("ENTRY_CODE")
txtText6.Text = rs.Fields("SENT_TO")
txtText7.Text = rs.Fields("SAMPLE")
txtText8.Text = rs.Fields("MIN_VOL")
txtText9.Text = rs.Fields("HANDLE")
txtText10.Text = rs.Fields("TAT")

End If
End If
For i = 0 To rs.Fields.Count - 1
Combo1.AddItem rs.Fields(i).Value
Next

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

MsgBox "It don't do nuffin", vbInformation
End Sub

This works up to a point. It adds all the columns from the search box. I still want it to add rows with the value like "%txtText3%

 

[cbsm]

Is this what you like to do ?

while not rs.eof
For i = 0 To rs.Fields.Count - 1
Combo1.AddItem rs.Fields(i).Value
Next
rs.movenext
wend

 

[jajinder]

To execute the code with the Keyboard - Enter-Key use the Keypress methode.

Private Sub Combo1_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case 13
Call Combo1_Click
End Select
End Sub

---------------------------------------
This will be the day when all of God’s children will be able to sing with a new meaning, “My country, ‘tis of thee, sweet land of liberty, of thee I sing. Land where my fathers died, land of the pilgrim’s pride, from every mountainside, let freedom ring. - Marten Luther King

 

[tag141]

Sorry guys, I really do appreciate the help but...I want the combo box to display the records in a field. All I seem to be able to do is display the fields. If my search is for ABC there may be several records that have ABC in them and I would like them displayed in the combo box (or listbox or whatever) to be chosen.

Thanks again for any help.

 

[johnwm]

Check your search criteria:

Set rs = conn.Execute("SELECT TEST_NAME,TEST_CODE, AKA, ENTRY_CODE, SENT_TO, SAMPLE, MIN_VOL, HANDLE, TAT FROM TESTGUIDE WHERE TEST_NAME= '" & txtText3 & "' or TEST_CODE= '" & txtText3 & "' or AKA like '%" & txtText3 & "%'")

In this search you're looking for txtText3 on its own in TEST_NAME and TEST_CODE (using the EQUALS condition) but you're searching for txtText3 anywhere within AKA (by using LIKE and the wildcards

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

For tsunami relief donations

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

http://www.essexsteam.co.uk

for steam enthusiasts

 

[AndrewNeely]

If you wish to have several records possible (like searching for "smith" in the phone book), and you want to display the results, you need to iterate (loop) through the recordset.

Do while rs.eof=false
cmbCombo1.additem( "" & rs.Fields("TEST_NAME"))
'moves the database to the next record in the recordset
rs.movenext
loop


The "" & rs.fields("Test_Name") is to prevent an error in the very real event that you run across a null record.

If I knew more about what you were trying to do, I could help more.

 

[tag141]

CBSM, thanks for that. Just a bit of brain fade. I had the combo box displaying the correct rows but with every field as well. I modified the code to this

while not rs.eof
For i = 0 To rs.Fields.Count - 8
Combo1.AddItem rs.Fields(i).Value
Next
rs.movenext
wend

and it displays the TEST_NAME field only and shows all the rows with the search criteria.

AndrewNeely, thanks for offer but as you can see, it's now sorted. I think it's basically what you coded.

 

[cbsm]

If you like to display only one field you do not need the second loop !
Just do :
while not rs.eof
Combo1.AddItem rs.Fields(X).Value
rs.movenext
wend

where X is the field you are looking for.

Or

while not rs.eof
Combo1.AddItem rs!TEST_NAME
rs.movenext
wend