Duplicating Records - Issues with pasting into unbound combo box

[dhmlofi]

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.

Much appreciated for your input Micron. I too have many tasks in between getting to work on this, so am only just getting back to it. Your explanation of the active control thing makes sense this time around, and am sure will help me down the road too. My client has changed the way he wants the search function to work in the header, so I think I'm going to be able to have a combo as before, but without the macro that was causing the problem. Haven't tried it yet, but if you're suggesting that removing the macro made the problem go away then fingers crossed all will be good. Failing that I'll try shifting the focus as you suggest. Before posting on the forum I had tried the GoToControl without any luck, but didn't think of SetFocus. Thanks!

[dhmlofi]

Aarrrggghhh! My issue with the random characters continues.

I have however decided to ditch the default duplicate record command and use Allen Browne's code for duplicating form and subform in the hope this will help. There is something wrong with my code though. I get a type mismatch error, code 3464. The debugger identifies the append query. The code is below. The parent/child fields between the form/subform are QuoteIntID and QuoteIntIDNo. The bit of code I'm confused by is his "SELECT " & lngID & " As QuoteDetailID (or as he has it "SELECT " & lngID & " As NewID). What of As NewID is code, and what is the field name? Have I got it right with mine? Or is it something entirely different that's giving me the error? Given that I'm amending from/to the same table I can't see how it could be a mismatch of field types. All number fields are long integer. The rest are either text, currency or the primary key autonumber.

'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew

!JobName = Me.JobName
!QuoteDate = Date
!Customer = Me.CustomerID
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !QuoteIntID

'Duplicate the related records: append query.
If Me.[QuoteDetailCustomer].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [QuoteDetail] ( Job, DoorLocation, Quantity, DoorSizeWidth, DoorSizeHeight, Thickness, HCSC, Swing, Jamb, Comments, UnitPrice, UnitPriceTotal, SortOrder, QuoteIntIDNo ) " & _
"SELECT " & lngID & " As QuoteDetailID, Job, DoorLocation, Quantity, DoorSizeWidth, DoorSizeHeight, Thickness, HCSC, Swing, Jamb, Comments, UnitPrice, UnitPriceTotal, SortOrder " & _
"FROM [QuoteDetail] WHERE QuoteIntIDNo = " & Me.QuoteIntID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If


Exit_Handler:
Exit Sub


Thanks in advance!
Resume Exit_Handler

[Micron]

What of As NewID is code

As "Anything" is used to give (usually) a field name or a table an alias. Sometimes this is necessary to avoid ambiguity, such as in a subquery, to avoid throwing an error due to having an ambiguous reference; i.e. to use the same name more than once would confuse Access because it would not know which instance of it you are referring to.

I'm confused by is his "SELECT " & lngID & " As QuoteDetailID (or as he has it "SELECT " & lngID & " As NewID)

In his case, it is for the reason I explained above. In yours, I cannot tell if QuoteDetailID is a field name or not (you are also using QuoteIntID). If you are sure the data types going into their respective fields are all correctly matched, then perhaps you are using two fields instead of using one and giving it an alias.

I'll need more time to peruse your code. In the meantime, perhaps someone else will jump in after seeing something obvious. You could also do us a favour: if you cannot go back to your last post and wrap the code in code tags, please use them in the future for anything more that a few lines because it makes it hard to read. I find that doing this and adding one space for an indented line (and one more for each subsequent indent) seems to lay it out well Yours is hard to read. Also, I have a pet peeve with line continuation characters, but it is mine to suffer with. If you really want to use them, it's your right, but I never do. One misplaced space and you have problems. I have even copied code right from MS web pages and dumped it in - only to have the section with these characters fail. What I do is this:
strSql = "INSERT INTO [QuoteDetail] (Job, DoorLocation, Quantity, DoorSizeWidth, DoorSizeHeight, Thickness, HCSC, Swing, "
strSql = strSql & "Jamb, Comments, UnitPrice, UnitPriceTotal, SortOrder, QuoteIntIDNo) "
I ALWAYS place the space at the end so as to be consistent in habit. Just wanted to show you another way - perhaps you prefer yours.

[CJ_London]

you can use

"SELECT " & lngID & " As thegoodthebadandtheugly

if you wanted, it is just an alias

However the problem is you are saying

"INSERT INTO [QuoteDetail] ( Job, DoorLocation...

and trying to insert

"SELECT " & lngID & " As QuoteDetailID, Job, DoorLocation,....

which as you can see, there in no lngID so you are trying to insert 3 fields where there are only two
and since you are saying

WHERE QuoteIntIDNo = " & Me.QuoteIntID

it like it is already that value

so try

"SELECT Job, DoorLocation,....

Alternatively if you are trying to change that field change the insert part to

"INSERT INTO [QuoteDetail] ( QuoteIntID, Job, DoorLocation...

[dhmlofi]

(Job, DoorLocation, Quantity, DoorSizeWidth, DoorSizeHeight, Thickness, HCSC, Swing, "
strSql = strSql & "Jamb, Comments, UnitPrice, UnitPriceTotal, SortOrder, QuoteIntIDNo) "
I ALWAYS place the space at the end so as to be consistent in habit. Just wanted to show you another way - perhaps you prefer yours.

Thanks again Micron. Computer issues and a super busy December took me away from this completely, but I've now had time to look at it again. Between your reply and Ajax's I seem to have resolved the issue. Will post my fixed code (for the benefit of anyone else looking) in a separate post - and will wrap them in code tags, hopefully correctly. Thanks for the note about line continuation characters. That's good advice and one I'll bear in mind in future when I have long pieces of code.

[dhmlofi]

you can use

"SELECT " & lngID & " As thegoodthebadandtheugly

if you wanted, it is just an alias

However the problem is you are saying

"INSERT INTO [QuoteDetail] ( Job, DoorLocation...

and trying to insert

"SELECT " & lngID & " As QuoteDetailID, Job, DoorLocation,....

which as you can see, there in no lngID so you are trying to insert 3 fields where there are only two
and since you are saying

WHERE QuoteIntIDNo = " & Me.QuoteIntID

it like it is already that value

so try

"SELECT Job, DoorLocation,....

Alternatively if you are trying to change that field change the insert part to

"INSERT INTO [QuoteDetail] ( QuoteIntID, Job, DoorLocation...

Thanks Ajax, that's been a big help. Got taken away from this for a few weeks, but now I've had the chance to look at it again, I had it fixed in a few minutes using your advice and Micron's advice from above! By the way, I like your example for the alias....that made it clear.

For the record, in case it benefits anyone else reading this in future, this is my code that seems to work:

'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
   
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
   
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
               
                !JobName = Me.JobName
                !QuoteDate = Date
                !Customer = Me.CustomerID
               
                'etc for other fields.
            .Update
           
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !QuoteIntID
           
            'Duplicate the related records: append query.
            If Me.[QuoteDetailCustomer].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [QuoteDetail] ( QuoteIntIDNo, Job, DoorLocation, Quantity, DoorSizeWidth, DoorSizeHeight, Thickness, HCSC, Swing, Jamb, Comments, UnitPrice, UnitPriceTotal, SortOrder) " & _
                    "SELECT " & lngID & " As QuoteDetailID, Job, DoorLocation, Quantity, DoorSizeWidth, DoorSizeHeight, Thickness, HCSC, Swing, Jamb, Comments, UnitPrice, UnitPriceTotal, SortOrder " & _
                    "FROM [QuoteDetail] WHERE QuoteIntIDNo = " & Me.QuoteIntID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
           
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If
 
Exit_Handler:
    Exit Sub

All I did was move the ID field for my sub-form (QuoteIndIDNo) from the end of the INSERT INTO statement, to the beginning of the field list. Thanks again!