Copy the information that is in the previous record into a new record 1

[meek]

I would like the user to click a command button and copy the information in the previous record(text boxes and combo boxes are used) into a new record. This is so they do not have to re-type all the same info again into the new record if it is already in the previous record. thank u

 

[SBendBuckeye]

Here is something to get you started. Create a command button (I called it cmdCopyValues) to copy the values. Put the other set of code in your form after update event processing. Good Luck!

Private Sub cmdCopyValues_Click()
Dim ctl As Control

For Each ctl In Me.Form
'Skip things like labels, etc.
On Error Resume Next
ctl.Value = ctl.DefaultValue
Next

'Reset the error processing
On Error GoTo 0

End Sub

Private Sub Form_AfterUpdate()
Dim ctl As Control

For Each ctl In Me.Form
'Skip things like labels, etc.
On Error Resume Next
' Debug.Print vbTab; ctl.ControlName; vbTab; _
' ctl.Visible; _
' vbTab; ctl.ControlSource; vbTab; _
' ctl.OldValue; _
' vbTab; ctl.DefaultValue
' Set default value to current record value
ctl.DefaultValue = ctl.Value
Next

'Reset error handling
On Error GoTo 0

End Sub

 

[meek]

thanks Buck, but I am not clear on what this does. The code is in, but when the command button is clicked, it just wipes out all the values in the current record. Sorry, I am not a programmer. In the For Loop, do i enter the names of all the text boxes and combo boxes like this:

Private Sub cmdCopyValues_Click()
Dim ctl As Control

For Each ctl In Me.Form
'Skip things like labels, etc.
*Do i enter the names of the controls in here?
*me.txtJobCode.value??
On Error Resume Next
ctl.Value = ctl.DefaultValue
Next

'Reset the error processing
On Error GoTo 0

End Sub

Also, would i do the same thing in the form load event?

 

[SBendBuckeye]

Sorry, I assumed that you would be sitting on a new record when you clicked the button. Try adding the new record check below into your code.

Here is what the code is doing. Everything on a form is considered to be a control so it is just spinning through all of the controls on the form.

The Form_AfterUpdate code sets each control'a default value to the current value. The on error logic takes care of controls that don't have such a property (eg labels as an example).

The cmdCopyValues_Click just sets all of the form values to the default values setup by the Form_AfterUpdate code. The new test I just added checks to make sure you are on a New Record before it does its work. This should prevent you from wiping out currently existing data.

Private Sub cmdCopyValues_Click()
Dim ctl As Control

If Me.NewRecord Then '<== Check for New Record
For Each ctl In Me.Form
'Skip things like labels, etc.
On Error Resume Next
ctl.Value = ctl.DefaultValue
Next
End If

'Reset the error processing
On Error GoTo 0

End Sub

Good Luck! If it still gives you trouble, my home email address is landgwiney@characterlink.net.

 

[cfcanter]

Sub CarryOver(frm as form)
' this is written for MS Access
'
' This was written by Allen Browne, abrowne@odyssey.apana.org.au
'

http://odyssey.apana.org.au/~abrowne/ser-24.htn

'
'notes: this example limited to text boxes and combo boxes.
'
Dim rst as recordset
Dim ctl as control
Dim i as integer

Set rst = frm.RecordsetClone
if rst.recordcount> 0 then
rst.Movelast
for i = 0 to frm.count - 1
set ctl = frm(i)
if typeof ctl is textbox then
if not isnull(rst(ctl.name)) then
ctl = rst(ctl.name)
end if
else typeof ctl is combobox then
if not isnull(rst(ctl.name)) then
ctl = rst(ctl.name)
end if
next
end if
exit_carryover:
Craig Canter
Cost Accountant
Sipex
Milpitas, CA

 

[meek]

Ok this is a bit wierd..

When info is entered into the current record, the user will click the add new record button. Now, when this button is clicked, the data that was in the previous record that i entered went into the new record. That is ok, but shouldn't that happen when the copy previous command button is clicked? Also, one of the text boxes had an error of &quot;Name?&quot;, and when i click the copy previous command button, that text box gets the value that was in the previous record. That is ok too, but shouldn't it work a bit easier?
The user should click on the add new record, and if he wants the previous info into that new record, he clicks on the copy previous, and so on
thank you for your help, it is much appreciated

 

[meek]

also, the data is still getting wiped out when i click on the copy previous command button

 

[SBendBuckeye]

My apologies, I tried to do an example quickly from work off the top of my head. When you set the default property that will automatically be displayed as the value for the new record. To do what you originally requested, use the code below. The tag property is just a catch all property Access supplies for programmers to use as they see fit.

What you are doing is saving the current values of the form controls in the various tag properties whenever a record is updated. Then, when you are on the new record, the command button causes the most recent set of values to be copied into the new record.

The reason I check the visible property is because I have a primary key defined as an autonumber and it is invisible. I don't want to do anything to my primary key. You could also check for specific controls as below:

If ctl.Name = YourFormControlName Then
'Do Nothing
End If

Private Sub Form_AfterUpdate()
Dim ctl As Control

For Each ctl In Me.Form
'Skip things like labels, etc.
On Error Resume Next
' Save the current value in the tag
ctl.Tag = ctl.Value
Debug.Print ctl.Name
Next

'Reset error handling
On Error GoTo 0

End Sub

Private Sub cmdCopyValues_Click()
Dim ctl As Control

If Me.NewRecord Then
For Each ctl In Me.Form
If ctl.Visible Then
'Skip things like labels, etc.
On Error Resume Next
ctl.Value = ctl.Tag
End If
Next
End If

'Reset the error processing
On Error GoTo 0

End Sub

Sorry for being in too much of a hurry before. Let me know how this works for you.

 

[meek]

Thanks for your effort Buck, but now there is a invalid outside event procedure with the expression On Click
Is it an error in ctl.tag? Maybe Access isn't happy with that.. I am using Access 2002. The error message pops up when i click the add new record command button and the copy previous command button. It is probally something very easy that we are missing. Thanks buck, take another look please, I would appreciate it.

 

[SBendBuckeye]

If you can, zip a small portion of what you have and send it to me at gwiney@partnersindiana.com or landgwiney@characterlink.net and I'll see what I can do.

Sorry, it worked for me before I posted it.

Good Luck!

 

[JesseL]

I think this is the problem I am having:

If ctl.Name = YourFormControlName Then
'Do Nothing
End If

What event procedure does this code apply to?

 

[meek]

Yes ksharp.. I was not clear on that either

 

[meek]

Thank you buck! I manipulated the first bit of code you gave me. It works now, without any errors!

 

[SBendBuckeye]

If you have a primary key defined or an autonumber key defined you would want to handle that yourself. I always define an autonumber primary key and then hide it on the form since the system will take care of it. YourControlName refers to what you have named the textbox or whatever on the form itself, NOT the database field behind it.

Since I hide it, I skip any control which has the visible property set to false. If your primary key was contained in a form control named PrimaryKey or txtPrimaryKey, etc. you would skip it in the above code with the following:

'Example 1
If ctl.name = &quot;txtPrimaryKey&quot; Then
skip this code or do something special or whatever
End If

'Example 2
If ctl.name = &quot;PrimaryKey&quot; Then
skip this code or do something special or whatever
End If

Hope this helps!