Duplicate Record Button not working

[Larry]

I wanted to duplicate a record on my form, so I thought I'd use the
Duplicate Record button that the wizard builds for you on the form. I
thought this would be simple, but something seems to be wrong. The
copied record is missing a couple of fields.

I simply put the button on my form and chose the Duplicate Record
macro. It copies almost all the data, but there are two fields (that
I've noticed so far) that are not copied.

Anyone know any reasons this might be happening?

I'm using Access 2003.

Thanks,
Larry

 

[Nick Coe \(UK\)]

What indexes do you have on the table (or tables under a
query) underlying the form in question?

It is likely that the record cannot be duplicated because
one or more of the fields appears in a unique index.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.pjandcoe.co.uk/ Online Store
http://www.mrcomputersltd.com/ Repairs Upgrades

In
Larry typed:

 

[Larry]

The only index is the PK which is an autonumber. The fields that are
not being copied do not have any idexes on them.

 

[Nick Coe \(UK\)]

Then I'm puzzled <g>. IIRC the wizard creates a bit of vba
using DoCmd to use the Edit menu commands Select Record,
Copy and Paste Append to copy over to a new record.

What happens if you do it manually from the Edit menu? Get
any error messages?

Is it possible the record is just too big for the clipboard
(clutching at straws here)?

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades

In
Larry typed:

 

[Larry]

Well, I tried it manually, using the menu commands, and get the same
results.

But, if I go into the underlying table, select the row and copy, then
move to the new record row and paste it, it works just fine.

One thing I noticed is that these two fields, on the form, are by
default disabled (only becoming enabled if certain fields are selected
in dropdown boxes). Now in the case I am testing, I am trying to copy a
record where the fields are enabled and have values, to a new record.

I assumed the copy record is working on the table, and ignoring the
enable property on the form, but not I am wondering if it could be
having an effect?

 

[Larry]

Well, I guess if I would have been smart, I would have done this test
THEN posted a msg, but no, I gotta do it the wrong way round!

Anyway, I just tested my theory, and sure enough, it's not pasting
those two fields into the new record, because the fields on the form
are disabled at the time!

So Duplicate Record is not working at the table level, it's working at
the form level and abiding by the forms properties. Now I have to
figure a way around this problem. Grrrrr

 

[tina]

if you're only talking about a few fields, it should be easy enough. in the
VBA procedure that duplicates the record, precede the first action with code
to set the Enabled property of the fields in question to True. then, after
the last copying action, add code to set those fields' Enabled property back
to False.

hth

 

[Larry]

Yeah, I tried that right after my last post, but it didn't work.

I enabled the combobox and the frame before the three lines of code to
copy the record, then after the three lines, I call the subroutine that
automatically sets the fields enabled or not.

The field that should have had the data copied, stays enabled, but
there is no data in it in the record. It's very strange.

 

[tina]

hmm, care to post your button's code so we can see it?

 

[Larry]

Private Sub cmdCopy_Click()
On Error GoTo Err_cmdCopy_Click

mblnDuplicate = True

Me.CustomerTypeID.Enabled = True
Me.fraFCPA.Enabled = True

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

' Set Customer Type/FCPA to enabled or not depending on data on
current record
Call CheckCustomerType

Me.txtProfileName.SetFocus
Me.txtProfileName.SelStart = 0
Me.txtProfileName.SelLength = Len(Me.txtProfileName)
Me.cboLocateProfile.Requery

Exit_cmdCopy_Click:
Exit Sub

Err_cmdCopy_Click:
MsgBox Err.Description
Resume Exit_cmdCopy_Click

End Sub

 

[tina]

well, nothing in the code jumped out at me, as a cause of the problem. i'm
afraid i haven't any further suggestions to offer; sorry i wasn't more help,
Larry. this thread is several days old now; if nobody else posts with a
suggestion for you today, suggest you abandon the thread and start a new
one. just explain how far you had gotten (and had not got) in this thread,
so you won't waste time re-hoeing old ground. good luck!

hth

 

[Larry]

Well, I figured out a work around.

One of the fields that's causing a problem uses a combobox on the form,
the other field is attached to a frame. So, even though I have other
comboboxes that work on this form, I thought maybe that was the issue
for some reason.

So I put 2 textboxes on the form, linking them to each of the fields
that are causing the problem.

Now, when I do the copy, it works! So I tried to make those two
textboxes invisible, to hide them from the users and it stopped
working! So THEN I put the two textboxes behind another big control, to
hide them that way, and it's working.

I don't understand it, but I figured a way around it.

Thanks for trying to help Tina.

 

[tina]

well, you're welcome. as you said, at least you figured out a way to
accomplish your goal - sometimes that's the bottom line, regardless of the
"why"s.