SELECT* cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field

[MHamid]

Hello,

I have a Delete button in my Data Entry Form that worked until I added a new column to the QAMaster table.
The code for the Delete record button also includes copying the data from the QAMaster table and pasting it into the DeletedRecord table.

Ive noticed since yesterday that I am receiving the error message "Run-Time error '3825': SELECT* cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field".

I ensured that both the QAMaster and DeletedRecord tables have the exact same columns. Where did I go wrong?

Here is the code for the Delete Record button. The part where it errors out at is in red:

Private Sub Delete_Record_Click()
Dim rst As Recordset
Dim strCopy, strSQL, answer As String
If IsNull(Me.txtRefID) Then
    MsgBox "No Record to delete."
Else
    answer = MsgBox("Are you sure you want to delete record?", vbYesNo + vbCritical + vbDefaultButton2, "Record Deleted Successfully")
    If answer = vbYes Then
        Call AuditChanges("txtRefID", "Delete")
        strCopy = "INSERT Into DeletedRecord select QAMaster.* from QAMaster where (QAMaster.RefID = " & Me.txtRefID & ");"
        strSQL = "delete * from QAMaster where RefID= " & Me.txtRefID
    DoCmd.SetWarnings False
    [COLOR=#ff0000][B]DoCmd.RunSQL strCopy[/B][/COLOR]
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    
    'Me.Requery
    Forms("Data Entry").Requery
    
    End If
End If
End Sub

Thank you

 

[mole999]

I'm not sure but from the statement I'm guessing you have mixed varchar / string / numeric or something and as such the system can't decide what needs to be updated, so you need to CAST / CONVERT to the same style values (IMHO)

 

[xenou]

I think you should take that error message at face value and do not use * when inserting into a table with a multi-valued field. Instead, use an explicit column list (and cross your fingers).

Multi-valued field are primarily for interacting between MSAccess and Sharepoint (lists) and really are probably best avoided otherwise.

 

[MHamid]

Hello,

I just realized that I have an attachments column in both of my tables.
How should I handle that column when deleting records?
How do I use an explicit column list?


Thank you

 

[xenou]

I don't have any information on attachments - I don't use them in Access.
An explicit column list means you list the column names:

without column list:
select * from Sales

with column list:
select (Product, TransDate, Price) from Sales

Note useful links:
https://support.office.com/en-us/ar...d-fields-7C2FD644-3771-48E4-B6DC-6DE9BEBBEC31
https://support.office.com/en-us/ar...-queries-6f64f92d-659f-411c-9503-b6624e1e323a
A possible workaround:
https://access-programmers.co.uk/forums/showthread.php?t=233106

The suggestion to use an explicit column list came from a stack overflow post where a user reporting that it worked.

 

[MHamid]

Hello,

So change it from

strCopy = "INSERT Into DeletedRecords select QAMaster. * from QAMaster where (QAMaster.RefID = " & Me.txtRefID & ");"

to

strCopy = "INSERT Into DeletedRecords select ([EnteredBy],[DateEntered],[CycleMonth],[ReportType],[DateReviewed],[ReviewerType],[Reviewer],[ReviewerReportArea],[MainSection],[TopicSection],[Ownership-Individual],[Ownership-Team],[Count],[Priority],[ApprovedBy],[L1],[L2],[L3],[Other],[RepeatAsk],[Exception],[Notes],[Outliers],[AdditionalOutlier]) from QAMaster where (QAMaster.RefID = " & Me.txtRefID & ");"

 

[xenou]

Yes - and see if it works.

 

[MHamid]

Hello,

I was just told that a PDF file attachment is not needed because it will make the database file size too big quickly.
I will now have to do a link to the path name where the file will be stored. I'm hoping that will be easier and I now don't have to deal with this multivalued field.

Thanks for you help.