ListBox.Value inside an MySQL Query

[techm3]

Hello there,

I want to select an item form MySQL database where the name column is equal to the ListBox1.Value and then show all he row information in different textbox...

I already tried this query:
rs1.Open "select * from employees_info where name like '" & ListBox1.Value & "'", conn, adOpenStatic, adLockOptimistic

but it doesn't work

Please Help Me with some code!

Thanks!

[Charlize]

declare a string variable and store the me.listbox1.value to it.

You are aware of dao and workspace / database / recordset ? And you have dao 3.6 ticked in the references ?

Charlize

ps. maybe give us your full coding, example sheet maybe ?

[techm3]

This is the code in the form that I want to select the user:

[VBA]Private Sub Btn_Add_Click()
Set rs = New ADODB.Recordset
rs.Open "select * from employees_info WHERE (name= '" & ListBox1.Value & "')", conn, adOpenStatic, adLockOptimistic
With rs
Do While Not .EOF
TextBox1.Text = ("employees_id")
.MoveNext
Loop
.Close
End With
End Sub

Private Sub UserForm_initialize()
Dim i As Byte
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=127.0.0.1" _
& ";DATABASE=nomina" _
& ";UID=root" _
& ";PWD="

'Module1.Connect2DB
Set rs = New ADODB.Recordset
rs.Open "select * from employees_info order by name", conn, adOpenStatic, adLockOptimistic
With rs
Do While Not .EOF
ListBox1.AddItem rs("name")
.MoveNext
Loop
.Close
End With

End Sub
[/VBA]

I want that when I press a name from the listbox another form open with all the information about the user in some textbox... But I dont know the mysql query for doing that...

My project is here ... ubuntuone.com/p/qGZ/

[Norie]

The query in the first post looks fine though you should only use Like if you are looking for partial matches.

Also if you are using Like you probably need to add wildcards.

In MySQL the usual wildcard is %.
[vba]
rs.Open "select * from employees_info WHERE (name Like '% & ListBox1.Value & "%", conn, adOpenStatic, adLockOptimistic
[/vba]

If that returns a record you would then need to go through the fields and transfer the data to the textboxes.

You can do that by naming the individual fields eg rs.Fields("employees_id")...
or you could try a loop.
[vba]

rs.MoveFirst

For Each fld In rs.Fields
I=I+1
Me.Controls("TextBox" & I).Value = fld.Value

Next fld
[/vba]
If you want particular fields to go in particular textboxes you could perhaps use an array for the field names and and array for the textboxes names.
[vba]
arrFlds = Array("employees_id", "join_date")
arrCtrls = Arayr("Textbox1", "txtJoinDate")

For I = LBound(arrFlds) To UBound(arrFlds)

Me.Controls(arrCtrls(I)).Value = rs.Fields(arrFlds(I)).Value
Next I
[/vba]

[Charlize]

Something like this ? Not tested.
[VBA]Private Sub Btn_Add_Click()
'loop for looping through all your datafields of current record in database
Dim myloop As Long
Set rs = New ADODB.Recordset
'You select each employee with certain name (are they unique ?)
rs.Open "select * from employees_info WHERE (name= '" & ListBox1.Value & "')", conn, adOpenStatic, adLockOptimistic
With rs
'do while selection of recordset is not eof
Do While Not .EOF
'loop through each field of record
For myloop = LBound(.fields) To UBound(.fields)
'store value of all the fields in textbox1'
'textbox1 is multiline ?
'ex. of possible result : the name - surname - id - paycheck amount ...
textbox1.Text = textbox1.Text & .fields(myloop).Value & " - "
Next myloop
'remove last space - and space and add a return
textbox1.Text = Left(textbox1.Text, Len(textbox1.Text - 3)) & vbCrLf
'goto next found record
.MoveNext
Loop
.Close
End With
End Sub
[/VBA]Charlize