COMMAND TEXT NOT SET 2

[rry2k]

Hi,
I'm trying to use a dbcombo box as a driver for my form. With alot of help from Woyler I got everything setup but now I have another problem.

When I get to the line that opens the recordset (rs.open) I get the following error: command text not set for the command object.

Private Sub DBCombo1_Click(Area As Integer)
Dim sMyCriteria As String
Dim sSQL As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sDatabasePath As String
Set cn = CreateObject("adodb.connection&quot
Set rs = CreateObject("adodb.recordset&quot
cn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=f:\db1.mdb;Uid=Admin;Pwd=;"
cn.Open
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
sMyCriteria = DBCombo1.Text
sSQL = "SELECT * FROM EMPLOYEES WHERE [name]='" & sMyCriteria & "'"
Text1.Text = rs("Name&quot
Text2.Text = rs("Title&quot
Text3.Text = rs("Department&quot
rs.Close
Set rs = Nothing
End Sub

Thanks for the help..Russ

 

[dannyh]

The line :

sSQL = "SELECT * FROM EMPLOYEES WHERE [name]='" & sMyCriteria & "'"

needs to be before the line :

rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText

Hope this helps,

Dan.

 

[rry2k]

Dan,
That got me down to the line where I populate text1 then I get eof or bof is true error. I'm not even getting to select a name from the combobox. is the event wrong?

 

[dannyh]

what about the line :

sMyCriteria = DBCombo1.Text

this should still be the line direcly above the line :

sSQL = "SELECT * FROM EMPLOYEES WHERE [name]='" & sMyCriteria & "'"

so what you should have is :

Private Sub DBCombo1_Click(Area As Integer)
Dim sMyCriteria As String
Dim sSQL As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sDatabasePath As String
Set cn = CreateObject("adodb.connection&quot
Set rs = CreateObject("adodb.recordset&quot
sMyCriteria = DBCombo1.Text
sSQL = "SELECT * FROM EMPLOYEES WHERE [name]='" & sMyCriteria & "'"
cn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=f:\db1.mdb;Uid=Admin;Pwd=;"
cn.Open
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
Text1.Text = rs("Name&quot
Text2.Text = rs("Title&quot
Text3.Text = rs("Department&quot
rs.Close
Set rs = Nothing
End Sub

The event should be fine. It would be worth checking that the recordset is not empty before trying to populate the text boxes, as the error you got that time means that no matching records have been returned.

Dan.

 

[woyler]

Use a regular combo box control. The need for the dbCombo is not necessary in this case

 

[dannyh]

Thinking about it, perhaps this code should be in combo1_change and not click. This would show the list, but it would fire if you type in the combo, so it depends what functionality you are after.

Dan.

 

[woyler]

the change event will fire every time you type a character. That will cause you some headaches unless you test the changes as they come in.

 

[rry2k]

I changed to a regular combo and now nothing happens. I put the code in combo_change and click, put a break point in both and I don't get to either?? very strange

 

[woyler]

Do you have any items in the combo box to select?

 

[rry2k]

no, it's empty but when I check the table directly it is indeed loaded.

 

[dannyh]

Okay I have just checked and I admit the click event is definately better for this !

 

[rry2k]

I'm really missing something here. I created a new app, put on a combo box, put a break on the click event and I never get to the break after I click on the combo?? I'm missing something stupid here.

 

[rry2k]

I used a DropDown event and now I'm back to the combo not being populated. I tried combo1.addItem but that didn't help

Russ

 

[woyler]

The click event will not fire until there is an item to select in the combo box.

 

[rry2k]

Danny and Woyler,
We/I finally got it. I Really appreciate the help.
I ended up loading the comboBox in the drop down event and then populating the text boxes on click.