[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)
