Solved

Connecting to db w/o data-bound controls

Posted on 2002-03-22
11
188 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

821 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