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

[jaad]

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

example:
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)

EDIT___________ _

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

[beacon]

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):

    For Each varItem In Me.YourListBoxName.ItemsSelected
        varWhere = varWhere & Me.YourListBoxName.Column(0, varItem) & ","
    Next varItem
    
    'Remove the trailing comma from the parameter variable
    '-------------------------------------------------------
    varWhere = Left$(varWhere, Len(varWhere) - 1)
    
    'Create the search string
    '--------------------------
    varWhere = "[TenantID] IN (" & varWhere & ")"

[jaad]

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.

cheers
Jaad

[beacon]

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:

    [B]Dim varWhere, varItem[/B]

    For Each varItem In Me.YourListBoxName.ItemsSelected 
        varWhere = varWhere & Me.YourListBoxName.Column(0, varItem) & "," 
    Next varItem 
  
    'Remove the trailing comma from the parameter variable 
    '------------------------------------------------------- 
    varWhere = Left$(varWhere, Len(varWhere) - 1) 
  
    'Create the search string 
    '-------------------------- 
    varWhere = "[TenantID] IN (" & varWhere & ")"

[jaad]

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.

[beacon]

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.

Thanks,
beacon

[jaad]

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

[jaad]

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)

[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.

[jaad]

thanks a whole bunch Amigo. I truly appreciate your time and would gladly buy you a beer if you were in my neighborhood.

cheers
jaad

[beacon]

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.

[jaad]

I'm sorry for the delay. I have been tasked with an emergency project but will respond to your request this weekend

[jaad]

I have enclosed a diagram of what I am talking about. Let me know if you understand it.

Cheers
Jaad

[beacon]

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:

="[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,
beacon

[jaad]

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

="[LCRDID]=" & Nz([LCRDID],0)