Combo Box and SQL

[Mark Gambo]

I have been trying to populate a combo box with the data contained in a table in a MS SQL Database. I have searched the BB for this information but I was unable to combine the posts in a manner that made sense to me.

I have a table located in SQL called Current_Users that I have linked to my vb project via the dataenvironment1. This table contains two (2) fields; UserName and P_Word. I want to use contains of the UserName field to populate ComboBox1. How can I accomplish this?


Thank You,

Mark

[Memnoch1207]

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
 
Set conn = "Driver={SQL Server};Server=ServerName;Database=dbName;Uid=Username;Pwd=password;" 
Set strSql = "SELECT * FROM Current_Users"
 
rs.Open(strSql, conn, 3, 3)
 
If(rs.eof) then
   MsgBox("There are no records in the database")
Else
   do until rs.eof
      combobox1.Add rs("Username") 'add the field to the combobox
      rs.movenext
   loop
End
 
rs.Close
conn.Close
 
Set rs = nothing
Set conn = nothing
Set strSql = nothing

[Mark Gambo]

I am now getting an error regarding the line:


Dim conn As ADODB.Connection

Error:

Compile Error
User Defined Type Not Defined

I have the Microsoft ADO Ext. 2.7 for DLL and Security reference loaded.

Any suggestions?

Mark

[Memnoch1207]

add a reference to Microsoft ADO 2.x library.

[Mark Gambo]

Thanks, it worked like a champ!

I am using this combo box to so that the user can select a his/her name from the drop down list. I would like them to be able to type the first letter of the last name form in order for them to select there name.

i.e., List
AAronn, John
Abhrams, Roberta
Able, Edward
Adams, Sara

I want Edward Able to press the "A" key three (3) times in order to select his name.

How can I do this?

Regards,

Mark

[vbcodec]

try this:

Dim Backspaced As Boolean

Private Sub cboNom_Change()

If Backspaced = True Or cboNom.Text = "" Then
Backspaced = False
Exit Sub
End If

Dim i As Long
Dim nSel As Long

For i = 0 To cboNom.ListCount - 1
If InStr(1, cboNom.List(i), cboNom.Text, _
vbTextCompare) = 1 Then
nSel = cboNom.SelStart
cboNom.Text = cboNom.List(i)
cboNom.SelStart = nSel
cboNom.SelLength = Len(cboNom.Text) - nSel
Exit For
End If
Next

End Sub

Private Sub cboNom_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeyBack Or KeyCode = vbKeyDelete Then
If cboNom.Text <> "" Then
Backspaced = True
End If
End If

End Sub

[vbcodec]

ops I didn't read your post very well at first beginning the code I post is for autocompleting but let me know if you really need only one character of first name so I'll modify if you think the one I sent is better then cheers

Hope it helps.

[Mark Gambo]

Thanks for the code!

Yeah, I was looking for the user to continue to press the letter of the last name until there get the name they are looking for.

Thank you again!!!

[vbcodec]

hi man
this is so funny, I never notice, default ComboBox does already have that thing, I just started to do that but it does have it.
have you ever give it a try?


Hope it helps.

[Mark Gambo]

I tried it before and it would not work. I'll create a new one and see if it works. Thanks again.

[Mark Gambo]

vb,

I tried it and I could not get it to work. I tried your code and the autocomplete will be handy for another portion of my program. Do you know what I could be doing wrong regarding my original post?

[vb_dba]

Set the Style property on your Combo box to 2 - Dropdown List.

[Mark Gambo]

That did the trick, Thanks