Adding Form Overwrites Record

  • Thread starter Thread starter Janet Ciegler
  • Start date Start date
J

Janet Ciegler

Another problem, please: Sometimes I need a form to add many records.
When many of the fields have the same value, I want the previous value
to appear on the next view of the form to tab over without retyping it.
If I design those fields as Unbound (Control Source is blank), then I
cannot request those to be filled in the Form_Activate sub, and have to
retype everything. But if I say the Control Source is the name of the
field, then whenever I start a new session of data entry, the first
record in the table is overwritten. (Within one session, only that one
record is overwritten.) So far I’ve not been able to put anything else
in the Control Source that the compiler will accept.
What I’m doing that causes the record to be overwritten is:
Private Sub Form_Activate()
ORDER.Value = strOrder
End Sub
Private Sub cmdOK_Click()
strOrder = ME!ORDER
DoCmd.Close

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblFamilies")
With rst
.AddNew
!ORDER = strOrder
.Update
.Bookmark = .LastModified
End With
 
Janet,

There's any easy way to do this--I recently built a program that does just
this. I'm assuming your form is set as a 'continuos' form. On each dropdown,
textbox, etc. you use the OnGotFocus event--it looks like this:

If Not Me![FieldNameHere] Then 'If the field is not empty (a
user enters a value) then don't default
Exit Sub
Else 'Get the previous record's value

Dim rs As Object
Set rs = Me.Recordset.Clone

If rs.EOF Or Not Me.NewRecord Then
' don't do anything if there's no records or it is not a new
record
Else
With rs

.MoveLast

Me![FieldNameHere] = .Fields("FieldNameHere")

End With

End If

End If

Best regards,

Todd

Another problem, please: Sometimes I need a form to add many records.
When many of the fields have the same value, I want the previous value
to appear on the next view of the form to tab over without retyping it.
If I design those fields as Unbound (Control Source is blank), then I
cannot request those to be filled in the Form_Activate sub, and have to
retype everything. But if I say the Control Source is the name of the
field, then whenever I start a new session of data entry, the first
record in the table is overwritten. (Within one session, only that one
record is overwritten.) So far I’ve not been able to put anything else
in the Control Source that the compiler will accept.
What I’m doing that causes the record to be overwritten is:
Private Sub Form_Activate()
ORDER.Value = strOrder
End Sub
Private Sub cmdOK_Click()
strOrder = ME!ORDER
DoCmd.Close

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblFamilies")
With rst
.AddNew
!ORDER = strOrder
.Update
.Bookmark = .LastModified
End With
 
Hi Janet,

The following is copied from www.mvps.ort/access There is a bunch of Access
information there.

(Q) How do I carry forward the current value of a control so that it's
automatically entered for all new records?

(A) To use the curent control value for new records, you need to assign
it to the defaultvalue of the control. For example something like

'******** Code Start **********
const cQuote="""" 'Thats two quotes
me!Control.DefaultValue = cQuote & me!Control.Value & cQuote
'******** Code End **********

would carry the current value of the control forward if you place this
code behind a command button to conditionally run it.

HTH
 
Back
Top