Build an unbound form in Acess 2007

Question

I have an access application that has a form that allows the user to enter case notes. The main field of this form is tied to a SQL Server varchar(MAX) field in the source table. Since the users switched to Access 2007, their program keeps crashing when they are on the case notes form. As a possible solution to this problem, I would like to try unbinding this form and re-building it as an unbound form.

This form needs to be able to add and update records into my SQL Server database. It also needs to be able to browse between records. I guess I am at a loss as to where to start. Any suggestions/code snippets is appreciated.

Answer 1

As a starting point, try Google on "unbound form in Access". Don't be distracted by PacMan! ;)

Anyway, the basic idea of an unbound form is to load the data into the unbound controls from a recordset, then to save it back when edits are done. This means you need these things:

1. controls to select the needed record, some kind of find functionality.

2. code to open the recordset and write the data from the fields into the corresponding controls on the form.

3. controls to save the record back to the database, which will use a SQL update to write the values in the unbound controls back to the database. I prefer not to update fields that have not changed (because I do lots of replicated Jet apps, and multiple updates can lead to unnecessary replication conflicts). You can compare the data in the undound controls to the data in the original recordset (if you open it as a snapshot type recordset, it won't reflect any updates since it was opened), and write your SQL UPDATE for only the fields where the values don't match. You'll have to account for Nulls.

The common practice is to name the controls exactly the same as the fields they correspond with so you can loop the recordset's fields collection and load the data into the controls:

  For Each fld In rs.Fields
    Me.Controls(fld.Name) = fld.Value
  Next fld

You can do likewise for saving the data and checking the control values against the original recordset values.

I don't know if this works with SQL Server VarChar() fields or not, but you could also try what I call a "semi-bound" form, where you load the recordset with the form's RecordSource property but don't bind the fields to controls. Thus, the form is bound, but the controls are not. I very often do this with a fully bound form where I make the memo fields unbound (to avoid the danger of memo field pointer corruption in Jet/ACE back ends). In that case, with a bound form recordset and an unbound textbox for editing, you'd do this:

1. in the form's OnCurrent event, load the unbound field(s) data into the corresponding unbound textbox(es).

2. in the AfterUpdate event(s) of the unbound control(s), write the data in the unbound textbox(es) back to the recordsource.

Those two steps would basically look something like this:

  Private Sub Form_Current()
    Me!txtMemo = Me!Memo
  End Sub

  Private Sub txtMemo_AfterUpdate()
    Me!Memo = Me!txtMemo
    Me.Dirty = False
  End Sub

With a Jet/ACE back end, you'd want to save the record immediately after you write the memo field value because otherwise you haven't avoided the danger of corrupting the memo field pointer. With a SQL Server back end, you may or may not need to do that, since the issues are completely different. Saving will release the write lock, but you might not need to avoid that.

Also, I'm assuming that the VarChar() data can be read from the form's underlying Recordsource and written into the textbox. You'll have to see if that works.