"Bruce" wrote in message
om...
I think I would use a somewhat different approach here. Let the form
save the record. In the AfterUpdate proc of the form, save whatever
key value or values you need to be able to retrieve the record that
you just saved. For example, if your key value is StoreItemID and
that's a long integer, do something like:

dim lngPrevItemID as long ' Do this in the (General) (Declarations)
section of the form so it will be available to all procs in the form

Form_AfterUpdate()

lngPrevItemID = Me.StoreItemID

End Sub

Then in the form's Current event, check to see if you're on a new
record. If so, use the key value you just stored to look up the
previously saved record and load in all the default values. For
example, do something like the following in Form_Current():

Dim rst As Recordset

If Me.NewRecord Then
Set rst = CurrentDB.OpenRecordset("select * from MyTable where
StoreItemID = " & lngPrevItemID
Me!Field1.DefaultValue = rst!Field1
...
Me!Fieldn.DefaultValue = rst!Fieldn
rst.Close
Set rst = Nothing
Else
Me!Field1.DefaultValue = ""
...
Me!Fieldn.DefaultValue = ""
End If

-or-

Dim rst As Recordset

If Me.NewRecord Then
Set rst = Me.RecordSetClone
rst.FindFirst "StoreItemID = " & lngPrevItemID
Me!Field1.DefaultValue = rst!Field1
...
Me!Fieldn.DefaultValue = rst!Fieldn
rst.Close
Set rst = Nothing
Else
Me!Field1.DefaultValue = ""
...
Me!Fieldn.DefaultValue = ""
End If

My first thought would be to write a class that extends a textbox to
include the behaviour that is wanted. You could handle both the
setting of a defaultvalue and the removal of CRLF's.

Something like:

class
Option Compare Database
Option Explicit

Private WithEvents frm As Access.Form
Private txtbox As Access.TextBox

Public Sub Init(tb As Access.TextBox)
Set frm = tb.parent
Set txtbox = tb
frm.BeforeUpdate = "[Event Procedure]"
End Sub

Private Sub frm_BeforeUpdate(Cancel As Integer)
If Len(txtbox.Value & vbNullString) 0 Then
txtbox.Value = Replace(txtbox.Value, vbCrLf, " ")
txtbox.DefaultValue = Chr$(34) & txtbox.Value & Chr$(34)
End If
End Sub

Private Sub Class_Terminate()
If Not txtbox Is Nothing Then Set txtbox = Nothing
If Not frm Is Nothing Then Set frm = Nothing
End Sub
/class

Set and load all the textboxes you want to have the behaviour
into a collection in the form's open event.