Some advice re:bound/unbound forms for MS Access

[This isn't a question, it's some advice about forms in Microsoft 
Access w/pgsql backend based on what my organization has been 
using.]

[Warning! VisualBasic code ahead. If this offends your religious 
sensibilities, skip to the next message. :-) ]

In Access, you normally can use either bound or unbound forms. 
Bound forms are those that
actually link directly to tables; unbound forms are those where you 
have to handle all of
the data reading and writing yourself.

When working w/Jet (Access native) tables, bound forms are almost 
always used. They offer many advantages:

* can use Find
* can use Filter
* can use Sort
* can use Record Numbers / navigation bar
* can use Spell Check
* can use regular Delete button, menu, etc.
* can use Continous Form mode, and Datasheet mode (very helpful!)
* no VBA programming required

When working with an ODBC datasource (such as PostgreSQL), 
sometimes unbound forms are a better solution. They offer:

* more control over reading/writing of data to backend
* _much_ better handling of errors
* less gratuitous reading of data from backend server (when you 
open a bound form, Access might read 10+ records of information, 
even if you only will ever use one single record.)

For many of the forms we use, we can live w/reading additional 
records, and don't need special control of data reading. However, 
with bound forms, the error handling options w/ODBC are *terrible* -
- Access will print out the PostgreSQL error message, but you 
*can't* trap this--all you can get is the Access error code, which is 
terribly generic. This is unsuitable for our applications.

However, I hate to let go of the great features of Find, Filters, Sort, 
etc. I could create workarounds for many of these (a sort button on 
the form that modifies the form recordsource and requeries it, my 
own delete button, a separate option to see the data in datasheet 
like-view, etc.), but for many simple forms, this would require a lot 
of programming and would, in many cases, run slower than the 
native Access solution.

Instead, we've begun using 'half-bound' (cf half-baked?) forms in 
many places. This is a 95% bound form (it has a datasource; it can 
be created using the normal Form Wizards). The only difference is 
that we trap the update action so that we perform the 
updates/inserts manually, and can therefore trap the PostgreSQL 
error messages. This gives us many advantages:

* Find, Filter, Sort, Spell Check, etc., all work as usual in bound 
forms.
* Can trap the exact PostgreSQL error message for much better 
messages/error handling
* There's only one VBA routine to customize per form--only takes 
about 3-10 minutes.

To do this:

0) Make sure your ODBC settings are correct so that ODBC lets you 
update and insert records w/o the #Deleted problems and such. See 
the PostgreSQL + Access FAQ (described below) if you need help.
1) Create a normal, bound form either by hand or with the wizard of 
your choice.
2) In form properties, create the Form_BeforeUpdate action, and 
cut and paste the code below.
3) Edit the middle of this action for your form. You'll need to 
manually assign every field from the form to the recordset. For 
example, if you have a mailing list-type table, you should add:
!fname = fname!mname = mname!lname = lname!address = address

... and so on, for all of your fields. It's critical that you keep this list 
up to date--if you add fields to your form, or change the name of 
fields, your form will not longer update the PostgreSQL database 
properly!
4) Read the comments in the code about auto-saving versus 
prompting the users, and decide if you want to always auto-save, 
prompt the users each time, or let the users control this via a form 
check box.

[Optional]

In addition, rather than having users receive the default, somewhat 
ugly error message if they leave a not-null field null (the error 
message doesn't tell them which field is the problem!), we 'pre-
check' their form for not-null fields that are null. To implement this:

5) Uncomment the not-null checking code at the start of the 
Form_BeforeUpdate action
6) Add the following to the .Tag property of every not-null field 
control on your form: "Req;"
7) Copy the function Link_FailRequired() into a module in your 
database. (You could put in in the form module, but, since all forms 
can share this, it will be smaller and easier to maintain if you just 
have one copy in the modules.)



This seems to work well for us. For very large tables (many 
memo/text fields, dozens of fields, etc.), it may still seem sluggish 
when you open a new form, as Access reads in all those 
unnecessary records; if this bothers you, you can play with the 
settings of your MSysConf table, or change over to a completely 
unbound form, and live with the loss of features.

(The best book I've read on advanced form issues, including client-
server unbound forms, is Getz et al, 
_Access_Developers_Handbook_ (for Access 2000, it's the second 
volume that really covers these issues; for Access 97, it's a one-
volume book.)

About 2-3 months ago, I started compiling FAQ items for an 
Access+PostgreSQL FAQ. I haven't had much chance to clean it up, 
but it's got a lot of stuff in it, and is available at 
http://www.scw.org/pgaccess. Please consider adding your own 
tips, correcting it, or giving it a good copy-editing.

I'd appreciate any feedback or improvements anyone has!

Thanks,
Joel Burton   jburton@scw.org

P.S. The current "standard" (6.50) ODBC driver does *not* report 
referential integrity errors, so this code won't even notice them. (I 
don't think there's *any* way to notice them in Access if the driver 
doesn't report them as errors.) However, the CVS and snapshot 
versions of the source code do fix this problem (& also work w/the 
7.1devel versions of pgsql, which the "standard" ODBC driver 
doesn't.) You'll need Visual C++ to compile this, or you can 
download a compiled copy from http://www.scw.org/pgaccess.

-------

Private Sub Form_BeforeUpdate(Cancel As Integer)      ' Check to see if any required fields are left blank. This isn't

strictly neccessary   ' (PostgreSQL will find and report these errors), but it's much 
more polite and   ' quick to find them all at once, and easier to report on which 
field is the problem.

' Uncomment this if you want to use the (optional) pre-check for 
not-null but null fields.  
'    If Link_FailRequired(Form) Then
'        Cancel = True
'        GoTo P_Exit
'    End If      ' If we didn't trap this action, Access would just save the record 
as usual, which   ' would be fine in normal circumstances.   '   ' However, if there is an error, Access will only
returna generic 
 
error box,   ' which we could trap w/the OnError action. *However*, at that 
point, we can only   ' get the Access error code, which is *very* generic, and would 
have no idea what   ' actually happened w/the PostgreSQL backend.   '   ' By doing the record update manually, we can
readthe actual 
 
PostgreSQL error   ' message, and provide a much more customized error message or 
routine.   '   ' This should be used w/bound forms.      On Error GoTo P_Err   Dim intResponse As Integer

' chkAutoSave is an unbound checkbox on my forms that allows 
users to decide if
' they want to be prompted to save records, or just have records 
autocommitted (the
' "normal" behavior in Access.) Add the checkbox to your form if you 
want to give users
' control over this, or change this code to always/never prompt.      If Not chkAutoSave Then       intResponse =
msgbox("Saverecord?", vbQuestion + 
 
vbYesNoCancel + vbDefaultButton1)   Else       intResponse = vbYes   End If      Select Case intResponse       Case
vbYes          ' Save record by writing it by hand.                      Dim rst As Recordset           Set rst =
Form.RecordsetClone          With rst                              ' If this is a new record, do this as insert;
otherwise,as  
edit               If Not Me.NewRecord Then                   .Bookmark = Form.Bookmark                   .Edit
     Else                   .AddNew               End If                              ' update next few lines for each
form              !reztypecode = reztypecode               !reztype = reztype               !reztypeshort =
reztypeshort                             .Update           End With           Set rst = Nothing                      '
"Undo"form changes so the form doesn't try to do a 
 
normal (bound form)           ' write of our data, which it would find confusing. Since the 
record is already           ' save (by above), this just undirties the record so we can 
move on.                      Me.Undo              Case vbNo           ' Don't want to save; just undirty the record.
                  Me.Undo              Case vbCancel           ' Don't want to save, and don't want to do whatever 
 
action was pending           ' (close, move to next record, find, etc.) By cancelling, 
Access should also           ' cancel next action. (Sometimes, there may be a followup 
dialog box, such as           ' "Action was cancelled" or "You can't save this record at 
this time", but           ' this is a minor issue, and explain it to the users should 
make this acceptable.                      Cancel = True   End Select

P_Exit:   Exit Sub


P_Err:   ' Handle the error by calling our generic PostgreSQL error-printing 
routine.   ' This digs an appropriate error message out of a table and 
presents it to user.

' put your own error handling routine/code here. If you don't have 
anything,
' MsgBox (Errors(0).Description) will at least let your users see the 
pgsql
' message, even if it is a bit ugly. At SCW, we do a lookup to a table 
that stores
' the PostgreSQL error text, and translates this to a much more 
human-oriented
' message; if you're interested in this code, drop me a line.   
'    link_err (Errors(0).Description)      ' Cancel, but don't Undo. This keeps the data on the screen, and 
cancels whatever   ' the user was trying to do (close, move, find, etc.)   Cancel = True      Resume P_Exit   
End Sub

-------

Public Function Link_FailRequired(f As Form) As Boolean   ' Check each field of form, looking to see if field is
required,but 
 
null   ' Present an error message of all fields that are null but should be 
required.   '   ' Fields are marked as required by having string "Req;" in the .Tag 
property      Dim c As Control   Dim output As String   Dim name As String      Link_FailRequired = False      For Each
cIn f.Controls       If InStr(c.Tag, "Req;") Then           If IsNull(c.Value) Then                              ' This
fieldis null but shouldn't be. Get it's name.               ' If it has a label, use this (remove any trailing colons 
 
from it first),               ' otherwise, use its name.                              If c.Controls.Count = 1 Then
            name = c.Controls(0).Caption                   If Right$(name, 1) = ":" Then name = Left$(name, 
 
Len(name) - 1)                   output = output & vbCrLf & "   " & name               Else                   output =
output& vbCrLf & "   " & c.name               End If                          End If       End If   Next      ' If we
foundany errors, output a message and return w/failure 
 
code.      If output <> "" Then       msgbox "You have empty fields that cannot be empty.@You 
have left the following field(s) empty," _           & " but they require input:" & output _           & "@Fill out
thisinformation and continue.", vbCritical       Link_FailRequired = True   End If   
 
End Function




keywords: microsoft access msaccess ms-access postgresql pgsql 
bound unbound forms odbc faq
--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)