"Steve" wrote in message link.net...
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType acLabel And Ctrl.ControlType acCommandButton Then
If Not IsNull(Ctrl.Value) Then
If InStr(Ctrl.Value, vbCrLf) 0 Then
MsgBox Ctrl.Controls(0).Caption & " Contains A Line Return." & vbCrLf &
vbCrLf _
& "The New Item Can Not Be Saved In The Database" & vbCrLf _
& "Until The Line Return Is Removed." & vbCrLf & vbCrLf _
& "Please Remove The Line Return!"
Cancel = True
Ctrl.SetFocus
Exit Sub
End If
End If
End If
Next Ctrl

' BeforeUpdate Event Is Used For This So InvNumber = StoreItemID When The Item
Is Saved
Me!InvNumber = Me!StoreItemID
If Me!SetThisItemAsTheDefaultNewItem = True Then
Call SetDefaultValues
Else
Call RemoveDefaultValues
End If
Me!Title.SetFocus
End Sub

Public Sub SetDefaultValues()
On Error GoTo ErrorHandler
Me!Title.DefaultValue = CQuote & Me!Title & CQuote
Me!FeatureFlag.DefaultValue = CQuote & Me!FeatureFlag & CQuote
Me!ShortDesc.DefaultValue = CQuote & Me!ShortDesc & CQuote
Me!Description.DefaultValue = CQuote & Me!Description & CQuote
Me!ThumbImage.DefaultValue = CQuote & Me!ThumbImage & CQuote
Me!FullImage.DefaultValue = CQuote & Me!FullImage & CQuote

If Not IsNull(Me!Category) Then
Me!Category.DefaultValue = CQuote & Me!Category & CQuote
Else
Me!Category.DefaultValue = ""
End If
If Not IsNull(Me!Section) Then
Me!Section.DefaultValue = CQuote & Me!Section & CQuote
Else
Me!Section.DefaultValue = ""
End If
If Not IsNull(Me!Aisle) Then
Me!Aisle.DefaultValue = CQuote & Me!Aisle & CQuote
Else
Me!Aisle.DefaultValue = ""
End If

Me!Alt1.DefaultValue = CQuote & Me!Alt1 & CQuote
Me!Alt2.DefaultValue = CQuote & Me!Alt2 & CQuote
Me!Alt3.DefaultValue = CQuote & Me!Alt3 & CQuote
Me!ListValue.DefaultValue = CQuote & Me!ListValue & CQuote
Me!SalePrice.DefaultValue = CQuote & Me!SalePrice & CQuote
Me!SellingPrice.DefaultValue = CQuote & Me!SellingPrice & CQuote
Me!DatePurchased.DefaultValue = CQuote & Me!DatePurchased & CQuote
Me!ItemCost.DefaultValue = CQuote & Me!ItemCost & CQuote
Me!ShipCost.DefaultValue = CQuote & Me!ShipCost & CQuote

If Not IsNull(Me!InventoryLocation) Then
Me!InventoryLocation.DefaultValue = CQuote & Me!InventoryLocation & CQuote
Else
Me!InventoryLocation.DefaultValue = ""
End If

Me!Inventory.DefaultValue = CQuote & Me!Inventory & CQuote
Me!ReorderPoint.DefaultValue = CQuote & Me!ReorderPoint & CQuote
Me!Quantity.DefaultValue = CQuote & Me!Quantity & CQuote


ExitHe
Me!Title.SetFocus
Exit Sub
ErrorHandler:
MsgBox Err.Description, , "Error# " & Err.Number
Resume ExitHere
End Sub

Public Sub RemoveDefaultValues()
Me!Title.DefaultValue = ""
Me!FeatureFlag.DefaultValue = ""
Me!ShortDesc.DefaultValue = ""
Me!Description.DefaultValue = ""
Me!ThumbImage.DefaultValue = ""
Me!FullImage.DefaultValue = ""
Me!Category.DefaultValue = ""
Me!Section.DefaultValue = ""
Me!Aisle.DefaultValue = ""
Me!Alt1.DefaultValue = ""
Me!Alt2.DefaultValue = ""
Me!Alt3.DefaultValue = ""
Me!ListValue.DefaultValue = ""
Me!SalePrice.DefaultValue = ""
Me!SellingPrice.DefaultValue = ""
Me!DatePurchased.DefaultValue = ""
Me!ItemCost.DefaultValue = ""
Me!ShipCost.DefaultValue = ""
Me!InventoryLocation.DefaultValue = ""
Me!Inventory.DefaultValue = ""
Me!ReorderPoint.DefaultValue = ""
Me!Quantity.DefaultValue = ""
End Sub

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

You might be able to do this with bookmarks as well.

Hope this helps.

Bruce