I am using a recordset in a sub to populate a set of DataCombo's.  If I
close the recordset at the end of the sub, then the DataCombo's do not show
all of the items in the list, they only show the current item that it is
referring to out of the DB.

I know it's good form to close the rs when done and set the rs = nothing,
but when I do, it hoses the data its pulling.  So, is it OK to not close the
rs and not set the rs = nothing, or should I move my dim rs as Recordset to
the general declarations and close the rs when I exit my form?

Example, one of the DataCombo's shows who the District Manager is.  If I
rs.close and set rs = nothing, then it only shows that District Manager, but
none of the other DM's.  If I take these two statements out, then the
DataCombo does show all of the DM's.

My sub is:

Private Sub setComboRS(cbo As DataCombo, strID As String, strName As String,
strTable As String)
On Error GoTo err_setComboRS
  Dim rs As Recordset
  Set rs = New Recordset
  rs.Open "SELECT " & strName & ", " & strID & " from " & strTable & " ORDER
BY " & strName, db, adOpenStatic, adLockOptimistic
  With cbo
    .DataField = strID
    .BoundColumn = strID
    .ListField = strName
Set .RowSource = rs
Set .DataSource = adoPrimaryRS
  End With
'  rs.Close
'  Set rs = Nothing
Exit Sub
err_setComboRS:
  MsgBox Err.Number & " " & Err.Description, , "setComboRS"
End Sub

You can't do that (close the recordset) when you're binding a control to
that recordset (at least as far as I know without having problems to
work-around, such as reopening the recordset). The thing with bound controls
is that they're going to do what they're written to do...and that may not be
what YOU want to do.

This is one of the reasons why most of us don't use data-binding at all.

I'm just guessing (because I don't ever bind controls to data sources), but
I'd guess that closing the recordset when the form unloads would be "ok"
(but my real advice would be to get away from binding controls to a data
source).

Show quote Hide quote You are binding the DataCombo to the recordset, so yes, if you close the
recordset, the data will go away.

Either leave the rs open, or don't use a DataCombo. If you use a regular
ComboBox, you can just fill it up by going through the recordset yourself, using
AddItem, and then close the recordset when you are done.

Oh, and if you try that, be sure to change the type of cbo in your sub back to
ComboBox :)

OK, so both Steve & Mike are recommending not using databinding.  It's OK,
but not recommended, right?

So, how would I need to do it with a "normal" combo box?  I can loop through
the rs and populate the combobox no problem, but the combo box is tied back
to a field in my db.  Let's see if I can explain how I'm doing this.

I have tblStoreData that contains information about the store, the region
it's in (RegionID), the District Manager which we call either a DFO or FC
depending, when I built it, we were calling them FC so that's what the db
has (FCID), the owner (OwnerID), the DMA (DmaID), the Project Manager
(FpmID), the system installer (InstallerID), the status of the store
(StatusID), & the store type (TypeID).  Each of these has a respective
table, i.e. tblRegion, tblFC, tblOwners, tblDMA, tblFPM, tblInstallers, etc.

How it's working now is when I change the drop down (DataCombo), it updates
the ID in the main tblStoreData to the new ID based on the selection in the
drop down.  When I display a record, it reads the appropriate ID and shows
the value in the DataCombo.

I'm OK with changing these to a regular ComboBox, but I'm not sure how to
tie the values together.

Thanks,

Darhl

Show quote Hide quote when you load the store name into the combobox you put the store ID into the
associated ItemData for the new entry; when the click event fires you get
the store id and query the database for the other fields to read the
associated data.

Show quote Hide quote Right, I'm OK with that part, but say for example, the District Manager
changes (FCID), so when I edit the store and change the value of the drop
down from Bill (FCID = 1) to Jim (FCID = 2), how do I get that back into the
database so next time I pull up the record it shows Jim as the DM?

d

when the user clicks "Save" or however your interface allows them to move on
to a new record you issue an Update statement against the database to set
all the new store information

OK, I'll give that a shot.  I haven't done a SQL update before, so I'll have
to do some more learning.  I'll post back if I decide I need help with the
update.

Thanks!

Darhl

A bit of Maths help with arrays
Right syntax for EXPRESSION
Collection Capabilities
How to control button in the form if i have more than 1 user profi
Variants
Type mismatch error
Excuting a command line program from within VB
Sending email from app in vb6 to hotmail
I have a problem with Microsoft Multimedia control
Clean Decimal Strip and Re-insert