Connecting to Database on Worksheet_Change Event

[Nerisha]

Hi there.

I have an Excel Worksheet that I have included a Data Validation List to. The list is populated with items for sale. What I would like, is to have the user select any item from the list, and the cost of the item should be updated accordingly. No, I read somewhere that I can use the Worksheet_change event to do this, but When I put in my code below, I get the following error: Compile error: User-defined type not defined on the following line:
Dim cn as ADODB.Connection

Any ideas on how to do this?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim Item As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set cn = ozConnection()
Set rs = New ADODB.Recordset
Range("B22") = Item

sql = "Select Amount from Items WHERE ItemDescr = " & Item
rs.Open sql, cn

If Not rs.EOF Then
Range("CostItem") = rs!amount
End If

End If

 

[Ruddles]

You haven't told VBA what an ADODB.Connection is.

Go Tools > References and add a reference to Microsoft ActiveX Data Objects 2.x Library.

 

[Nerisha]

Thanks for that. Can you please help me and give an example of code to use to connect to the DB. This does not work:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim ozConnStr As String


ozConnStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=tst01.habitaz.co.za;UID=oztech;PORT= 3306;DATABASE=oztech_test;USER=oztech;PASSWORD=fYb42248Z9;OPTION=3;"

Set cn = Nothing
Set cn = New ADODB.Connection
On Error GoTo connError
With cn
.ConnectionString = ozConnStr
End With
Set rs = New ADODB.Recordset

 

[Ruddles]

I probably can't help you with it but perhaps you could tell us in what way does it not work? Does it stop with an error? Give unexpected results? Do nothing at all?

You may need to remove the On Error directive as this will mask any errors which occur after it's invoked.

 

[Nerisha]

It does nothing at all at the moment.

 

[Ruddles]

So the variables cn and rs contain Nothing after it's run but there's no error message? That's odd. Are you sure?

Something for you to look at:-

You've coded Range("B22") = Item. Item is blank because it's just been DIMmed: you copy it to B22, then whilst it's still blank you use it in a SQL string:-

sql = "Select Amount from Items WHERE ItemDescr = " & Item

This will generate a SQL string containing "Select Amount from Items WHERE ItemDescr =" which is not good SQL. If you've left the On Error directive in, that error will be masked. Place a Debug.Print statement after the sql = to confirm that the SQL string is incomplete.

If ItemDescr is a text field, I think you should be coding:-

sql = "Select Amount from Items WHERE ItemDescr = [B][COLOR=red]'[/COLOR][/B]" & Item & "[COLOR=red][B]'[/B][/COLOR][COLOR=black];[/COLOR]"

 

[Nerisha]

My problem is not the query. That I can fix. The problem is this. I have the code behind the worksheet_change event, but it only fires when I click on a different cell than the one currently selected. If I change the value in the listbox, nothing happens. How can I make it such that I have a listbox populated with items for sale, and when the user changes the item on the list, the cost of the item is updated.

 

[Richard Schollar]

What version of Excel are you running? On xl2010 the Worksheet_Change is fired when I select a different value from the list. How is your Data>Validation>List set up by the way? Is it a hardcode comma-separated list, a range or a formula?

 

[Nerisha]

I am using Excel 2007. The list is populated with a range of data located on the same sheet.

 

[Ruddles]

I'm sorry if I answered the wrong question but you did say your code didn't do anything and you wanted "an example of code to use to connect to the DB". I think my suggestions about your SQL query were reasonable. Now you've changed the nature of the problem.

I think you need to get the database code to work before you start worrying about where it's going to be called from.