Duplicate portions of a record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to click a button to copy a record with the exception of the
primary key.
I use the following code that includes an input box to enter the new primary
key value. The button works fine until I call some other function in the
forms "OnCurrent Event" If I use this event to act when I move from record to
record the button creates a new record but does not carry any of the data
except the primary key with it.

Does anyone have any suggestions? What other events trigger when you move
from record to record in a form other than the "Current"?
Private Sub btnDuplicateRecord_Click()
On Error GoTo Err_btnDuplicateRecord_Click

NewRoomNo = InputBox("Room Number Input", "Input Room Number", "XXX", 1000,
1000)

RunCommand acCmdSelectRecord
RunCommand acCmdCopy
RunCommand acCmdPasteAppend

txtRoomNo = NewRoomNo
Exit_btnDuplicateRecord_Click:
Exit Sub

Err_btnDuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_btnDuplicateRecord_Click

End Sub
 
This code looks like it should work as long as it's tied to a command button
on the form AND you click it when you are displaying the record you want to
duplicate.

I don't understand what you're saying about the OnCurrent Event. The
OnCurrent event happens when you land on a different/new record. At that
point, the data in the previous record is no longer available. If you're
saying that you're trying to put this code in a the OnCurrent event, then it
won't work because you're no longer on the record you want to duplicate.

Can you explain in more detail what the problem is and how the OnCurrent
event impacts it?
 
I have a Private Sub that enables or disables some combo boxes in a form
based on the value of a check box. I trigger the Sub with the afterupdate
event of the check box and the OnCurrent Event of the Form. I am having
trouble getting the combo boxes to reappear on the next record with out using
the OnCurrent Event. When I use the OnCurrent event and press my duplicate
button the new record is created but the contents of the previous record are
not carried over.
 
Bill,

There are probably two different things happening here to cause you
problems.

1. I assume that you have some sort of IF statement in the OnCurrent Event
that checks the status of the checkbox. Something along the lines of:

if me.checkbox then
me.combox1.visible = true
me.combox2.visible = false
etc
etc
endif

This will not work in the OnCurrent event when you're copying and pasting a
record using the code you posted previously. The reason is that the
OnCurrent event happens as the new record is created, but before it is
populated with any data. As a result, the IF statement sees me.checkbox as
being NULL. Since that does not evaluate to either true or false, the if
statement fails with an error and nothing gets loaded into the record.

2. When you use the copy/paste functionality that you posted, it only
copies the data that is actually displayed on the screen (that is, it takes
the data from the screen and not from the underlying record). The data in
comboboxes that are not visible at the time simply does not get copied.

There are a couple of ways around the first problem. The easiest is to use
the form's newrecord property. When you process the keypress of the command
button to copy the record, store the status of the checkbox in a module
level variable. Then use the following type of logic in your OnCurrent
event:

if me.newrecord then
if module_level_ckb_status then
me.combo1.visible = <status if checked>
me.combo2.visible = <status if checked>
etc.
else
me.combo1.visible = <status if not checked>
me.combo2.visible = <status if not checked>
etc.
end if
else
if me.checkbox then
me.combo1.visible = <status if checked>
me.combo2.visible = <status if checked>
else
me.combo1.visible = <status if not checked>
me.combo2.visible = <status if not checked>
etc.
end if

With this code:

On a new record, the module-level checkbox status determines which combo
boxes are visible and which aren't, thus avoiding the null problem
On an existing record, the status of the checkbox on the form (which is
presumably bound to field in your record) is checked.
You can, of course, move all the me.combox.visible stuff into subroutines so
you don't have to type it out twice.

As for the second problem, that's a problem with this approach (using cut
and paste from the form). Perhaps the better approach would be to actually
copy the record using SQL or using a recordset. Either way, once you copy
the record, you can find out the primary key of the record added and then
move to that record. Since you would then be copying the data in the
underlying record, it would not matter which combo boxes are visible and
which aren't.
 
Back
Top