• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Connecting to db w/o data-bound controls

I know that it's 'easier' to just throw a data-bound control onto all of my forms, but I want to do this all in code.

I have the connection object global throughout the project...

Microsoft ActiveX Data Objects Data Library 2.1 Library

Option Explicit
Global gDbConn As ADODB.Connection
Function gfnConnectToDatabase()
          'connect to database
          Set gDbConn = New ADODB.Connection
          gDbConn.Open "file name=c:\MyDatabase.dsn"
End Function


...but I don't know how (or even if it's possible to) to set the [frmEmployee] form to the [tblEmployee] table within the gDbConn object.   In Access, I would set the ControlSource to the table, but can I connect the form to the table in VB code?  Or will I have to set the controls individually?

Option Explicit
Private Sub Form_Load()
          ' Open employee table.
          Dim rsEmployee As ADODB.Recordset
          Set rsEmployee = New ADODB.Recordset
          rsEmployee.CursorType = adOpenKeyset
          rsEmployee.LockType = adLockOptimistic
          rsEmployee.Open "tblEmployee", gDbConn, , , adCmdTable
End Sub
this creates a recordset, but I need to have controls on the form with the [tblEmployee]![VariousFields] data.

I put
"SELECT [rsEmployee].[Akey] FROM rsEmployee;"
in the DataSource field of a ComboBox, but it didn't see the data.

thanks very much!

  • 7
  • 2
  • 2
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
you could write :
set text1.datasource = rstemployee
text1.datafield = "YourFieldName"
MacRenaAuthor Commented:

nice, emoreau.  thanks.  do you know the syntax for a combobox to show all values in that field?

MacRenaAuthor Commented:

this is only showing the first value in the field but the dropdown is blank.

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Éric MoreauSenior .Net ConsultantCommented:
for a regular combo box, you need to iterate through your recordset and use combo1.additem to add elements to the control.
A couple of things, first, you can use a dataCombo, if you want to automate the filling of the list.  They have a separate source for the list and for the datafield of the combo, so, you could have them bound to different recordsources....

After all of this, you're still taking about having controls on a form "bound" to something.  You've just changed the bound object from a data control to a recordset.

Which is fine, except that in my opinion, it's just too easy to update the recordset accidentally by changing something in a textbox.

I prefer to use unbound controls.  This means that I have to do a bit more work, but I have total control over what happens, and when.

So, for example, if you have a bunch of controls on the screen that you want to display the fields of the current record in the recordset, you'd create a procedure called LoadScreenFromRecordset:

Public sub LoadScreenFromRecordset()
   txtFirstname.Text = RS("FirstName").Value & ""
   txtMiddlename.Text = RS("MiddleName").Value & ""
   txtLastname.Text = RS("LastName").Value & ""
End Sub

Then, you have another routine that moves from the screen to the recordset:

Public sub LoadRecordsetFromScreen()
   RS("FirstName").Value = txtFirstname.Text
   RS("MiddleName").Value = txtMiddlename.Text
   RS("LastName").Value = txtLastname.Text
End Sub

Then, under a move next command button, you might have code like:

Public Sub cmdMoveNext_Click()
   If RS.EOF then RS.MoveLast
   If Not RS.BOF then
   End if
End sub

Lists are a little bit trickier.  Normally you'd have a function that runs at form_load time called FillStates() where you might go, create a recordset of Unique State Names and then fill a cboStates combo/drop-down list manually using .AddItem
MacRenaAuthor Commented:

thanks again, emoreau!  that's the answer I needed!

MacRenaAuthor Commented:

oops, mdougan - your post didn't get in there before I accepted the comment.  I like this, mdougan.  You understand what I'm going for!  I'll get back to you when I try it.  I've got a few points left in my purse!


MacRenaAuthor Commented:

hmmm, I'm not seeing the DataCombo.  I do have Office2000 Developer installed.

HELP says...
>>"In the Visual Basic Editor, with your UserForm open, right-click on or near a control on the Toolbox.
Click 'Additional Controls' to display a list of all available ActiveX controls."<<

well, with my form open, I right-click the ToolBox and these are my options:
Add Tab...
Dockable (checked)

(there is no 'Additional Controls')
I did click Components, but DataCombo isn't listed.
Can you tell me the filename of the control so I can search for it?  Maybe I can register it (?)


HELP cont...
Note   The list is dynamically created, and many controls that appear will have been installed by various applications or downloaded from the Internet. However not all controls are suited to be placed in the host containers listed earlier. Doing so can result in unexpected behavior.

Select the desired control from the list. Office 2000 Developer controls are listed as:
Microsoft ADO Data Control, version 6.0 (OLEDB)

Microsoft Chart Control, version 6.0 (OLEDB)     <----  I Have This -----
Microsoft DataCombo Control, version 6.0 (OLEDB)
Microsoft DataGrid Control, version 6.0 (OLEDB) <----  I Have This -----
Microsoft DataList Control, version 6.0 (OLEDB) <----  I Have This -----
Microsoft Date and Time Picker Control, version 6.0
Microsoft Hierarchical FlexGrid Control, version 6.0 (OLEDB) <----  I Have This -----
Microsoft MonthView Control, version 6.0 (OLEDB)
Click OK.
MacRenaAuthor Commented:

well, at support.microsoft.com, I searched for 'datacombo control' and learned this:

MSDatLst.ocx   Microsoft DataList Control 6.0 (OLEDB)
               Dependency: MSDatLst.dep
               Controls: DataCombo

so I selected Microsoft DataList Control 6.0 (OLEDB) in 'Components', and there they (both) appeared on my Toolbox.


Yes, that will work for you, and, it will more closely match what you might be used to in Access, being able to specify different values to appear in the field, from the values used to update the database etc.

Save your points for other questions, glad if I could have been some help.
MacRenaAuthor Commented:

Thanks very much - I have done just that, because I am going to have plenty of questions before I decide which way to go.

I have posted a new question along these same lines at:


Thanks again!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 7
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now