problem with sql stmt and multi-valued field

[ljr]

I am trying to insert a record into a table using sql. One of my fields
"PicLocation" is multi-valued. I have tried just the field in my sql as
shown below. I have also tried with the multi fields and keep getting the
same error message. Any know what I am doing wrong? Thanks in advance.

The error is: Run-time error '3824'
An INSERT INTO query cannot contain a multi-valued
field.

strSQL = ("INSERT INTO tbl_Login ( UserID, PicLocation, Privilege) " & _
"SELECT tbl_Employees.[UserID], tbl_Employees.PicLocation, " & _
"tbl_Employees.Privilege " & _
"FROM tbl_Employees " & _
"WHERE (tbl_Employees.UserID)=" & "'" & me.txtWinUser & "'" & ";")

I have also tried the following with the same error.

strSQL = ("INSERT INTO tbl_Login ( UserID, PicLocation,
PicLocation.FileData, " & _
"PicLocation.FileName, PicLocation.FileType, Privilege ) " & _
"SELECT tbl_Employees.[User ID], tbl_Employees.PicLocation, " & _
"tbl_Employees.PicLocation.FileData, " & _
"tbl_Employees.PicLocation.FileName, " & _
"tbl_Employees.PicLocation.FileType, tbl_Employees.Privilege " & _
"FROM tbl_Employees " & _
"WHERE (tbl_Employees.[User ID])=" & "'" & Me.txtWintUser & "'" & ";")

 

[John W. Vinson]

I am trying to insert a record into a table using sql. One of my fields
"PicLocation" is multi-valued. I have tried just the field in my sql as
shown below. I have also tried with the multi fields and keep getting the
same error message. Any know what I am doing wrong?

Just using Microsoft's misleading, incorrectly implemented, and infuriating
MultiValued Field misfeature.

Under the covers, Access actually does this right - implements a hidden table
in a one-to-many relationship with your table and inserts the multiple values
as records. However, this table is not exposed to user view or even to VBA
code without going through a lot of contortions.

I don't have 2007 installed yet, so I don't know how - or if - this can be
done at all; but I'd really recommend modeling a many to many relationship the
old-fashioned relational way, with three tables (MainTable, AllowableValues,
ValuesUsed).

John W. Vinson [MVP]