• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • 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...

****************
'Reference:
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!
mac

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

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

0
 
MacRenaAuthor Commented:

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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
É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.
0
 
mdouganCommented:
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()
   RS.MoveNext
   If RS.EOF then RS.MoveLast
   If Not RS.BOF then
      LoadScreenFromRecordset
   Else
      ClearScreen
   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
0
 
MacRenaAuthor Commented:

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



0
 
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!

mac


0
 
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:
Components...
Add Tab...
-----------
Dockable (checked)
Hide

(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.
0
 
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
                         DataList

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

WooHoo!


0
 
mdouganCommented:
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.
0
 
MacRenaAuthor Commented:

mdougan,
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:

http://www.experts-exchange.com/visualbasic/Q.20281168.html

Thanks again!

Mac
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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