Solved

Connecting to db w/o data-bound controls

Posted on 2002-03-22
11
187 Views
Last Modified: 2013-11-26
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
Comment
Question by:MacRena
  • 7
  • 2
  • 2
11 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6889797
you could write :
set text1.datasource = rstemployee
text1.datafield = "YourFieldName"
0
 
LVL 4

Author Comment

by:MacRena
ID: 6889840

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

0
 
LVL 4

Author Comment

by:MacRena
ID: 6889849

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

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 70

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 6889855
for a regular combo box, you need to iterate through your recordset and use combo1.additem to add elements to the control.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6889916
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
 
LVL 4

Author Comment

by:MacRena
ID: 6889923

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



0
 
LVL 4

Author Comment

by:MacRena
ID: 6889939

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
 
LVL 4

Author Comment

by:MacRena
ID: 6890016

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
 
LVL 4

Author Comment

by:MacRena
ID: 6890067




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
 
LVL 18

Expert Comment

by:mdougan
ID: 6890127
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
 
LVL 4

Author Comment

by:MacRena
ID: 6894189

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question