Adding Overwrites Record

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

Janet Ciegler

Thought I had posted this yesterday but don't see it, so here it is
again. If you've already read it, please accept my apologies.

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,

If your fields are bound and...your form is a 'continuous' form, then you
can use the following excerpt of code for each field's OnGotFocus event
(edit YourFieldName in code). I have a tabular style form in a few
applications where I have implemented this code; it gives the user the
ability to simply tab through a new record set without having to type
anything, and only changing the necessary fields.

If Not Me![YourFieldName] Then
Exit Sub
Else

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![YourFieldName] = .Fields("YourFieldName")

End With

End If

End If

Best regards,

Todd Shillam
 
Hello, Todd,

Thanks for your fast response! I'm not sure if your response pertains
to my situation. I've never used (= don't know how to use) continuous
forms, bound fields, or tabular style forms. Would your command
".MoveLast" help me if I just added that in?

Jan

Todd said:
Janet,

If your fields are bound and...your form is a 'continuous' form, then you
can use the following excerpt of code for each field's OnGotFocus event
(edit YourFieldName in code). I have a tabular style form in a few
applications where I have implemented this code; it gives the user the
ability to simply tab through a new record set without having to type
anything, and only changing the necessary fields.

If Not Me![YourFieldName] Then
Exit Sub
Else

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![YourFieldName] = .Fields("YourFieldName")

End With

End If

End If

Best regards,

Todd Shillam

Janet said:
Thought I had posted this yesterday but don't see it, so here it is
again. If you've already read it, please accept my apologies.

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
Click to expand...
Click to expand...
 
Janet,

No--it would not help you if your form controls are not bound. The code I
provided only works if your fields are bound and the form is continuous.

Best regards,

Todd

Janet said:
Hello, Todd,

Thanks for your fast response! I'm not sure if your response pertains
to my situation. I've never used (= don't know how to use) continuous
forms, bound fields, or tabular style forms. Would your command
".MoveLast" help me if I just added that in?

Jan

Todd said:
Janet,

If your fields are bound and...your form is a 'continuous' form,
then you can use the following excerpt of code for each field's
OnGotFocus event (edit YourFieldName in code). I have a tabular
style form in a few applications where I have implemented this code;
it gives the user the ability to simply tab through a new record set
without having to type anything, and only changing the necessary
fields. If Not Me![YourFieldName] Then
Exit Sub
Else

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![YourFieldName] = .Fields("YourFieldName")

End With

End If

End If

Best regards,

Todd Shillam

Janet said:
Thought I had posted this yesterday but don't see it, so here it is
again. If you've already read it, please accept my apologies.

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
Click to expand...
Click to expand...
Click to expand...
 
Thought I had posted this yesterday but don't see it, so here it is
again. If you've already read it, please accept my apologies.

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.
Click to expand...

The simplest way to accomplish this is to set each such control's
DefaultValue property to its current value in the control's
AfterUpdate event. That way, each control will default to the most
recently entered value; if you have several records with the same
value, you can just tab through or skip that control altogether.

John W. Vinson[MVP]
 
And how you do what John suggested is shown on www.mvps.ort/access - lots of
Access lore there.

=========================================================
Forms: Carry current value of a control to new records
Author(s)
Dev Ashish


(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.

================================================================

You could put that into the AfterUpdate event of each control you want to
copy or put the lot of them into the form's AfterUpdate event

HTH
 
Thank you, Todd, John, and Larry, for all your help. The form now seems
not to be overwriting old records, which is most important!

As a minor enhancement, is there any way (easily) to get the cursor back
on the first field after each update?

Jan
 
Back
Top