Skip site navigation (1) Skip section navigation (2)

Some advice re:bound/unbound forms for MS Access

From: "Joel Burton" <jburton(at)scw(dot)org>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Some advice re:bound/unbound forms for MS Access
Date: 2000-11-30 19:46:01
Message-ID: 3A266829.31508.4FE16D@localhost (view raw or flat)
Thread:
Lists: pgsql-interfaces
[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(at)scw(dot)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 return a 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 read the 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("Save record?", 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 c In f.Controls
        If InStr(c.Tag, "Req;") Then
            If IsNull(c.Value) Then
                
                ' This field is 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 found any errors, output a message and return w/failure 
code.
    
    If output <> "" Then
        msgbox "You have empty fields that cannot be empty(dot)(at)You 
have left the following field(s) empty," _
            & " but they require input:" & output _
            & "@Fill out this information 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(at)scw(dot)org
Support Center of Washington (www.scw.org)

Responses

pgsql-interfaces by date

Next:From: Tim UckunDate: 2000-11-30 22:02:45
Subject: Re: Some advice re:bound/unbound forms for MS Access
Previous:From: npdavisDate: 2000-11-30 19:01:10
Subject: server side interfaces

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group