Retrieve a DataSet from SQL w/ComboBox

[dstrat6]

Hi, Everybody. I'm trying to figure out how to Retrieve a DataSet from a SQL Server with a Combo box, then show the results in multiple txtBox's. I can retrieve the correct info from the server into the combo box, but when I select a paticular object nothing happens. I would like to make the program work as follows. I select an object from the ComboBox or DropDown list and the data within that paticular row will show up in my txtBox's. Thank you in advance. Sorry so long wanted to be very discribtive.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If you show us your code how you did "retrieve the correct info from the server into the combo box", we may be able to help you with your problem.

Also, is it Word, Excel, PowerPoint, Access, other app?


Have fun.

---- Andy

 

[dstrat6]

This is what I've done so far for the dropdown list. The code works but only retrievies the last row not the selection that I pick.

Private Sub cmbMachine_Change()
Dim cnt As ADODB.Connection
Dim rst As New ADODB.RecordSet
On Error GoTo ErrorHandler
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=XXXXXX;" & _
"Data Source=."
Set cnt = New ADODB.Connection
stSQL = "SELECT * FROM Operations"
With cnt
.CursorLocation = adUseClient
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
Do Until rst.EOF
Me.lblOperator = rst!OperatorInitials
Me.lblCycle = rst!Cycles
Me.lblLotNum = rst!LotNumber
Me.lblPartNum = rst!PartNumber
rst.MoveNext
Loop
DoEvents
If rst.EOF Then
Set rst = Nothing
Set cnt = Nothing
End If
Exit Sub
ErrorHandler:
MsgBox "Can not add items.", vbCritical
End Sub

Have any suggestions.

 

[dstrat6]

Bye the way I'm using Excel. All the labels and my dropdown list are on a worksheet not a user form. Thanks for the replies.

 

[PHV]

Add a WHERE clause in stSQL.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dstrat6]

Here is my where clause. It compiles but does not execute, it simply goes to my ErrorHandler:

stSQL = "SELECT * FROM Operations WHERE MachineNumber=" & "Me.cmbMachine=" & "'"

 

[Andrzejek]

If you can just pick from Combo, and not type in it, change

Private Sub cmbMachine_[red]Change[/red]()

to

Private Sub cmbMachine_[blue]Click[/blue]()

Have fun.

---- Andy

 

[Andrzejek]

Oh, yeah, and change your SQL:

stSQL = "SELECT * FROM Operations WHERE " _
    & " MachineNumber = '" & Me.cmbMachine.Text & "'"

If Machinenumber is a string.

If it is a number, do:

stSQL = "SELECT * FROM Operations WHERE " _
    & " MachineNumber = " & Me.cmbMachine.Text

You should get just one record as an outcome.

Have fun.

---- Andy

 

[dstrat6]

For any one who is interested I finally was able to get my code to work properly. Here is the correct WHERE clause. Thanks to all who took the time to help. I'm sure you'll be hearing from me again until then best regaurds.

stSQL = "SELECT * FROM Operations WHERE MachineNumber='" & Me.cmbMachine.Value & "'"

 

[dstrat6]

Sorry Andrzejek I didn't see your post before I posted mine but Me.cmbMachine.Text works the same as Me.cmbMachine.Value. Thanks a bunch for all of your help.