Duplicating Records - Issues with pasting into unbound combo box

[dhmlofi]

I'm working with a form right now (Access 2010) where I have created a button to duplicate a record. I did this using the button wizard. There is a subform in the form.

The record seems to duplicate ok, but I have two unbound combo boxes in the form header which are used to search for/go to records. What seems to happen after the Paste record function, is that the unbound combos are causing issues. Their GotFocus functions are called, even though there is nothing in the macro to give them the focus.

I get random characters pasted into either one of these unbound fields. They look like Japanese or some other script. I've never seen an issue like this.

If I end the Debug to bypass the issue, usually having to delete the characters in the unbound field, the paste record command completes ok. But clearly something is wrong and I have no idea how to resolve it. I'd appreciate some advice please. Thanks in advance.

I tried converting the macro to VB but still the same issue. The code it converted it to is below.
Private Sub cmdDupRec_Click()On Error GoTo cmdDupRec_Click_Err


On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
If (MacroError = 0) Then
DoCmd.RunCommand acCmdCopy
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdSelectRecord
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdPaste
End If
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If




cmdDupRec_Click_Exit:
Exit Sub


cmdDupRec_Click_Err:
MsgBox Error$
Resume cmdDupRec_Click_Exit


End Sub

[Gina Maylone]

Good morning!

I cannot duplicate your problem. Please post your db so I can have a closer look. Thank you!

[CJ_London]

would these fields returned byt he combo rowsouce be memo fields by any chance? If so, try changing them to text fields

[dhmlofi]

Good morning!

I cannot duplicate your problem. Please post your db so I can have a closer look. Thank you!

Thanks. I've stripped out all unnecessary contents but cannot get the front and back ends below the upload limit of 500kb (I'm between 500 and 800 each). Is there another way to post the files that I'm missing? Thanks

[dhmlofi]

would these fields returned byt he combo rowsouce be memo fields by any chance? If so, try changing them to text fields

No, they're autonumber and text fields.

[CJ_London]

Is there another way to post the files that I'm missing? Thanks

try zipping the file

[dhmlofi]

Now why didn't I think of zippingQUOTES_be - test.zip them!! Boy did those compress well. Thanks

Front and back end files are attached. Hope this helps get to the bottom of the problem.

[CJ_London]

I've had a quick look, but it is all macros which I don't use so don't think I can help. However I suspect it is something to do with you refreshing and referencing screen.activecontrol (which after clicking the duplicate button, is that button, and not perhaps what you think it is).

[dhmlofi]

I've had a quick look, but it is all macros which I don't use so don't think I can help. However I suspect it is something to do with you refreshing and referencing screen.activecontrol (which after clicking the duplicate button, is that button, and not perhaps what you think it is).

Thanks. I removed the Refresh command but am still having these random alternative characters pasted into one of the search combos. (It's as if it's trying to paste the record there?). If you're meaning the screen.activecontrol on these search combos on the form header, that's the default macro for searching a record based on the combo box contents and so I wouldn't know what to put as an alternative. If I converted it to VB, would that help do you think?

Incidentally, I get the same issue if I manually copy and paste a record into a new record, i.e. without a macro.

[Gina Maylone]

Good morning!

Re-do your comboboxes. Delete the old ones and create new ones. when I did that, I didn't get the Chinese characters in the combo boxes.

HTH.
Gina

[dhmlofi]

Good morning!

Re-do your comboboxes. Delete the old ones and create new ones. when I did that, I didn't get the Chinese characters in the combo boxes.

HTH.
Gina

Hmmmm, interesting. The combo boxes in the file I uploaded were ones I had recreated as I thought to try that....but still had the same problem. Do yours have the same macro or VB to search for the record? Thanks

[Gina Maylone]

Yes, the lookup function is there.

[Micron]

I converted your embedded macro to code and set the event to the new function and stepped through it. At one point, I pasted the record into notepad then into Excel but everything seemed to line up and looked reasonable. I noticed you have an embedded macro on the lower of the two top combos that runs an after update event. This won't fire if the control is modified in code, but it will if the form is saved. So I removed the embedded macro and it seems to work, but I suppose you need that event to affect the subform. What looks odd about the embedded macro is that you're converting the value of the active control to a string, but the active control is the command button. I was wondering if that's the reason for the odd text.

[dhmlofi]

Thanks for your efforts Gina and Micron but I'm still getting nowhere I'm afraid. I've tried replacing the combos with new ones as you suggested Gina but still got the same result.

I have also tried doing a conversion of the macros to code, using the default 'convert to code' function. That gives me the code...

DoCmd.SearchForRecord , "", acFirst, "[QuoteIntID] = " & Str(Nz(Screen.ActiveControl, 0))

Is this the code you refer to Micron? I sort of understand what you're meaning, but not entirely. Not enough to know what to do next though.

Essentially, I want the combos in the form header to find a record. I've used combos to do this on a few forms in more than one database in the past and it's never given me grief. I've never had to do a duplicate record function before though. I want to be able to duplicate a complete record, form and subform.

When I use the duplicate command (or a manual copy and paste record) the focus always seems to go to one of those combo boxes and pastes in these characters. This happens whether the combos and/or duplicate button is a macro or code.

Currently my duplicate code is:

On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
If (MacroError = 0) Then
DoCmd.RunCommand acCmdCopy
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdSelectRecord
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdPaste
End If
DoCmd.RunCommand acCmdSaveRecord
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

If alternative code would be better, I'm not sure what to try.

Thanks again

[Micron]

Is this the code you refer to Micron?

DoCmd.SearchForRecord , "", acFirst, "[QuoteIntID] = " & Str(Nz(Screen.ActiveControl, 0))

I believe it is, but I removed it, so to be absolutely sure, I'd have to reload your db because I didn't back up the form. To elaborate my point, I think this is what is happening:
When you click on the button to copy the record, that button is the active control. This part of the code DoCmd.RunCommand acCmdSaveRecord causes the form to be saved, which causes the controls to update, which causes your embedded macro to run. This macro is trying to set the combo box to whatever the button equals - because the button is what has the focus. I do wonder about this theory because I would expect an error, but perhaps there is a property or value that is the default and you are pulling it in without generating an error - thus the garbled text (which looks like part of the ASCII character set to me). As mentioned, I removed the embedded macro and the problem went away. Since the screen.activecontrol is a reference to the control that has the focus, try moving the focus to the control whose value you want to grab before you save the record.

DoCmd.RunCommand acCmdPaste
 End If
NameOfYourControl.SetFocus
DoCmd.RunCommand acCmdSaveRecord
If (MacroError <> 0) Then
   Beep

There is another method you can consider. When duplicating parts of a record, sometimes it's just easier to copy what you need to a temp table and change the form's recordsource to the temp table and work on it there. Then you copy the record to the main table(s) and flush the temp. If this is a split db where each user has their own copy, temp tables should be in the FE (front end) only and all should be OK. If it's a split db but a shared FE you will remove records that others are working on if you flush this table. The only way around that AFAIK is to tie the records to the user login id. If your db is not split, you should do this regardless if it's shared or not.

I apologize for not responding quickly, but I've got some woodworking projects that I have to move along, so I'm trying to restrict my forum time to the evenings for now. Hope something I said helps you solve the issue.