Duplicate output destination 'txt_FullName

[ddrew]

I have a piece of code to import a record into a Database, part of it is to update an existing table that populates a combobox. The problem I am having is that I get a message "Duplicate output destination 'txt_FullName'. If the item wasn't in the list I could understand it happening, but its not! I have posted my code below along with the query. Many thanks.

Private Sub btn_SaveImport_Click()
    On Error GoTo btn_SaveImport_Click_Err

    If IsNull(Me.DogID) Then
        MsgBox ("You must load the record and then press the the Import Button")
        Me.cbo_Import.SetFocus
        Me.cbo_Import.Dropdown
    Else


        DoCmd.SetWarnings False

        DoCmd.OpenQuery "qry_AppendToDetectionTraining", acViewNormal, acEdit
        DoCmd.OpenQuery "qry_AppendToTypeOfHideFromDetection", acViewNormal, acEdit
        DoCmd.OpenQuery "qry_AppendToVenueFromDetection", acViewNormal, acEdit
        DoCmd.OpenQuery "qry_AppendToWeatherFromDetection", acViewNormal, acEdit
        DoCmd.OpenQuery "qry_AppendToSpecialistEquipmentFromDetection", acViewNormal, acEdit
        DoCmd.OpenQuery "qry_AppendToOdourFromDetection", acViewNormal, acEdit
        DoCmd.OpenQuery "qry_DeleteDectionTrainingImports"

        'DoCmd.OpenQuery "Query1", acViewNormal, acEdit

        DoCmd.SetWarnings True

        MsgBox "Record has been imported"
        Me.Requery
        
    End If

btn_SaveImport_Click_Exit:
    Exit Sub

btn_SaveImport_Click_Err:
    MsgBox Error$
    Resume btn_SaveImport_Click_Exit

End Sub

And the query:

INSERT INTO tbl_Odours ( txt_FullName )
SELECT tbl_Odours.*, tbl_Odours.txt_FullName
FROM tbl_DetectionImports LEFT JOIN tbl_Odours ON tbl_DetectionImports.Odour = tbl_Odours.[txt_Odour]
WHERE (((tbl_Odours.txt_FullName) Is Null));

 

[GinaWhipp]

Well, txt_FullName is in the table and you have also included in the SELECT line...

SELECT tbl_Odours.*, tbl_Odours.txt_FullName

So, just remove it...

SELECT tbl_Odours.*

...and the error message should go away.

 

[ddrew]

Well, txt_FullName is in the table and you have also included in the SELECT line...

SELECT tbl_Odours.*, tbl_Odours.txt_FullName

So, just remove it...

SELECT tbl_Odours.*

...and the error message should go away.

Thanks, I just tried it but now when I try to save the query I'm getting a message "Number of query values and destination fields are not the same."

Just a thought could it be that this is a concatenated field?

 

[mboyde]

So the table that you are updating has the RowSource for the combobox, right?

 

[GinaWhipp]

My bad, did not read the whole line, here you go...

INSERT INTO tbl_Odours ( txt_FullName ) SELECT tbl_Odours.txt_FullName FROM tbl_DetectionImports LEFT JOIN tbl_Odours ON tbl_DetectionImports.Odour = tbl_Odours.[txt_Odour] WHERE (((tbl_Odours.txt_FullName) Is Null));

Is that really the name of the field in the table *txt_FullName*? Maybe you should tell me what you are doing because it can be a concatenated field but not the way you are doing it...

 

[ddrew]

Yes but it is made up of four fields [txt_Odour], [txt_OdourType], [txt_Brackets] and [txt_FullName].

txt_FullName is a concatenated field made up of the other three fields. I have just changed the query but now Im getting the same error but on the field txt_Odour

Query is:

INSERT INTO tbl_Odours ( txt_Odour, txt_OdourType, txt_Brackets, txt_FullName )
SELECT tbl_Odours.*, tbl_Odours.txt_Odour, tbl_Odours.txt_OdourType, tbl_Odours.txt_Brackets, tbl_Odours.txt_FullName
FROM tbl_DetectionImports LEFT JOIN tbl_Odours ON tbl_DetectionImports.Odour = tbl_Odours.[txt_Odour]
WHERE (((tbl_Odours.txt_Odour) Is Null));

 

[GinaWhipp]

You cannot select all and then add the fields, you need to just select the fields? Understand?

 

[ddrew]

You cannot select all and then add the fields, you need to just select the fields? Understand?

OK with you on that one and the error has now gone but now when I do the append it creates a blank record in the table.

 

[GinaWhipp]

Hmm, have you tested the query to make sure it returns results?

 

[ddrew]

Its an append query so I can only test it by running it and seeing what happens!

 

[ddrew]

Just tested it and its not actually writing to the table. I tested it by putting in some new data into a spreadsheet and then importing the table and running the append query

 

[GinaWhipp]

Not sure what you did...

Is there data in the table that contains the imported data?
Have you viewed the queries to see if they are returning anything?

 

[GinaWhipp]

FYI, you can view an APPEND query. While in Design View of the query you can press the Datasheet button, upper left and view the results before actually running it.