Solved

Connecting to db w/o data-bound controls

Posted on 2002-03-22
11
185 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 69

Expert Comment

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

Author Comment

by:MacRena
Comment Utility

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
Comment Utility

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

0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 4

Author Comment

by:MacRena
Comment Utility

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



0
 
LVL 4

Author Comment

by:MacRena
Comment Utility

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
Comment Utility

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
Comment Utility




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
Comment Utility
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
Comment Utility

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now