ADO help.

[si_the_geek]

That all looks good (except for spaces in the field names - that causes problems, so you should change it), and we're ready for some code..

I notice that you have posted other threads about this exact same thing (eg: this one). Please don't do that, as you will get various answers which conflict with each other (thus causing yourself problems), and it will annoy people who answer questions (they end up answering twice, or seeing that the answer they spent time on has already been posted).


I notice from the other threads that you are using a Data Control, which I (like most people) recommend against. Is there a particular reason you want to use it?

For an explanation of why it is a bad idea, see the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of this forum), or the "Bound Controls are Evil" link in Hack's signature (see the thread I linked to).


Replacing it is incredibly easy, and gives you much more freedom. To replace it, you just need to go to "Project"->"References", and tick "Microsoft ActiveX Data Objects library 2.x", then add this code:

'## In the "General" - "Declarations" section at the top of the code
Private objConn as ADODB.Connection

'## In Form_Load
  Set objConn = New ADODB.Connection
  objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Folder\filename.mdb"
  objConn.Open

'## In Form_Unload
  objConn.Close
  Set objConn = Nothing

Simply change the highlighted part to suit your database file - but if you are using an .AccDB file (or what I posted doesn't work), see the ConnectionStrings link in my signature for examples.


To fill the combo's, you can then use code like in the FAQ article How can I fill a combobox with values in a database?. I would recommend adding the FillCombo routine from that thread to your project, then to fill the combo's you can simply add this into Form_load:

Dim strSQL as String
  strSQL = "SELECT CurrencyName, CurrencyID FROM Currency"
  Call FillCombo(cboFirst, objConn, strSQL, "CurrencyName", "CurrencyID")
  Call FillCombo(cboSecond, objConn, strSQL, "CurrencyName", "CurrencyID")

This not only puts the names into the list, but also puts the ID's into an array (ItemData) that we can use later.


In the _Click event of the Convert button, you can find the rate you want by opening a recordset with an apt SQL statement, eg:

Dim dblRate as Double
Dim strSQL as String
Dim objRS as ADODB.Recordset

    'create the SQL statement (using the ID values in ItemData)
  strSQL = "SELECT ExchangeRate " _
         & "FROM Conversion " _
         & "WHERE FromCurrencyID = " & cboFirst.ItemData(cboFirst.ListIndex) _
         & " AND ToCurrencyID = " & cboSecond.ItemData(cboSecond.ListIndex)

    'run it to get the data 
  Set objRS = New ADODB.Recordset
  objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
  If objRS.EOF Then
    objRS.Close
    Set objRS = Nothing  
    MsgBox "Exchange rate not found!", vbInformation
    Exit Sub
  End If
  dblRate = objRS.Fields("ExchangeRate").Value

  objRS.Close
  Set objRS = Nothing

    'you now have the rate, in the dblRate variable

[si_the_geek]

You missed this part of my post:

To fill the combo's, you can then use code like in the FAQ article How can I fill a combobox with values in a database?. I would recommend adding the FillCombo routine from that thread to your project, then to fill the combo's you can simply add this into Form_load:

[mattythomson]

sorry mate bit lost atm, any help would be nice lol