Binding Controls to Access Database by Code- Using Ado

[hansel]

I have witnessed the slow-down in performance,
particularly when one has a form with many Adodc controls (say, for example,
when one needs to make selections from DataCombos). In my case, the form has
many textboxes and about seven Datacombo controls. I am led to believe the
approach I used by binding the controls to related Adodc controls on the
form has a performance penalty. So, I am trying to re-write most of what I
have in code (i.e. without using the ado controls supplied in vb6).

I progressed fine, but ran into trouble when
trying to bind the datacombo in code. I insert below my code:


' Code in MainForm
' This is just a demo test-drive program.

private cbo as DataCombo
private depRS as ADODB.recordset 'I created this recordset for the
datacombo, which contains all the department names (about 90) from
' a table
in the database called Dept. There are two tables in the database- Dept and
Employees.
' The database fields are as follows:
' for table named Dept-> ID, DeptName
' for table named Employees-> ID, DeptName, FirstName, LastName

private recordset as ADODB.recordset ' for main employees table
private connection as ADODB.connection

private Sub cmdConnect_Click()
set recordset = new ADODB.recordset
set depRS = new ADODB.recordset
set connection = new ADODB.connection

connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data
Source=c:\windows\desktop\dbtest.mdb"

connection.Open
recordset.Open "Employees", connection, adOpenKeyset
depRS.Open "Dept", connection, adOpenKeyset
' I have attempted to initialise the datacombo with the data from the
database table
for Each cbo In me.dcboDeptName
set cbo.DataSource = recordset
set cbo.RowSource = depRS
cbo.DataField = "DeptName"
cbo.ListField = "DeptName"
cbo.BoundColumn = "DeptName"
next
'Bind the text boxes to the data provider
populateFields
End Sub

private Sub populateFields()
txtFirstName.Text = recordset.Fields("FirstName")
txtLastName.Text = recordset.Fields("LastName")
dcboDeptName(0).Text = recordset.Fields("DeptName") ' I am not sure
about this one
End Sub

private Sub cmdNext_Click()
on error GoTo GoNextError
recordset.MoveNext
If recordset.EOF then recordset.MoveLast
populateFields
Exit Sub

GoNextError:
MsgBox Err.Description
End Sub

private Sub cmdPrev_Click()
on error GoTo GoPrevError
recordset.MovePrevious
If recordset.BOF then recordset.MoveFirst
populateFields
Exit Sub

GoPrevError:
MsgBox Err.Description
End Sub


private Sub Form_Unload(cancel as Integer)
If Not (recordset is nothing) then
recordset.Close
depRS.Close
connection.Close
set recordset = nothing
set connection = nothing
End If
End Sub




Well, my problem is that when I make a department selection from the
datacombo, It is not updating the "DeptName" field in the Employees table.
Also, when I fire the next or previous button the fields on the forms are
displayed accordingly. However, I try to make a department selection and
then press Prev or Next buttons I get the following error: "The change was
canceled during notification; no columns are changed"

Well, that's it. I'd appreciate any suggestions or examples or corrections
regarding the use of DataCombo with code.

Sincerely,
Hansel
hansel@solutions2000.net

[Tahbaza]

Your direction of rewriting the data bound code
into ADO objects is the right way to go.
Don't use the DataCombo. Use a normal combobox
and load it with the additem method while inside
a loop that looks like this :

' populate your recordset here <=
Do while not rs.eof
cmb.AddItem RS!FieldName
cmb.ItemData(cmb.NewIndex) = RS!NumericPrimaryKey
rs.movenext
loop
set rs = nothing



To retrieve this record and update other fields on
your form you have the primary key in the itemdata
property of the combobox. You can get it like this for the currently selected item :

selectedkey = cmb.itemdata(cmb.listindex)




== Ali R. Tahbaz, MCSD ==========================
"Whether you think that you can, or that you can't, you are usually right."
--Henry Ford

[hansel]

OK Thanks.
I am able to
1) Enter items from the combo into the database main table.
2) read in the available choices from some table in the database.
However, I still have a problem.

If I press the next, previous, last or first buttons the combo field does not scroll through the previously selected values as does the text boxes. I have attached some of the code:


Dim withevents adoPrimaryRS as Recordset
Dim withevents adoAgeRS as Recordset

private Sub Form_Load()
Dim db as Connection
set db = new Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" + App.Path + "\mydb.mdb;"

set adoPrimaryRS = new Recordset
adoPrimaryRS.Open "select EmployeeID,FirstName,LastName,DOB,Height,Weight, Age from EmployeeData", db, adOpenStatic, adLockOptimistic

set adoAgeRS = new Recordset
adoAgeRS.Open "select Age from Age", db, adOpenStatic, adLockOptimistic

Dim oText as TextBox
'Bind the text boxes to the data provider
for Each oText In me.txtFields
set oText.DataSource = adoPrimaryRS
next

With Combo1
set .DataSource = adoAgeRS
Do While Not adoAgeRS.EOF
.AddItem adoAgeRS!Age
.ItemData(.NewIndex) = adoAgeRS!Age
adoAgeRS.MoveNext
Loop
set adoAgeRS = nothing
End With
End Sub

private Sub Combo1_Click()
' updates age field in main table with select value from combobox
selectedkey = Combo1.ItemData(Combo1.ListIndex)
adoPrimaryRS!Age = CStr(selectedkey)
End Sub

private Sub cmdNext_Click()
on error GoTo GoNextError

If Not adoPrimaryRS.EOF then adoPrimaryRS.MoveNext
If adoPrimaryRS.EOF And adoPrimaryRS.RecordCount > 0 then
Beep
'moved off the end so go back
adoPrimaryRS.MoveLast
End If

Exit Sub
GoNextError:
MsgBox Err.Description
End Sub





When I click the next button, the displayed record scrolls to the next set of records: ie the fields are updated with the next record, but this does not happen with the combo. What am I missing?


Sincerely,
Hansel
hansel@solutions2000.net

[Tahbaza]

You're on the right track, but don't use bound controls or properties and methods of normal controls intended for use with bound controls.
The datasource / datafield / datamember / recordsource properties are all databound trash.

Here is some code that will load a combo with a field's values. When the combo is clicked two textboxes will reflect the values associated with the currently selected combo record.

Sub Form_Load()
RefreshCombo
End Sub

private Sub RefreshCombo()
on error GoTo Err_
Dim RS as Recordset
cmb.Clear ' your combo or listbox
set Rs = new Recordset
Rs.CursorLocation = adUseClient
RS.Open "SELECT DISTINCT Age FROM Age", _
"Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=false;Data Source=" & App.Path & "\mydb.mdb", adOpenStatic, adLockOptimistic
Do While Not RS.EOF
cmb.AddItem RS!Age
RS.MoveNext
Loop
' BE SURE THE STYLE property OF THE COMBO is = 2
' AT DESIGN time
If cmb.ListCount > 0 then cmb.ListIndex = 0
set RS = nothing
Exit Sub
Err_:
RaiseError me.Name, "RefreshCombo"
End Sub

private Sub cmb_Click()' updates age field in main table with select value from combobox
Dim RS as Recordset
set RS = new Recordset
RS.Open "SELECT EmployeeID,FirstName,LastName,DOB,Height,Weight FROM EmployeeData WHERE Age = " & _
cmb.Text, _
"Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=false;Data Source=" & App.Path & "\mydb.mdb", adOpenStatic, adLockOptimistic
' the below should be replaced with code to
' load each returned record here into a listview or msflexgrid
' this code only shows the first returned record
if Not RS.EOF then
txtDOB.Text = iif(isnull(RS!DOB),"",RS!DOB)
txtFirstName.Text = iif(isnull(RS!FirstName),"",RS!FirstName)
' and so on...
else
txtDOB.Text = ""
txtFirstName.Text = ""
' and so on...
End If
set rs = nothing
End Sub



This scheme will display one record as the result of an age choice in the combo.
(This is of limited usefulness.) If I were you I
would not use just the textboxes to display the current record. Use a listview or an MSFlexgrid control to display the results of the _Click event query. When a record in the listview or msflexgrid is selected THEN refresh the textboxes as done in the above example.

Good luck...

== Ali R. Tahbaz, MCSD ==========================
"Whether you think that you can, or that you can't, you are usually right."
--Henry Ford