ADO help.

[mattythomson]

Hello, basiclly this is what i got -

seprate databases in Access which have for example "FromPounds" database
which has a new colum for each country and what the excange rate would be, and in the vb program i have 2 drop down boxes one " FromCurrency" and "ToCurrency" a "convert" command button and "txtAmount" enter box and "txtResult" result box, i would like the "FromCurrency" cbo box to be able to find out what currency it is trading from, and the "ToCurrency" cbo box to find the conversion rate in the database, then the "toCurrency" cbo box will need to be multiply by what the amount has been entered into the "amount box"

tbh i do not want to have to write out 100 if statement due to needing to use 10 different countrys. if any one could help this would be welcome

[si_the_geek]

Thread moved to Database Development forum - which is where ADO questions belong

How is the data stored in the database(s)? (tell us the table name(s), field names and data types, and show us a couple of example rows)

What values are shown in the combo boxes?

[mattythomson]

the data is stored in each data base -

countries are along the top and each exchange rate from "pounds" to each country are entered below, like image shown below, there is a database as show below for each coutry to be converted from.


the form is layed out as below with each cbo named as cboFirst and cboSecond as show in image below

[si_the_geek]

I'm afraid you haven't answered any of my questions.. your first picture (of one of the tables) might have been useful, but you resized it too small to be readable.

By the way, I hope in a way you have got the terminology wrong - and by "database" actually mean "table" (a database can, and usually should, contain many tables), otherwise there will be extra work to be done.

[mattythomson]

[si_the_geek]

Well that's part of the first question answered..

What are the data types of the fields?

Do you have one table or one database per "from" currency?

What values are/will-be shown in the combo boxes?


By the way, I think your table design is less than ideal, in terms of complexity of tables, querying, and maintenance - the entire thing could be replaced by two tables with fewer columns, one for Currency info (CurrencyID, CurrencyName [perhaps CurrencyAbbreviation too if you want it]) and one for Conversion info (FromCurrencyID, ToCurrencyID, Rate), eg:

Table: Currency
CurrencyID  CurrencyName
1           GBR Pounds
2           Euro
3           US Dollar
...

Table: Conversion
FromCurrencyID  ToCurrencyID  Rate
1               2             1.26790
1               3             1.95934
...

[mattythomson]

What are the data types of the fields?

Text

Do you have one table or one database per "from" currency?

One data base per currency

What values are/will-be shown in the combo boxes?

All of the different countries, ie . English Pounds / US Dollar / Euro

[si_the_geek]

What are the data types of the fields?

Text

That's not good... is there any reason that you have chosen to do that, rather than using a numeric data type?

Using a numeric data type will reduce the amount of work involved every single time it is used.

Do you have one table or one database per "from" currency?

One data base per currency

Seriously? What on earth possessed you to create entirely separate databases?

Or are you confused about the terminology? (note that "From GBP" is a table, not a database).

If they are actually tables in the same database, it is OK - but would be better with changes to the design (like the alternative I suggested).

What values are/will-be shown in the combo boxes?

All of the different countries, ie . English Pounds / US Dollar / Euro

That's good.. but made much more complex by your odd table design.

[mattythomson]

tables are being redun atm the way you have wrote above, yer not sure why i did it that way, just stuck as access 2007 just wants to sort the colums atm and carnt find out why.

[si_the_geek]

Don't worry about how the tables are sorted (in theory, there is no sorting/order at all - the records can be returned in any order).

All that matters is that the data is correct.

[mattythomson]

Updated Exchange Table ? ?

[mattythomson]

Updated Currency Table ? ?

[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

[mattythomson]

error on " Call FillCombo "

* complie error *
sub or function not defined

[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

[mattythomson]

Private objConn As ADODB.Connection
Dim dblRate As Double
Dim objRS As ADODB.Recordset
Dim strSQL As String    'Declare the variables we need
Dim oRS As ADODB.Recordset
 

Private Sub Adodc1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

End Sub

Private Sub cmdGo_Click()

 '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

End Sub

Private Sub cmdReset_Click()
txtAmount.Text = Empty
txtResult.Text = Empty
End Sub

Private Sub Form_Load()

  
  Set oRS = New ADODB.Recordset
  oRS.Open strSQL, oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
                            
  With objComboBox          'Fill the combo box
    .Clear
    If strFieldForItemData = "" Then
      Do While Not oRS.EOF      '(without ItemData)
        .AddItem oRS.Fields(strFieldToShow).Value
        oRS.MoveNext
      Loop
    Else
      Do While Not oRS.EOF      '(with ItemData)
        .AddItem oRS.Fields(strFieldToShow).Value
        .ItemData(.NewIndex) = oRS.Fields(strFieldForItemData).Value
        oRS.MoveNext
      Loop
    End If
  End With

  oRS.Close                 'Tidy up
  Set oRS = Nothing


 Set oRS = New ADODB.Recordset

Set objConn = New ADODB.Connection
  objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Documents and Settings\Matt\Desktop\Old Assignments\Unit 32 - Graphics\Exchange Rate\currencyexchange.mdb"
  objConn.Open
  

  strSQL = "SELECT CurrencyName, CurrencyID FROM Currency"
  Call FillCombo(cboFirst, objConn, strSQL, "CurrencyName", "CurrencyID")
  Call FillCombo(cboSecond, objConn, strSQL, "CurrencyName", "CurrencyID")
  
End Sub

[si_the_geek]

Rather that copying part of FillCombo (and putting it inside Form_Load), I meant to copy the entire routine, and add that to the form. You can paste it just before the top (or just after the bottom) of any sub/function.

Once you have done that, the only code you need in Form_Load is from "Set objConn .." onwards.


Note that not all of the variables should be declared where you have them - only objConn should be there. The declarations for dblRate,
objRS and strSQL should all be inside the cmdGo_Click routine (so you can't accidentally add bugs later, by using those variables elsewhere). The variable oRS is not needed, as that is declared inside the FillCombo routine.