Some advice re:bound/unbound forms for MS Access - Mailing list pgsql-interfaces
From | Joel Burton |
---|---|
Subject | Some advice re:bound/unbound forms for MS Access |
Date | |
Msg-id | 3A266829.31508.4FE16D@localhost Whole thread Raw |
Responses |
Re: Some advice re:bound/unbound forms for MS
Access
|
List | 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@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)
pgsql-interfaces by date: