[AccessD] Framework book - was caption from code

Jim Lawrence accessd at shaw.ca
Fri Jul 15 12:40:21 CDT 2005


Hi John:

I will answer inline...

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Wednesday, July 13, 2005 9:29 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Framework book - was caption from code

OK, bear with me since I am new to this unbound (but data aware) form
business.

Forms have a recordset correct, just not bound to one.
Controls on each form are created manually and the named.  The corresponding
field is set how?  IOW how is the control made aware of the data that it is
unbound to.  Do you (currently) use classes for this at all?  

>
I do not use classes to do this but should... It definitely leads itself
toward a class. I am/have doing classes but they are in VB and subsequently
VB.Net. The fields are filled from the recordset like: 

With rsCardList
 If .BOF = False And .EOF = False Then
  txtNameonCard = ![Name] 
 end if
End With

..or...

Here is a very simplified version of filling the Form fields from memory.
The recordset fields have to precisely match the form layout for this to
work but position of the fields  
...
For i = 0 to frm.Controls.Count
 With MyRecordset
  Frm.Controls(i) = .Fields(i)
 End With
Next i
...

.. and a while loop if a subform
<

Off the top of my head, I see a need to load a class instance for each
control, which will hold the field name that the control loads data from and
writes changes back to.  A "dirty" variable to indicate that the control
(data) has been modified.  The form class then has a control scanner that
loads a control instance for each control discovered on the form.  

>
It is not the form being 'bound' or 'unbound' that indicated whether a field
has been changed 'dirty' on a form but it is whether the field .text or
.value is different. To test whether form values have been changed you can
check the 'dirty' flag, monitor the validation event, compare the field
.text and .value info or compare the values against the recordset values.

Each combo box list values are collected first, used to populate the list
data and generally remain static until the user leaves the form or even the
application. If the list value are continually changing then they must be
managed like standard form fields.
<

The form init will init the form class, then go back and initialize each
control with the field name that it is unbound to.  The form class knows how
to read data and then iterates the control classes pushing in data values to
the control class, which in turn pushes the value in to the control.  Or the
control class just uses the control as the structure that holds the data
value.  Correct me if I'm wrong, but unbound controls don't manipulate the
OldValue property the way that bound controls do, correct?  

>
Actually they do; see above...
<

Thus the control
class needs to store the value into an OldVal variable in the control class.


What events work correctly with unbound forms and controls, and which events
do not fire or exhibit issues due to the unboundness?  IOW it seems that a
form BeforeUpdate / AfterUpdate etc simply wouldn't fire since there is no
bound recordset to monitor.  What about controls?  

>
As soon as a change is made to a form/record the data for the specific
recordset/form is re-acquired. The following assumes a text request though I
try to keep most of my calls done through queries or stored procedures. This
command attempts to lock out any other uses until the recordset is closed.
It works as if it was originating from a 'bound' form and really there is
only two lines of important code... the rest relates to the ADO data
methodology. Below is part of a snippet:

...
With objCmd
 .CommandType = adCmdText
 .CommandText = "select * from Company where recordID = " & Str(glbRecordID)
End With
    
Err.Clear
rsCompany.Open objCmd, , adOpenDynamic, adLockPessimistic
If Err.Number > 0 Then

 ...
 Handle the errors or warnings here
 ...
    
End If
...

Here is a site you can check over with more code and samples"
http://support.sas.com/rnd/eai/oledb/rr_locking.htm   
<

Is there any documentation anywhere that discusses the event differences
between bound and unbound forms and controls?  My bound object classes sink
the events for the object that they represent and can (and do) run code in
those events.  I need to know the differences between bound object events
and unbound object events.

I actually have a tiny little form (8 controls or so) that I need to take
unbound to eliminate locking issues that I simply cannot resolve with the
bound version.

>
Hope this helps...Jim
<

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, July 13, 2005 12:17 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Framework book - was caption from code


Hi John:

The methods I have used were designed about eight years ago and have
copied/improved again and again. Here are the basics and a step by step
methodology:
1. I always use ADO-OLE. (One exception is MySQL... a bit of a pain. If
someone could figure out a method that would not require a station by
station visit to install the MySQL ODBC drivers I would be for ever
indebted.)
2. The forms and subforms are populated as the user moves through the
records and but until the user actually changes field content on a form or
attempts to delete the current record, the recordset used to feed the form
is static. 3. a) A record or group of records in the case of a subform being
displayed, are first pulled into a recordset, and content are then used to
populate the form collection. In theory the record exists in three places.  
b) If there is a change of deletion request the recordset is locked by
creating a dynamic recordset with record-lock attribute.
c) If an 'already locked' error occurs when this lock is being applied then
the user is informed of the condition and given the appropriate options.
d) When using a 'real' :-) SQL DB (MS SQL/Oracle etc.) much of the record
handling is managed internally as soon as the 'BeginTrans' is applied and
closed or resolved when the 'CommitTrans' or 'RollbackTrans' runs.

It may initially seem a little complex but it gives a full range of options
for managing the data and it allows the client to build large databases with
many users and only requires the purchase a few licenses/seats. (ie. 20
licenses and 100 users.)      

Using ADO makes is easy to switch or attach different data sources with
little effort. (In theory one line of code.) Even web applications can be
created leveraging the same methods. That is not totally true but given that
SQL versions are standardizing coupled with the use of Store
Procedures/Queries, with passing parameters, very few changes are needed. I
am still pacing around the XML pool but believe in the long run that is
where to go.

So there is the 'thumb-nail' sketch of the 'non-bounder' application
implementation methodology.

Jim   

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
Sent: Wednesday, July 13, 2005 4:35 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Framework book - was caption from code

Jim,

My framework (the part that had to do with forms and controls) was indeed
aimed at bound forms.  However the concept works for unbound forms as well.
Indeed it would be even more critical to use a framework as you the
developer are doing so much of the work that Access was doing for you -
loading all of the data into the controls, checking for updates to the data
by other people before you wrote changes back to the table, writing the data
back from controls into fields in the tables, stuff like that.  All of that
code would be framework material.  Once written and stored in the framework,
the doing of it would be automatic by the framework.

If you are one of the unholy (unbounders) why don't you give a thumbnail
sketch of how you manage all of that work.  I will then brainstorm how I
would look at doing it in a framework.  Remember that the concept of a
framework is to make it as automatic as possible.  For example (in a bound
form) I have a form class and I have a class for each control type.  The
form class runs a control scanner, gets the control type of each control,
loads a class instance for each control found.  Once this is done, the form
class and control classes can work together to do tasks.  

As an example, I can literally just drop a pair of controls on the form, a
text box named RecID that exposes (is bound to) the PK (always an
autonumber) and an unbound combo called RecSel.  The names are specific and
if the control scanner finds this pair of controls it knows that a record
selector exists and runs the code.  The afterupdate of the combo by that
name calls a method of the parent form saying "move to recid XXXX".  The
column 0 of the combo is the PK of the record in the form so it knows the
PK.  The form's OnCurrent knows that it needs to keep the combo synced to
the displayed record in the form so it calls a method of the combo class
telling it to display the data relevant to the current record in the form.
Thus the form, the combo and a text box all work as a system to form a
record selector.  If the developer drops these two controls on the form he
"just has" a record selector.  Nothing else is required - other than binding
RecSel to the PK field and setting the combo's query to display the data and
get the PK of the records in the table.

As an unbounder, you need to think about how to achieve this kind of
"automaticness" in the things you do.  Naturally some tasks do require
feeding data into init() methods of classes to set the classes up to do
their job.  After that though the "systems" need to just work together. Load
the data into the unbound form / controls.  Store flags in each control's
class saying that control's data was modified.  Scan for all controls with
modified data and update the underlying data in the record. Check for data
changes in the data you are about to update and inform the user that another
user edited the record since you loaded the data.  Etc.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Wednesday, July 13, 2005 2:29 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Framework book - was caption from code


Hi John:

Save a copy for me. It sounds like a good read...but I guess it only works
with bound forms.(?)

Jim 



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list