Can't reference a Multi-Value field in an update query. How can I get around needing a MVF?

[PeakH]

I have one table with a single multivalue field that I just created today and it's already causing problems. The table is a list of product lots:


(simplified version of the table)

LotNumber	OGPesticides	OldLotNum
2011-5482-6624-0071	1, 2
2011-5482-6638-2086	3

Every pesticide used has to end up on a report used for irl labeling, so it's important that the list is complete. Previously we've only used one so it was a single value field, but we have recently needed to add more and a multi-value field seemed to be just what I needed.

I have come across problems when trying to use an update query. We often divide a product lot into the old lot and a new one with all the same details. I have a query that (when I only had single value fields) would allow a user to input the new lot number in [LotNumber] and the originial lot number in [OldLotNum] and the query would set all the detail fields in the new lot to be equal to the detail fields from the old lot. Now I get error 3844 every time saying that I can't reference a MVF in an update query.

I am hoping one of you guys can either help me get the update query to work, or help me find a way around using the multi-value field without losing any data.

[CJ_London]

No idea if it will work but you need to reference the value property of the field. The problem is the MV field is like a many to many relationships table, but because it is hidden away you cannot access it as you would with the normal way of doing things. Not sure if this will do what you want, but about the only thing I could find online which looks like a solution

https://stackoverflow.com/questions/...ield-in-access

Personally I would drop the MV field in favour of a many to many linking table

[ssanfu]

I am with Ajax on this sugject.
I have never and will never use a MVF. I would suggest creating a properly normalized structure. It will save you from major headaches and save you loads of time.

Having said that, I understand some people will use MVFs. I did find these sites that might help:
http://www.utteraccess.com/forum/Mul...-t1984616.html
https://stackoverflow.com/questions/...s-in-ms-access
https://social.msdn.microsoft.com/Fo...orum=accessdev
http://www.utteraccess.com/forum/lof.../t1944468.html

[PeakH]

I'm hesitant to use a many to many linking table since the way that our label reports are designed doesn't leave room for a details section to have multiple details. Everything needs to be on one line since our whole set up is for 2.25" x 1.25" labels and there is a lot of info that goes in that tiny space. The MVF was able to work at first because it had both values in a single detail section with just a comma separating them. If there is a way to do that with a many to many, that would be great!
I haven't gotten around to trying INSERT INTO query, but will try to make time this weekend. I'm thinking that I'll end up needing both an update query for the other detail fields and then an insert into query as well.

[ssanfu]

See
Concatenate values from related records
http://www.allenbrowne.com/func-concat.html

[June7]

If you really must use MVF, review https://support.office.com/en-us/art...ad=US&fromAR=1

[orange]

As other have said, the MVF is a "mechanism" within MS Access. It was intended to "simplify things" for users. Developers/database designers would use normalized tables and relationships. Many/most people avoid them because they are limited/specific to MS Access and deal with hidden entities.
As a poster in another forum said:
The values contained in a multi-value field are actually contained as separate records in a hidden table, they are just presented as a single comma-delimited string for convenience....They can be very useful in certain very specific circumstances; but by-and-large I've found them to be more trouble than they are worth.

[PeakH]

See
Concatenate values from related records
http://www.allenbrowne.com/func-concat.html

Ssnafu, I tried using the ConcatRelated function as that seemed like the best option where I could get around using a MVF. I used the function in a text box on the label report:
"Approved Pesticides: " & ConcatRelated("PesticideName","tblLotPesticides"," LotID = " & [tblLot.LotID])
[tblLot.LotID] is the name of the query field.
But when I try to use it, I get asked for a ConcatRelated parameter value, and the text box returns #Type!
Was there something that I missed?

[ssanfu]

As I said, I have never use a MVF, have never used Allen Browne's Concat function so I can only make a guess.

"Approved Pesticides: " & ConcatRelated("PesticideName","tblLotPesticides"," LotID = " & [tblLot.LotID])
[tblLot.LotID] is the name of the query field.

First, you shouldn't have the quote at the beginning of the name nor the ampersand.
In the query design view, it would/should look like

Approved Pesticides: ConcatRelated("PesticideName","tblLotPesticides","  LotID = " & LotID)

Do you have two fields named "LotID" in the query? If you DO have two fields in the query named "LotID", try

Approved Pesticides: ConcatRelated("PesticideName","tblLotPesticides","  LotID = " & tblLot.LotID)

'NO brackets

[PeakH]

I was not using ConcatRelated in the query, but instead in a textbox on the report that is based on a query. So the textbox is: ="Approved Pesticides: " & ConcatRelated("PesticideName","tblLotPesticides"," LotID = " & [tblLot.LotID])

The query calls on multiple tables two of which have a field LotID, but only one is used in the query. If I attribute a report textbox to that field, the option available is tblLot.LotID
I hope that cleared up what has been happening so far.

I then tried changing the [tblLot.LotID] to tblLot.LotID, which the textbox automatically changed to [tblLot].[LotID] but it kept thinking that [tblLot] was a field and would ask for a parameter value for both ConcatRelated and tblLot.

So I next tried making a concat field in the source query: AppPests: ConcatRelated("PesticideName","tblLotPesticides"," LotID = " & [tblLot].[LotID]) When I try to run the query I get a message box that says "Undefined function 'ConcatRelated' in expression." I double checked that I didn't miss any part of Allen Browne's function and I didn't. So I'm at a loss again.

But am I correct in thinking that the ConcatRelated function is not meant to be used with MVF's, but to make one to many relationships appear as MVF's? I think I forgot to mention that I created a junction table (tblLotPesticides) that is designed with fields LotPestID, LotID and PesticideID. These are related to tblLot.LotID and tblPesticides.PesticideID

[June7]

Did you put the function in a general code module?

Don't prefix field with table name unless there is more than one field in the query named LotID.

The function does handle MVF.

[PeakH]

Yes, it's in a general code module.

I tried changing the tblLot.LotID to just LotID on the report textbox, but the result was still the same, it still asks for a ConcatRelated parameter value and then returns #Type!.

I tried again using the function in the source query itself but referencing tblLot and then tblPesticides instead

AppPest: ConcatRelated("PesticideID","tblLot","LotID = " & [tblLot].[LotID])
And
AppPest: ConcatRelated("PesticideID","tblPesticides","Pesti cideID = " & [PesticideID.value])
and I still get "Undefined function 'ConcatRelated' in expression." with each variation.

Being pretty new to VBA, I feel like there could be something I messed up somewhere, but I wouldn't know how to let you know where I messed up..
Thank you everyone for your patience.

[June7]

All I can say is declaring the function as Public in a general module works for me. I can call the function from query or textbox. Make sure the module does not have same name as the function.

LotID is a number type?

If you want to provide db for analysis, follow instructions at bottom of my post.

[PeakH]

Ah hah! I did call the module ConcatRelated! Once I changed the name everything worked perfectly!

[PeakH]

Thank you everyone for your help!
It's often the little things that get me.