# refresh recordset



## Guest (May 15, 2002)

within VB6 i have a combo and a datacombo
i am trying to use the value of the combo to change what table the value of the datacombo returns
at the moment within the gotfocus event of the datacombo i have a probably over complicated if section that checks to see the value of the combo and then calls the query that relates ( i dont like datacontrols very much, i always use code)
this works fine first time round the problem occurs when i change the value of the combo and want it to now return a new set of data to the datacombo
no matter what i do i cant seem to disassociate the RS data from the datacombo
tried closing RS i nthe lost focus along with settign the datacombo.datasource = nothing
but no joy!!
any ideas?


----------



## Rockn (Jul 29, 2001)

Is the focus really that important since the user will have to click the combb box again to change the valuse anyway. Jut have it set so when the valuse in the combo box changes that it will refresh what is supposed to be displayed in the datacombo window. It's really easy to do this in Access and I don't think the transition would be that hard. Maybe it is just an problem with resetting the recordset after the initial change is made.


----------



## Guest (May 15, 2002)

focus isnt that important 
it was just the trigger i chose to use for the recordset data to change
thinkign about it putting something within the change event would be better
still i think i will have the same problem
i agree i think the problem problem is with reseting the recordset
but nothing i try is doing it
i am sure i have managed to do it before
but cannot find any reference to it


----------



## TimCottee (Oct 10, 2001)

Here is a straight sample that shows how to achieve this, you can adapt it to your needs.


```
Private cnnAccess As ADODB.Connection
Private rstSource As ADODB.Recordset

Private Sub Combo1_Click()
    rstSource.Close
    If Combo1.Text = "Source 1" Then
        rstSource.Open "Select ID From MyTable", cnnAccess, adOpenStatic, adLockReadOnly
    Else
        rstSource.Open "Select ID From MyTable11", cnnAccess, adOpenStatic, adLockReadOnly
    End If
    DataCombo1.DataField = "ID"
    DataCombo1.ListField = "ID"
    Set DataCombo1.DataSource = rstSource
    Set DataCombo1.RowSource = rstSource
    DataCombo1.Refresh
End Sub

Private Sub Form_Load()
    Combo1.AddItem "Source 1"
    Combo1.AddItem "Source 2"
    Set cnnAccess = New ADODB.Connection
    Set rstSource = New ADODB.Recordset
    cnnAccess.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\personal\other\testbed\cremosa2k.mdb;Persist Security Info=False;"
    cnnAccess.Open
    rstSource.Open "Select ID From MyTable", cnnAccess, adOpenStatic, adLockReadOnly
    DataCombo1.DataField = "ID"
    DataCombo1.ListField = "ID"
    Set DataCombo1.DataSource = rstSource
    Set DataCombo1.RowSource = rstSource
    DataCombo1.Refresh
End Sub

Private Sub Form_Unload(Cancel As Integer)
    rstSource.Close
    cnnAccess.Close
    Set rstSource = Nothing
    Set cnnAccess = Nothing
End Sub
```


----------



## Guest (May 16, 2002)

ok thanks Tim, but....
still gettign the same error message when i select option then move to datacombo then click back onto combo1 and select a different value:
Runtime error 545
Unable to bind to field or datamember 'DEBTOR'

this is my code:

Private Sub Form_Load()
Set CNSearch = New ADODB.Connection
CNSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\files\databases\labelling.mdb;Persist Security Info=False"
Set RS1 = New ADODB.Recordset
RS1.Open "SELECT DEBTOR FROM Customers ORDER BY DEBTOR", CNSearch, adOpenStatic, adLockReadOnly
DC1.Refresh
End Sub

Private Sub Combo1_Click()
RS1.Close

If Combo1.Text = "CUSTOMER" Then
RS1.Open "SELECT DEBTOR FROM Customers ORDER BY DEBTOR", CNSearch, adOpenStatic, adLockReadOnly
With DC1
Set .DataSource = RS1
Set .RowSource = RS1
.ListField = "DEBTOR"
.DataField = "DEBTOR"
.Refresh
End With
End If

If Combo1.Text = "ITEM" Then
RS1.Open "SELECT ItemNumber FROM Product ORDER BY ItemNumber", CNSearch, adOpenStatic, adLockReadOnly
DC1.Refresh
With DC1
Set .DataSource = RS1
Set .RowSource = RS1
.DataField = "ItemNumber"
.ListField = "ItemNumber"
.Refresh
End With
End If

End sub

The values of combo1 are entered in the properties
please also note there are 5 values in the combo, and 5 different if clauses, i only put 2 of them here to give an example
as far as I can see this is the same as yours (more or less)
it still seems to not be closing RS1?


----------



## Guest (May 16, 2002)

ok sorted it
ignore all of that

it was just moving the :

.DataField = "ResultDN"
.ListField = "ResultDN"

in front of this 

Set .DataSource = RS1
Set .RowSource = RS1

in the if clause
why would this make such a difference?
surely i should be settign the datasource before the datafield??
anyways it now works
thanks for your help tim


----------



## TimCottee (Oct 10, 2001)

You have to set the datafield and listfield first because otherwise when you set the datasource VB internally will check the column list of the recordset being used. If the field name doesn't exist then it will fail the datacombo load without giving you an error that makes sense.

Anyway glad you got it sorted.


----------

