X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Copying Information From A Multivalue Field, Access 2010    
post Jun 25 2020, 08:23 AM

Posts: 5
Joined: 25-June 20


I am relatively new to access and very new to macro's.

I have a large database which has a number of multi-value fields. We are finding these to be problematic.

The data is currently in the table [Employee details] under field [Notified of Changes to] and has a selection of numeric numbers between 1-20 which refers to a separate [Discipline] table.

I am looking to paste this into a new table called [Employee Groups] with [Employee ID] in one column and [Discipline] in the other.

The applicable part of my code is as follows:

If [Tables]![Employee Details].[Notified of changes to] Like "1"
Item = [Tables]![Employee Groups].[Employee ID]
Expression = [Tables]![Employee Details].[Employee ID]
Item = [Tables]![Employee Groups].[Discipline]
Expression = 1
Command SaveRecord

End If

I have repeated this command up to number "20", though I'm sure there is a better way of doing this the above expression fails as it doesn't recognise [Tables]! or any other permutation of options I've put in.

I have tried Dlookup but can't quite get that to work.


Go to the top of the page
post Jun 25 2020, 08:57 AM

Posts: 2,120
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

Many/most would advise against the use of multi-valued fields and many of those do not use macros.
You might get more and focused responses if you gave a little more info about the application and your intent.
It would also help readers if you provided a graphic of your tables and relationships.

Good luck with your project!
Go to the top of the page
post Jun 25 2020, 09:05 AM

Posts: 5
Joined: 25-June 20

Thanks Orange,

Yes this is a historic mistake we are trying to rectify by removing the multi-value field.

My intention is to take it from a multi-value field on the [Employee Details] table where there is one entry per [Employee ID] and move it so that you have many input's per [Employee ID] with the different [Disciplines] they are approved for. I will then make a new form for the front end and allow the user to add/remove [Disciplines] per [Employee ID]


Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    3rd August 2020 - 10:09 AM