copy previous record

[J.J.]

I have a form which has a subform in which a user enters a lot of data.
Occasioanlly the same event occurs so I would like to create a button either
on the form or subform that will copy over the data from a previous record
where the current customerID matches the previous entry. I found what I
thought was an elegant piece of code but I can't get it to work. It also
needs to reference the customer ID. An error message reports that there is
no current record even though the customerID displays in both the form and
subform.
With Me.RecordsetClone
.Bookmark = Me.Bookmark
.MovePrevious
Debug.Print IntakeDataID
For Each ctl In Me.Controls
If ctl.Tag = "RepeatPrevious" Then
ctl.Value = .Fields(ctl.ControlSource)
End If
Next
End With
One further question. By tagging the subform will all controls be copied or
does each one have to be tagged seperately?

 

[J.J.]

I got a little further with the code. Here it is so far but now I am stuck.

Private Sub Dupli_Click()
On Error GoTo Err_dupli_Click

Dim rst As Recordset
Dim strSearch As String
Dim ctl As Control

Set rst = Me.RecordsetClone
strSearch = str(Me!AutoID)
rst.FindFirst "AutoID= " & strSearch

If rst.NoMatch Then
MsgBox "No Previous Record Exists. Unable to Duplicate."
Else
Me.Bookmark = rst.Bookmark
End If

For Each ctl In Me.Controls
If ctl.Tag = "RepeatPrevious" Then
ctl.Value = .Fields(ctl.ControlSource) ' ****
End If
Next
End With

Exit_Dupli_Click:
etc
I ain't no programmer! I have a problem with "Fields" at the line marked
with asterisks. I have no idea how "Fields" works as I do not understand the
line's syntax other than values are copied from ControlSource. I assume
Fields has to be declared as some kind of variable. Can anybody have a look
at what I am trying to do and help me out?

Thank you so much!

 

[dymondjack]

End With

What does this go to? I don't see any corresponding With statement.

ctl.Value = .Fields(ctl.ControlSource) ' ****

rst.Fields("NameOfTableOrQueryField")

the recordset.Fields gets the value from the current record of the Fieldname
supplied. For example, if the underlying table of the recordset has fields
named ID, CompanyName, and DateCreated, you would use something like this:

= rst.Fields("CompanyName")

to get the value of the current record's CompanyName value.

If rst.NoMatch Then
MsgBox "No Previous Record Exists. Unable to Duplicate."
Else
Me.Bookmark = rst.Bookmark
End If

This is going to change the current record before you start to copy the
fields, which I don't think is what you're aiming at.

If you have no child records to copy along with this, the easiest way that I
have found is by using a private variable for each field that is declared at
the module level. Then, before changin records, copy the data into those
variables. Go to a new record, and copy from the variables into the controls
on the form.

If you have related child records to copy as well, this doesn't really work
out all that well. At this point you may want to think about using two temp
tables with the same structure as your main tables. Then you can copy the
record and its children to the temp table, and copy from the temp table back
into the main tables. This would generally be done via recordset or sql, not
through form/controls.



You may find this link to be useful:

http://allenbrowne.com/ser-57.html

hth

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[J.J.]

Thank you for all the help and taking the time to explain. It is very much
appreciated.

As for Allen's code it is way to complicated for what I am trying to do. I
don't even know where to try and edit it. I trying a very simple copy of an
old record but filtered on AutoID to display in the user form. The user could
then add or edit and then paste it as a new record.

There are no child records. Because of the large number of fields, I
thought the automatic copy using rst.Fields would be faster and more elegant.
If you or anyone has any further ideas on how to put this code piece to
life, please let do me know.

thank you

 

[dymondjack]

Personally, I think you're going about this the wrong way. Look up some
basic info on using an Append query, it should be all you need. Very simple
to use. What you are trying to do is like curing dandruff by cutting off
your head. Overkill.

An append query is definately the way to go here.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[J.J.]

Hi Jack,

I already am using an Append query and works great. It's not as slick as VB
and I liked the elegance of the code snippet. The other problem is that
Access 2007 does not support "SetWarnings" (to No). The dialoque box would
scare the bejeebers out of many a timid end user.

From what I gather from you is that:
For Each ctl In Me.Controls
If ctl.Tag = "RepeatPrevious" Then
ctl.Value = .Fields(ctl.ControlSource) ' ****
End If
Next
Should work but I need to rework how link to the previous record where ID's
match.
No, I am not copying child records. The button is simply on the main form.
Question: By tagging the subform with "RepeatPrevious" will all controls be
copied or does each control have to be tagged seperately?
Thank you for the help.