468,540 Members | 2,006 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,540 developers. It's quick & easy.

How to reference a single value field to a multi-value field?

158 100+
how do you reference a single value field to a multi-value field?

I sometime use a macro in form1 to open form 2 containing the same ID

Open form: WorkOrder
where condition: ="[TenantID]=" & Nz([TenantID],0)

How do you do write this same reference but for a multi value field?

="[TenantID]=" & Nz([Multi-value expression?],0)


I tried:
="[TenantID]=" & Nz([TenantID],0)
="[TenantID]=" & Nz([TenantID].Value,0)
="[TenantID]=" & Nz(([TenantID].Value),0)
="[TenantID]=" & Nz([TenantID].[Value],0)
="[TenantID]=" & Nz(([TenantID].[Value]),0)

and none worked
Jan 9 '11 #1

✓ answered by beacon

Ok, I think I understand. You should still be able to search for the repair if it's contained with the set of values in the corresponding record.

Don't quote me on this because I've never actually done it before, but I think you can still make it work. The key is whether or not you are storing the values being selected in the multiselect list box. If you're storing the values in a table (as a comma separated list), then you should be able to assess the field, determine if the repair you're looking for exists, then select that record if it does.

I know this doesn't really help you resolve the problem, but I'll have to play with it myself to see if I can recreate what you're doing and make it work. I probably won't have time to do it today or tomorrow, but if no one else has responded by Thursday I look into it.

19 6300
579 512MB
Hi jaad,

When you say multi-value field, are you talking about a List Box where you can select multiple values?

If so, you can use the following VBA for some event on your form (you'll need to change YourListBoxName to the actual name you are using for your list box on your form):

Expand|Select|Wrap|Line Numbers
  1.     For Each varItem In Me.YourListBoxName.ItemsSelected
  2.         varWhere = varWhere & Me.YourListBoxName.Column(0, varItem) & ","
  3.     Next varItem
  5.     'Remove the trailing comma from the parameter variable
  6.     '-------------------------------------------------------
  7.     varWhere = Left$(varWhere, Len(varWhere) - 1)
  9.     'Create the search string
  10.     '--------------------------
  11.     varWhere = "[TenantID] IN (" & varWhere & ")"
Jan 10 '11 #2
158 100+
Hello Beacon and thanks for responding to my question, I really appreciate it.

From what I can tell with my limited amount of knowledge in Vb codes I think the code you wrote would work but where do I put this code? on Click event?

How I used to do it was simple because I was in charge of performing only one work order at the time for one tenants that lives in one building but now I have been given the charge of many (or batch work orders) so sometime the same work needs to be performed with many tenants at many location and reason why I have a multi select TenantID field so I can batch the tenants into one work order.

Jan 11 '11 #3
579 512MB
That all makes perfect sense and is similar to the reasons why I implemented this recently in a database I was working on.

Were you assigning the macro to a command button on your form? If so, the likely place for the code would be the OnClick event for the same command button...that's what I typically do.

Just in case you don't know, you'll need to declare the variables in the OnClick event for the command button if you choose to go that route:

Expand|Select|Wrap|Line Numbers
  1.     Dim varWhere, varItem
  3.     For Each varItem In Me.YourListBoxName.ItemsSelected 
  4.         varWhere = varWhere & Me.YourListBoxName.Column(0, varItem) & "," 
  5.     Next varItem 
  7.     'Remove the trailing comma from the parameter variable 
  8.     '------------------------------------------------------- 
  9.     varWhere = Left$(varWhere, Len(varWhere) - 1) 
  11.     'Create the search string 
  12.     '-------------------------- 
  13.     varWhere = "[TenantID] IN (" & varWhere & ")" 
Jan 11 '11 #4
158 100+
ok I pasted it but while I was cleaning it up I notice that there are some things missing like the form name that I am trying to open and the way I use the multiselect combo box .....

OK let me start again............

I used the example of tenantID because it made it simple for me to write the question. This is how it is exactly:

As an example: I have to change 20 smoke alarms in 20 different suite that are occupied by 20 different tenant. Instead of creating 20 work order I can create one and then batch all of the repairs into one work order.

so my starting point is in the REPAIR form which I used to use the ID# of the record as a reference to jump into the project form where I write all the specifics for the job. This is where the multivalue list is. The originating form has single field values.

What I would like to be able to do is come back to the Project form where the repairID was entered whether it was a stand alone or with many other RepairID that I batched together.

Another way I was thinking of doing it is by jumping straight to the Project Form and choose from the multivalue combo box one or many repair(s) from the list but if I do that I then lose the reference PK and FK and would need a VBA code to transfer that into my Repair table.
Jan 11 '11 #5
579 512MB
Ok, so let me make sure I understand. You use the Repair form to launch the Project form, where the multiselect list box is located, and enter the repairs, right?

Are you wanting to post the repairs in the multiselect list box to a table, like as a comma separated list (i.e. KitFaucet, DoorKnob, CeilingFan, etc.)? Or are you trying to use the repairs in the multiselect list box to create a filter to launch a report? I was under the impression initially that you were trying to create a filter so you could launch a report, but now it sounds like you're trying to post the data and I want to make sure we're on the same page before we move forward.

Jan 11 '11 #6
158 100+
yes, I have a Repair table and a project table. Both use different forms.

I enter each item that requires repair into the Repair form. I have to do it this way because there are other module which link tenants and the Current Location Condition of the suite they rent. I use a Repair record reference to launch the Project form which has the multivalue control on it.

When it gets to the project level of the activity that I am doing I really dont care about the tenants at that time its just the location that matters to me as I already have a reference in my repair table that points toward the tenant and dont need to double up on that reference.

All I want to accomplish is to have the ability to batch a bunch of similar repairs together(like my 20 smoke alarm going into 20 different location for 20 different tenants. The multi value control makes a lot of sense. I can pick them out directly from the Project form no problem and do what I want to do. The problem is when lets say I am in my repair form and need to check something out in the project relating to this repair. I can't search it given that my repair is a single value and where I entered the corresponding record into the project table is multivalue
Jan 11 '11 #7
158 100+
OK, I figured out another way of doing this. I've create a query with the records that I created in Project to feed my Repair updated form. That was kind of easy and stupid when I think about it now.

But I would be extremely thankful if nevertheless you would show me how to do what I wanted to do in the first place so I could maybe use this function in the future (which I think I will have a use for it with my Journal entries)
Jan 11 '11 #8
579 512MB
Ok, I think I understand. You should still be able to search for the repair if it's contained with the set of values in the corresponding record.

Don't quote me on this because I've never actually done it before, but I think you can still make it work. The key is whether or not you are storing the values being selected in the multiselect list box. If you're storing the values in a table (as a comma separated list), then you should be able to assess the field, determine if the repair you're looking for exists, then select that record if it does.

I know this doesn't really help you resolve the problem, but I'll have to play with it myself to see if I can recreate what you're doing and make it work. I probably won't have time to do it today or tomorrow, but if no one else has responded by Thursday I look into it.
Jan 11 '11 #9
158 100+
thanks a whole bunch Amigo. I truly appreciate your time and would gladly buy you a beer if you were in my neighborhood.

Jan 11 '11 #10
579 512MB
Will you do me a favor and post pictures of anything that might help me recreate your database? I think the two forms, maybe the underlying queries, and your relationships page should cover it.
Jan 12 '11 #11
158 100+
I'm sorry for the delay. I have been tasked with an emergency project but will respond to your request this weekend
Jan 14 '11 #12
158 100+
I have enclosed a diagram of what I am talking about. Let me know if you understand it.

Attached Files
File Type: zip structure.zip (17.0 KB, 131 views)
Jan 15 '11 #13
579 512MB
I don't know how I missed it when helping you before, but I totally didn't catch on that you were using Access 2007. I was trying to give you answers thinking you were using 2003, which would have meant that you would have been creating a multivalue list as a comma separated value list in a single record of a table. Now that I know you're using 2007, I think I might be able to help you out a little better, although my experience with 2007 is somewhat limited. However, I do have a book on it that I read a about 7 months ago and I remember the part on multivalues.

Two quick questions before I get into it...is the Tenant ID the multivalue? Also, do you have a primary key in the table/query where the Tenant IDs are? I thought you said you did, but I'm writing this in a hurry and don't have time to re-read the whole thread. I'm going to assume you do and if you don't, hopefully you can extrapolate what I'm saying and apply it as you see fit.

Anyway, check out this site, How to employ a multivalued field in a query, when you're creating your query, because, as the article explains, it has a lot to do with how you access the data.

If the TenantIDs are in the multivalue list, I think you'll need another field, preferably the primary key you're using for the form, to access the row where the TenantIDs are stored. If your TenantIDs are the primary key, you may need to rethink your design for that particular table only because I'm not sure you'll be able to provide the database with a point of reference to access the IDs.

If you do have a separate primary key (heck, any other field may work...it's hard for me to say without seeing the tables/queries the form is based on), then you would use the same expression you mentioned in the beginning, but change the first TenantID to the primary key. For example:

Expand|Select|Wrap|Line Numbers
  1. ="[YourPrimaryKey]=" & Nz([TenantID],0)
This may be nothing new to you, but hopefully you can piece it together from what I've written. You may also want to use the DLookup() function on the table/query to determine whether or not a value actually exists before you execute macro. My last piece of advice would be to get away from the macros...it's been my experience that they are slower than VBA and don't have quite as much flexibility. But that's just my opinion.

Hope this helps,
Jan 22 '11 #14
158 100+
Thanks a great bunch Beacon. I've managed to get what I wanted and used a query with the PK and FK as you mentioned. The thing that I was hoping at the end was to only get 1 record that would batch a whole bunch of repairs (which it does in the foreground but the project detail query needs to create a separate record for each FK that I receive.)

I've used the reference below which works great but the downfall is that in form it shows 5 records with the same Project detail ID which is ok. I'll live with that

Expand|Select|Wrap|Line Numbers
  1. ="[LCRDID]=" & Nz([LCRDID],0) 
Jan 22 '11 #15
579 512MB
That looks like the shortcoming of multivalue lists. Unless you have another search mechanism to pull the exact tenant from the multivalue list, you may be stuck. However, I only say that because my experience with it is very limited.

You could go a different route, but it wouldn't do you much good because it would require not using the multivalue list at all and instead creating your own table with a many-to-many relationship that you can access easier than the system tables that power the multivalue list.

At least you were able to get it to (mostly) work. I'm actually working on a project right now where I don't think I'm going to be able to get all the functionality to place nice with one another, so I feel your pain.

Good luck,
Jan 22 '11 #16
158 100+
You know, I have been looking at a many to many table first. that was the first idea becuase I had used multi value field before and ran into similar problem.

Although setting it up a many to many table didn't seem like any problem at all, I ran into trouble when it was time to use the table. ( I didn't know how to use the reference and how and where to place the ID controls in my form.

at the end it is really hard to batch anything in data entry, I wish it would be as easy as in Report
Jan 22 '11 #17
158 100+
take a look at this:


could I use this function in 2007? He makes reference to Library Reference that are different in 2007 and when I look at my reference library my object library is 12.0 instead of 3.6 and I have no activex library selected.
Jan 22 '11 #18
579 512MB
Your Microsoft Access Object Library should be 12.0. The 3.6 should be for your Data Access Objects (DAO) Library. These are two different libraries that serve different purposes. I don't think anything you've been working on requires any additional ActiveX controls, so you probably don't need this reference, but you can add one if you want. The version I use is 2.5, but you may have a higher version available in 2007.

When you're setting up a many-to-many relationship between tables, you have to add a junction table that handles the multiple occurrences between the other two tables. One of the examples I've seen alot to explain many-to-many relationships, with a junction table, is a school/college database.

In this type of database, you will likely have a table for students and another for professors. The junction table would be a class table because students will typically have many classes and professors will teach many classes, but the class won't have more than one of the same student and typically won't have more than one professor.

You create a foreign key for each table in the junction table, such as StudentIDFK or ProfessorIDFK, and you'll have a primary key in the junction table too, something like ClassID. You might have additional attributes in the Class table, like the name or the time of the class, but whether you do or not, you'll only have one unique record in the database. If the class is at a different time then it's still unique because that one attribute changes and so do the students or professors if they repeat.

For your database, and this is generalized, I think you would have a table for repairs and a table for buildings. The junction table would be your tenants because a building could have many tenants or there might be many repairs for a tenant, but only one building could be assigned to an individual tenant and only one repair could be associated with one individual tenant. You might have the same type of repair, but it won't be associated with the same tenant each time and even if it occurs in the future, surely you'd have a date the repair was requested to differentiate it from other repair orders.

If you break it out like this, I can tell you from experience that it's much easier to filter your form because you'll filter the main form by the primary key in the repairs table, for instance, and, if you included a subform, which I think you did, you can link the subform to the main form using the RepairID as the master and the RepairIDFK as the child from the tenants table and it should show. The same may be possible using a mutlivalue list, but I just don't have the experience to intelligently speak to it (mostly because the values are stored in a hidden system table and I don't know what the setup is).

I should probably mention that, for your specific case, you'll want to use something similar to the example used in the video to add the repair for each user to an individual row and, more importantly, assigns it a unique RepairID. That way, when you filter, even if you entered the same repair for multiple people, you'll only return one record.
Jan 22 '11 #19
158 100+
here are a couple of pictures. ONe is the location symbiosis between tenants and locations. The other is Project which administrate the repairs in Location.

While the tenant is active in a location, the tenant ID follow a location everywhere it goes as the tenant may or may not be liable for repairs and damages. Each location has a sub location Kitchen, bathroom, Patio... and each sub location has items: Floor, wall, toilet....

each location item must be given a condition grade at move-in and at move out. this is the way we know if the tenant security deposit is credited or not. while the repairs are being performed an addition table called Interim accounts for the repairs and those repairs are added as an attachment to the location condition report that was signed at move-in.

you can see in the picture example. the tenant has several items that needs to be repaired but only two are current. The repair subform is FK is location because no matter who lives in the unit these repair will always need to be performed.

on the project picture you can see in a list box that the two repair item that are current are showing in the listbox. this is the multivalue filed. I can pick and chose what of the current repair I will perform. I may do one or all of them. Below I have a sub form that tracks the material that I use on the job which is linked in location and with the symbiosis to show what has been done historically. then I know when a smoke detector was change and when it will be due for change for example.

The only problem that I had was that depending on how many record I had in my multivalue field I was getting the same amount of project record. So what I ended up doing was to filter the form as you suggested.

so it works! thanks to you and my other new friends in here (which I would gladly buy a beer if you ever come my way :o)
Attached Images
File Type: jpg Project.jpg (39.4 KB, 395 views)
File Type: jpg Symbiosis.jpg (30.9 KB, 379 views)
Jan 23 '11 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Peter Morris [Droopy Eyes Software] | last post: by
reply views Thread by AmateurDBer | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.