VBA Express
addbuttonandcode [Archive] - VBA Express Forum

PDA

View Full Version : addbuttonandcode



miki
04-25-2006, 07:12 AM
Hi All,

I have found the code 'AddButtonAndCode' at this link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=275
, written by DRJ.
It works fine when i just run this macro only. however, when i list the records from db and called this AddButtonAndCode function to add buttons, my workbook didn't work and closed. I can't fix this error.
Here is my code:
CommandButton1 is the search button for user to submit his keywords 'textbox1.text' from db.


Private Sub CommandButton1_Click()

Dim rst As New ADODB.Recordset
rst.ActiveConnection = _
" Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=c:\db1.mdb"

keyword = Trim(TextBox1.Text)
If keyword <> " " Then
strSQL = " SELECT * From mydb WHERE ID LIKE '%" & keyword & "%' " & _
"OR Name LIKE '%" & keyword & "%' " & _
"OR Score LIKE '%" & keyword & "%' Order By ID Asc"

rst.Open strSQL
Range("A5..Z100").Clear

Do While Not rst.EOF()
For k = 0 To 2
ActiveCell.Offset(0, k).Value = rst.Fields(k)
Next
AddButtonAndCode
ActiveCell.Offset(1, 0).Select
rst.MoveNext
Loop
rst.Close
End If
End Sub


Anyone can help me to solve the problem? Many Thanks!!!!!!!!!
miki :help

OBP
04-25-2006, 08:42 AM
To help us help you it would be nice if you could capture the error and then let us know what it is that causes the Excel crash.
put this line after the Dim statement

On Error GoTo Err_Command1_Click

and this after the last End If statement

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

Hopefully when the error occurs Excel will now tell you what it is instead of crashing out.